Jump to content

Latest Record for sets of records


cflamey
 Share

Recommended Posts

I write a fantasy wrestling blog where I'm rewriting wrestling history from 1983 on. I've taken a few months off the blog so that I can work on automating the update process through VB 6 and Microsoft Access 2000. The Access database has been converted back to a prior version of Access so that the VB coding works.

 

I'm trying to come up with a SQL statement to bring up the list of current champions. Everything I try only gives me one record.

 

Here's the table structure:

 

Table: Titles

 

ID

Championship

Champion

Reign

DateWon

DateLost

 

The problem is making the SQL statement work to go through the multiple items in "Championship" instead of just the first one. I've tried using the Top, Max and Group By statements as found on this web site's help sections (which have been very helpful for other things I've forgotten in my years since I was a database programmer).

 

What am I missing?

Link to comment
Share on other sites

What is the datatype of "Reign?" What is the value of "DateLost" for the current champion? If "DateLost" is null for the current champions that would be a pretty simple way to select the current champions.

 

Also I seem to remember that Access has had some definite limitations in its support of SQL.

Link to comment
Share on other sites

"Reign" is an integer showing how many times the wrestler has held the particular title.

 

I had "DateLost" as a null value for a while, but I changed the programming to have the computer choose how long the title reign would be. May have to end up going back to that to make it simpler.

 

And yes, there are some serious limitations for Access SQL. Most of my database programming experience has been with Oracle. Now there's a powerful database engine!

Link to comment
Share on other sites

Obviously you have the opportunity to enter something unique in the DateLost column when you record a new or reigning champion. Or if DateLost is a future date you could select on that. Or you could add another boolean column to the table to indicate the current champion.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...