Date Query

[Da Stinga]

I need your help .

Ok, i have a database of members of a video game store, who rent out games. I have several fields, and the last few fields are:

-Rented Game 1
-Rented Game 1 Return Date
-Rented Game 2
-Rented Game 2 Return Date
-Rented Game 3
-Rented Game 3 Return Date

I was wondering how i could create a query that displays only the members that have a game with a return date that has passed. For example, if one member had "Game 2" was due back yesterday, how could i get the query to display this member's details and only the details of the overdue game?

PLEASE HELP ASAP!

Stinga

 

[boblarson]

First of all, your table needs to be "normalized." Your tables and fields should just be:

TABLE 1
CustomerID
FirstName
LastName
Address
City
State
ZipCode (and etc. all details you want)


TABLE 2
CustomerID
Game
DateOut
DateDue

Then, you can create and run queries that will let you know which games are out, which are overdue (the date of Date Due is < the current date).

To create a query that lets you know customer details and what is out, you can open the QBE (Query By Example) grid and add both tables, joining the CustomerID fields (if it doesn't do it automatically). Then drop the fields you want to display into the grid, and then you can do several things, depending upon how fancy you want to go. You can put input boxes into the fields that select GAME and DATEDUE and then when you run the query you can type in what you want, you can set up a form to put that input and place references to the controls on the form to the Game and DateDue, and there are other things you can do as well.

 

[Da Stinga]

You can put input boxes into the fields that select GAME and DATEDUE and then when you run the query you can type in what you want, you can set up a form to put that input and place references to the controls on the form to the Game and DateDue, and there are other things you can do as well.

Ok, i understand you up to here, lol, sorry . In the second table, can i still have the fields:

-Game 1
-Game 1 DateOut
-Game 1 DateDue
-Game 2
-Game 2 DateOut
-Game 2 DateDue
-Game 3
-Game 3 DateOut
-Game 3 DateDue

If i do that , how do i design the query so that it only displays the information of the games that are due back, but not those that are not due back?

Thanx for your help

Stinga

 

[boblarson]

Sorry, you still have to revise your tables for it to work properly and to be designed properly (just look for all posts on Normalization, especially by Pat Hartman)