Querying multiple tables for a report

[soma56]

Greetings. This is for an online university course that I am taking. I have tried to figure this out myself for weeks now. I have emailed my tutor who gave me some information that has helped. I have invested hours with nothing to show for except confusion and frustration. I have to create a simple database for a video store. I have four tables with fields filled out accordingly with various information (Customers, Rentals, Media, Categories). I cannot seem to figure out how to string them all together in a query.

For example: If I had one table with a customers information, a second table with movie information and a third with rental status - what criteria in which table do I write? If the customers table had a check box for 'current rentals' how do I link it to a specific record in the rentals table? How would I only show late rentals (with a table I've already created that has old and new dates).


It seems all the issues are surrounding the query criteria. I know it's probably very basic for most people here however any help or direction would be greatly, greatly appreciated.

I would love to email the actual instructions to anyone who is interested in reading them and helping me out...
cromarty at aci dot on dot ca (cromarty@aci.on.ca)

Ashley

[Brandtrock]

Welcome to VBA Express Ashley!

First off, you will get more replies (and probably faster) if your thread title is more descriptive. For example, Querying multiple tables for a report.

Next, putting your email addy out there is likely to get you spammed when the bots crawl the site.

Are you trying to do the query through VBA, or simply set one up in Access? I wasn't sure from your original post.

Access can be frustrating to deal with when you first learn it, just take a deep breath and we'll try to get you headed in the right direction.

No more :banghead: than necessary. :rotlaugh:

If you want to email the instructions you have to me, go to my profile and send them, I'd be happy to take a look. I won't do the work for ya, but it does help to know exactly what you are facing so the direction you need to be sent is more clear to me.

Regards,

[andrew93]

Hi Ashley

Access can be very frustrating if you don't have the right table set-up and relationships. I'm not saying there is only one right way of doing this - it's just that some ways are easier to handle than others.

There is a useful webpage here that explains database / table normalisation:
http://datamodel.org/NormalizationRules.html

Based on what you have provided, I'm envisaging something like this:

tblCustomers
Customer_ID {unqiue ID}
Customer_Name
Customer_Address
Customer_Status {linked to tblStatus perhaps?}
etc etc etc

tblRentals
Autonumber {unique ID}
Customer_Link {linked to tblCustomers.Customer_ID many-to-one}
Product_Link {linked to tblProducts.Product_ID - many-to-one}
Product_Price {See Note 1}
Rental_Date
Rental_Due_Date
Rental_Return_Date

tblProducts
Product_ID {unique ID}
Product_Type (e.g. DVD, Video, PS2 etc) - linked to tblProductTypes?
Product_Description
Product_Genre - linked to tblProductGenres?
etc etc etc

In some rental businesses the rental table is split into rental headers (ie the rental agreement) and rental details (ie the products rented - one agreement can have multiple products, but that may be overkill for this scenario). There may also be other fields and tables, like in the Products table you would want a list price (or a list price category, in another table) and a standard rental term (or rental term category in another table like 'new releases' etc) - how detailed you want to get is up to you.

So in a nutshell you have a one-to-many relationship between the customer and the rentals (one customer can have many rentals) and a one-to-many relationship between the products and the rentals (one product can be rented many times). This is actually a many-to-many relationship (http://www.ehow.com/how_13626_define-many-many.html) between the customers and products (many customers can rent many products).

Have you created the relationships between your tables under menu option Tools > Relationships? If not, then you need to do that.

In answer to your query questions, if you aren't familiar with building queries then there is a useful resource here:
http://office.microsoft.com/en-us/access/HA100474921033.aspx

and here for queries that use multiple tables (you might want to read this one):
http://office.microsoft.com/en-us/access/HA100962751033.aspx

You also need to use criteria in your queries, and this is a useful starting place:
http://office.microsoft.com/en-us/access/HA100666111033.aspx

To answer your questionsl, build a query based on the three main tables, show all of the fields you want to see (e.g. customer name, product description, rental dates etc) and depending on how you have set up your table, if you enter the 'product return date' into the 'rentals' table (see my example above), then to find overdue rentals you would be looking at rentals where the rental return date is null (nothing has been entered) and the 'rental due date' would be less than today. And to find the current rentals you would use a very similar query except the criteria would be looking for all rentals where there was no rental return date.

Note 1 : I have included the price in the rental table (instead of the product table - it can be there too) to take account of discounts, ie where the actual price differs to the list price.

Apologies for the long reply.

HTH, Andrew

[stanl]

Sad to hear about your frustrations. I Googled 'Video Store Database' - the 2nd hit has a complete design in Access. Stan

http://www.databasedev.co.uk/video_rentals_data_model.html

[soma56]

Thanks to all of you for your replies...

Brandtrock - I am simply trying to set up a query in Access. I don't expect you or anyone else to do the work for me I just need some direction. And, thank you for the advice (email address, specific title, etc.)

Andrew93 - No need to apoligize for the long reply. I intend on reviewing all the information you have posted this morning. If you have a chance to review the instructions and have anything else to add please let me know. Thanks.

Stanl - I found the same link a few weeks ago when I started out however I really appreciate the post.

Ashley

[soma56]

Here are the instructions...



The task in this project is to design a normalized database which could be used to collect and store information for a video rental outlet. The database should be structured to support tasks such as:

tracking rentals by title and by customer
producing reports such as rentals due on a certain date
categorizing rentals by media type (VHS, DVD, Game) The assignment does not require that you produce examples of the reports listed, but only that the database be correctly structured to allow the query and reporting features of a DBMS such as Access to work properly.
You will need to collect information about several titles in each category, which can be done online, in the local video store, or from your personal media collection. You will be given an initial, un-normalized field list of data items to be tracked, which you will normalize into appropriate separate tables.
The example is somewhat simplified in comparison to a real application. For example, only brief customer address information is included, to reduce the amount of data input required for the project. We have also simplified the example in terms of structure, ignoring, for example, complexities of the real-world video outlet such as varying rental periods for different products.
The initial field list does not already include appropriate key fields for all of the tables which will be created, so suitable key fields should be added to the tables as required.
The project is most easily done using MS-Access, however, it can also be done without Access. For a detailed discussion of other options for completing the Project, please see "Alternatives to Using MS Access" below.
PROJECT DETAILS

The following is a list of essential data items to be recorded. As noted above, there may be a few additional fields required as keys for the tables you will create from these fields.

date of rental
due date
media item title
media item category
category description
category code
customer ID#
customer lastname
customer firstname
customer address
customer phone# If only a single table were made from all the fields listed above, the result would be a system in which every rental that was recorded would have to include duplicate customer and media item detail. In addition to wasting input time duplicating data, such a system would also be highly error prone, as any change to customer data (a change of address for example) would also result in different records showing different content for the same fields, unless every historical record for that customer was updated.
The relational database model was designed to solve those problems by identifying key data entities, separating them into their own tables, and relating the tables through the use of foreign keys (The foreign key is usually the primary key from one table, placed in one or more other tables to create linkages.)
This allows for customer detail, for example, to be recorded once, in a table reserved for that purpose, and referenced in other tables by a unique identifier (key) such as Customer ID#.
Your task is to create a simple relational database from the field list above. The required tables are Customers, Media, Categories, and Rentals, each of which should be properly provided with a primary key, and each of which should be related to other tables as required.
All four database tables should be populated with records as follows:

Customers - at least 10
Categories - three records, one describing each of the the media types: DVD, VHS or Game
Media - at least 30 records, representing a selection of DVD movies, VHS movies and Games.
Rentals - at least 20 records. The structure of this database is analagous to the student information database described in the course reading section covering Normalization.
To compare the text example of the Student Information database to this exercise, consider CUSTOMER to be equivalent to STUDENT, MEDIA to be equivalent to COURSE, and CATEGORY to be equivalent to CATEGORY. Remember that each table should contain only those fields required to describe that entity, plus any key fields used as a foreign key (linking to another table).
Note that the analogy between these two examples does NOT extend to the concatenated key. Using a concatenated key of Customer ID# plus Media Item#, for example, would not be effective, as this would not allow for the possibility of the same customer renting the same item more than once. A unique single key can be used for the Rentals table, or a combined key that would not be duplicated (ie one that includes the date).

[soma56]

I don't get it.

I have the following three tables already created with a few records in an attempt to create any type of query through Access. Whenever I try to run any query there are never any results (simply blank).

Customers
CustomersID (Primary Key)
CustomersLastName
CustomersFirstName
BillingAddress
PhoneNumber
Rentals

Rentals
Rentals (Primary Key)
CustomerID
Media
MediaItemCategory
ReturnDate
RentalDAte
DueDate

Media
Media (Primary Key)
Title
DVD
VHS
Games

CustomerID from the Customers table is linked to CustomerID in the Rentals table. Media in the Rentals table is linked to Media in the Media table. The Return Date field is blank in the Rentals table. If I set a criterion to “Is Null” in the Return Date of the Rental table why do I have no results? Should it not bring up everyone I've listed that has the Return Date field blank? If I have a customer with the last name “Smith” and I place his name “Smith” in the CustomerLastName Criteria box shouldn’t I just get his results back (I currently get nothing). I’m extremely confused, upset and frustrated.

What am I missing or not doing right?

[stanl]

what you probably need to do is click on SQL View when in the query editor; copy the SQL query and paste it here. Stan

[stanl]

what you probably need to do is click on SQL View when in the query editor; copy the SQL query and paste it here. Stan

[Brandtrock]

Ashley A previous experience with Access is why I included the bit about not doing the whole thing for you. I didn't mean to imply that that was what you were doing. I hope I didn't step on your toes.

Thanks for posting the instructions.

[andrew93]

Hi Ashley

Following on from Stan - have you created a query? If not where are you trying to set the criteria? Also, did you read the query links in my earlier post? If you have created a query, it would be easier if we could see the SQL.

Andrew

[Brandtrock]

Also take care when creating your fields. You have a primary key of CustomersID and then CustomerID (without the "s") listed later on. These are seen as different. Disregard if this was simply a typo.

[XLGibbs]

I don't get it.

I have the following three tables already created with a few records in an attempt to create any type of query through Access. Whenever I try to run any query there are never any results (simply blank).

Customers
CustomersID (Primary Key)
CustomersLastName
CustomersFirstName
BillingAddress
PhoneNumber
Rentals

Rentals
Rentals (Primary Key)
CustomerID
Media
MediaItemCategory
ReturnDate
RentalDAte
DueDate

Media
Media (Primary Key)
Title
DVD
VHS
Games

CustomerID from the Customers table is linked to CustomerID in the Rentals table. Media in the Rentals table is linked to Media in the Media table. The Return Date field is blank in the Rentals table. If I set a criterion to ?Is Null? in the Return Date of the Rental table why do I have no results? Should it not bring up everyone I've listed that has the Return Date field blank? If I have a customer with the last name ?Smith? and I place his name ?Smith? in the CustomerLastName Criteria box shouldn?t I just get his results back (I currently get nothing). I?m extremely confused, upset and frustrated.

What am I missing or not doing right?

So you have a 3 table query. What is critical here are the directions of the "joins" on these tables. In design view, when you link, you should do a left join. Meaning the All records in the customers table, only records in the Rentals table with a matching CustomerID (Linked LEFT Join from Left side Customer table to right side Rentals ON CustomerID). Similarly, you would want All records from Rentals to Only records from Media. (All records from Rentals, only records from Media with matching Media table data on MEDIA fields)

If you use INNER joins (the default when you do a design view link), and the joins do not match properly, you will get NO records.

This can happen if for example, the CUSTOMERID field in the customer table is 9 digits with a leading zero, but hte CustomerID field in the Rentals table is 9 digits with NO leading zero. (NONE will match..)

In short, make sure your linking fields are identical in type, size, and content.

It sounds like the JOINS themselves are linked on data that does not match properly to get NO records.

PS. The caps were not yelling, just emphasis. :)


Double click the line joining the tables and follow those hints first.

[OBP]

Ashley, you have received some great advice in previous posts and I have a couple of questions.
Have you created simple Select Queries for each of the tables alone?
If so do you get the results that you expect?
Have you the tried adding one more table to one of the queries that has results to see what happens?
As Andrew asked in post #3 do you have "Relationships" set between the Tables?

[stanl]

I don't know how much credit you get for really envisioning how a business is run, or perhaps your own experiences with video rentals. If I were grading I would want to see.

1. number of items in on-hand - to handle phone calls
2. status fields - for example the rental is returned, but was damaged, the customer is brand new and must make a security deposit, customer is established, can call in and have a title reserved.

and on and on. It may seem counter-intutitive but the more crap you envision, the clearer normalization becomes. .02 Stan

[austenr]

I agree with Gibbs, when I started out learning Access it was really confusing especially how joins work. This is critical in getting the desired information in your query.

There are excellent resources everywhere on the web that can help you understand how joins work. The information you have received in the above posts and the willingness of those offering to point you in the right direction and offer suggestions should get you where you want to be.

[soma56]

You guys have been great so far. I took a little time-out to enjoy New Years...I will be tackling all of your posts in the next day as it is a lot of information to review.

I really appreciate the help so far. I will read all the previous posts and post my latest 'status' tommorow. Hopefully something will click.

Ashley

[soma56]

Trying very basic steps after reading and still getting nowhere.

Here is my SQL view:


SELECT Customers.ContactLastName, Customers.PhoneNumber, Rentals.Rentals, Rentals.[Return Date], Rentals.Media, Rentals.[Due Date], Rentals.[Rental Date], Rentals.[Media Item Category], Media.GAMES, Media.Title, Media.DVD, Media.VHS, Categories.CategoriesID
FROM Media, (Customers INNER JOIN Rentals ON Customers.CustomerID = Rentals.CustomerID) INNER JOIN Categories ON Rentals.[Media Item Category] = Categories.[Media Item Category]
WHERE (((Rentals.[Return Date]) Is Null))
ORDER BY Customers.ContactLastName, Customers.PhoneNumber, Rentals.Rentals, Rentals.[Return Date], Rentals.Media, Rentals.[Due Date], Rentals.[Rental Date], Rentals.[Media Item Category];

I have records in each table. One record in the rentals table 'return date' is intentionaly blank however when I run the query nothing comes up. Shouldn't that one record appear? What did I do wrong?

[XLGibbs]

SELECT Customers.ContactLastName, Customers.PhoneNumber, Rentals.Rentals,
Rentals.[Return Date], Rentals.Media, Rentals.[Due Date], Rentals.[Rental Date], Rentals.[Media Item Category],
Media.GAMES, Media.Title, Media.DVD, Media.VHS, Categories.CategoriesID
FROM Customers
LEFT JOIN Rentals ON Customers.CustomerID = Rentals.CustomerID
LEFT JOIN Categories ON Rentals.[Media Item Category] = Categories.[Media Item Category]
LEFT JOIN Media on = Rentals.[Media] = Media.[Media]
WHERE (((Rentals.[Return Date]) Is Null))
ORDER BY Customers.ContactLastName, Customers.PhoneNumber, Rentals.Rentals, Rentals.[Return Date], Rentals.Media, Rentals.[Due Date], Rentals.[Rental Date], Rentals.[Media Item Category];


Your SQL was a bit off from what I can tell, try the above

[soma56]

I tried to set up a query with what you have written. When I run it there are no records to show. See for yourself:

http://www.aci.on.ca/%7Ecromarty/VideoStore.mdb

The above link is my database that isn't working.

[XLGibbs]

SELECT Customers.ContactLastName, Customers.PhoneNumber, Rentals.Rentals, Rentals.[Return Date], Rentals.Media, Rentals.[Due Date], Rentals.[Rental Date], Rentals.[Media Item Category], Media.GAMES, Media.Title, Media.DVD, Media.VHS, Categories.CategoriesID
FROM ((Customers LEFT JOIN Rentals ON Customers.CustomerID = Rentals.CustomerID) LEFT JOIN Categories ON Rentals.[Media Item Category] = Categories.[Media Item Category]) LEFT JOIN Media ON Rentals.Media = Media.Media
WHERE (((Rentals.[Return Date]) Is Null));

I was able to get results by properly joining the tables...not sure if they are the right results, but if you paste that into the SQL view, you will see the difference in how the joins are.

I attached the one I altered. Query1 is the one represented by the above SQL

[soma56]

XLGibbs, thank you. I know this is kindergarden material to you however it really means a lot to me. I compared both queries that you sent. The results don't make any sense. My plan was to run a query that brought up any Customers that had their "Return Date" feild blank - a blank feild would mean an outstanding rental. We can make up whatever we want in this project. Instead I'm getting the only customers ID that isn't in the rentals table (Hopper). I'm going to play with it for a bit and see what happens.

Your posts have been encouraging.

[XLGibbs]

Well, start smaller, and we know this stuff can be frustrating which is why we help.

The query results were correct though, Hopper was the only one with a null return date. You have 2 null RENTAL DATES, but all 3 are populated with RETURN DATES. I think you just have your data in the wrong place...the query returned exactly what it was supposed to. Problem is you have 3 returns, but only 1 rental date.

Do simple queries from Customers and Rentals joining on CustomerID.

Bring back all the records from each so you can see how the query system works.

Have a multi table joined query right out of the gate is like going from Kindergarten to the 8th grade right away.


Since this is homework, basically, no one here can really do it for you (and we know you don't expect us to), but we can certainly point you in the right direction.

[soma56]

Ok, so I've figured out how to bring up a table if a specific feild is empty (Is Null) or if I wanted to see a specific customer "Knight" (Although one customer, Hopper, won't seem to show up - I'm trying to figure out why". Which I could use that to show any number of things. My query shows games, VHS and DVD's in each result. I'm guessing this has something to do with with the Categories_ID - Media Item Category. I have a record already established in my rentals table under a Media Item Category linking to Categories_ID - Media Item Category, correct? What is the missing link to present a result from only showing a dvd or game as opposed to all three.

Secondly, shouldn't I have only a number in the rentals table under media (as opposed to the actual title)? This number should link to the media table to bring up the respective title, correct?

[XLGibbs]

Does categories ID link to Media Item Category ID properly? Only you would know if the fields are meant to be connected.

Limiting a result to a specific media type is the adding of criteria for that specific type (either by name or category ID).

On the second, if you have 1 table that has the title, that should be sufficient, because you can always link tables to get corresponding results.

If you set up relationships, which is little beyond where you are right now, you can specify that X from Table A = X From Table B and they kind of "link up" automatically behind the scenes...

If I have time later I will do some work and set up relationships and table queries that you might find useful to learn from.

[Colin]

In database window right mouse clik, click on relationships make the links to each table.

Open query in design, Access will make the links automatically when a table is added. Add your criteria to grid then run the query. If it fails add one table get it working then add another table, continue until all is ok. I managed to get you query working in just a few minutes.
Colin

[soma56]

I think I've figured out the problem with linking up the CategoriesID appropriately however I"m back to square one - when I run a query in query 3 (with what I think has all the tables linked up properly) brings back no results. When I run a query in query 2 it brings back results for which I understand why.

Here it is:
http://www.aci.on.ca/%7Ecromarty/VideoStore.mdb

Query 3 is the improvement on query 2. The part on setting up relationships makes sense. The " Limiting a result to a specific media type is the adding of criteria for that specific type (either by name or category ID)" isn't registering - I'm going to read it over and over for a hundred times and see if something clicks. I know this is the key to having a query bring back a specific title - not my current DVD, VHS, Beta.

[Colin]

It's not a matter of making links to get the query to work. First look at the CustomerTbl CustomerID should be (AutoNumber)
RentalsTbl
RentalID is OK
CustomerID should be (Number not text)
MediaTbl
Should have MediaID and be (AutoNumber)
Add a add another CustomerID field and make it (Number)
CategoriesTbl
This should be a look up table go to CustomerTBL add a field called [Format]and make it into a combo field using the categories data to record DVD, VHS, and Beta.
You will then go to the database window click on the black screen and right mouse click highlight relationship add the tables and link all the relevant ID's when that has been done go to the forms and create a main form with sub forms data can then be added which will be entered sequentially only then will your queries show what you want it to show. A lot of errors will occur if you try and enter data direct into tables.
Good Luck

[austenr]

Open your query3 in design view. Left click on each table join line, right click and select Join Properties then select radio button #2. Click ok. Now run your query.

[Colin]

Because you have linked the relevant Tbls, data entry into Tbls will only be allowed providing data referential integrity is ok. At the moment you have three records in CustomersTbl. One customer has entry in RentalTbl. Try entering data for the other customers? If all is ok when you go to query4 and all customers information should show. I think you will have a problem because data is being entered direct into tables. The database needs to know what rentals each customer is making, it won’t know unless it can identify each rental to each customer. The easiest way to enter data is to create forms for each table you can then have a main form with sub forms attached. You will be able to see data linked to each customer and you will know that all data entered has an identification number. That is why using text in the Data Type box for an ID is not recommended.
Very important
All tables made relevant to your needs
If there are more than one table link the tables to the master table
To enter data make a form or datasheet to enter data
Only then should you make a query you will then see positive result every time.

Good luck
Colin

[soma56]

I think I'm making progress.

Have a look at query 2:

http://www.aci.on.ca/%7Ecromarty/School/Video Store.mdb

Am I on the right track? Did I miss something? Everyone's help has been really great so far.

Ashley

[Colin]

Your no futher forward than when you first posted, start entering data, into the tables. When there is enough data set the criteria and run the query, you will then see for yourself if your on the right track.
:banghead::banghead:

[austenr]

Post your current DB on the site referenced above.

[soma56]

Ok, here are the course instructions:

http://www.aci.on.ca/%7Ecromarty/School/VideoStore.doc

and here is my database so far:

http://www.aci.on.ca/%7Ecromarty/School/VideoStore.mdb

Have I covered the basics of this assignment? What would you recommend that I add?

Ashley

[Colin]

Your instructions is to make a database with a number of tables, this you have done, however, the tables are not linked which means it is not a relational database.

There is data entered into the tables, but on investigation I would say all indexing have been manually inputted into the tblRentals. You have to make a normalised database and you have not done that, when the tables are linked and normalised and data has been correctly entered you can make your queries.

[adam_r_davis]

Hi,

Newbie here so ignore to your hearts content but...

General naming:
I would name tables in the format "tTableName", queries "qQueryName" etc others use the tbl Suffix - whatever as long as it is consistent. Likewise within each table name the ID field something like CategoryID and directly after it have a string description like CategoryDesc. When you have a few hundred tables to play with it helps! Also the naming "Category is not going to help in the future - what about if a marketing person said we need to group customers into Categories... try tMediaCategory. It also sorts related tables together alphabetically. Lastly within the tables I tend to name each column without spaces... you can make it work but "MediaItemCategory" is bulletproof.

Customers table:
CustomerID (not CustomerSID) should be in the singular, it should be the primary key, it should be the first field and change it to an autonumber instead of text even if you have to recreate all your data.

Categories table:
You seem to have duplicated the CategoryID - what is the purpose of 'Media Item Category'?
Also with lookup lists like category, VHS might not be relevant in the future. Say you have a form that will allow the new media to be inputted and also display the older media. You would probably want a combo box to select the currently available Categories but not the older ones... adding a Hide (Yes/No default No) to your categories table would enable this along with a record source for the combo box along the lines of [select distinct current category for this Media record] UNION [ select * from qCategoryList].
Also a SortOrder can be useful in list type tables. So qCategoryList might end up being SELECT CategoryID, CategoryDesc WHERE Hide = false ORDER BY SortOrder.
Also by embedding the logic for the lookup into a query rather than the user form you can get more intelligent in the future, perhaps a certain supplier only supplies games... the qCategoryList can be recoded to handle this and the forms aren't changed.

Media Table:
This is not fully normailised because the item can only be one of VHS, DVD or GAME. Use CateroryID instead... From table design add a record called CategoryID then make the type LookupWizard... follow the steps. This also creates the linking others have described between the Media table and the Category table.

Rentals table:
I think this should be split into two tables... Rental (or a better name tSale) and Rental item (or tSaleItem). Because: Susan Surandon came in on the 11/05/2006 once but hired multiple items. Other fields that may exist in the tSale table might be DiscountAmt, SaleTotal, Tax, Recieved, RecievedType (CreditCard / Cash) etc. Then each of the items ignore the payment type stuff as a parent record agregates that info.
CategoryID is unnessesary it is implied by the MediaID, just remove it.

Cheers,

Adam

[Colin]

soma56 I have made a word doc for you I hope it will help

[Brandtrock]

This site (http://www.databaseanswers.org/data_models/video_rental_store/video_rental_conceptual_v2.htm) was linked in another thread, it has some really helpful information on design of databases.

HTH,

[soma56]

I think I finally got it !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

http://www.aci.on.ca/%7Ecromarty/Video_Store.mdb :friends:

[asingh]

Hi,

I tried to download the above DB..but was not able too. Yes, Access is difficult in the beginning, specially when it returns 'no' data. The key here is to be patient, and I would suggest the following:

1. You have three (3) tables --- Customers ; Rentals ; Media. Dont Query these tables togethar initially. This is just to 'test' your data. Just run simple select queries on these tables [wont take too long]..and see what Access is returning. The reason you are probably not getting recordsets on your complex queries is because your joins..are incorrect or data types do not match...! For example on Customer data run a simple query selecting customers with last name SMITH. Or Customers with phn number as NULL. See how these queries are running.

2. Is the Customer ID in your Rentals and Customers tables exactly the same..data types. Even if there is a padding of zeroes in the beginning...Access will see them differently. 00034 <> 34 for Access. Check the same for MEDIA in the Media and Rentals tables. The key here is that..your table structures should be clear to you...and each data type..should be explicitly defined by you for the tables. If you do not do this..Access takes them as defaults..which at times causes problems when querying. Be really carefull with dates....Access at times...converts them to TEXT....!

3. JOINS. There are three types of joins in Access. For example if you have two tables....TableonLEFT and TableonRIGHT. If you join them..using a common field. Then you double click on the join..you get three options. The INNER JOIN is when you choose the first option. This will only show records...that present in both tables. The LEFT join is when you choose the middle option. This will show ALL records from the LEFT table..and those records from the RIGHT table which equal any record on the LEFT TABLE. So if the left table has 1000 records..and 20 of those...are present in the right table. The left join..will show 1000 recordsets....! And the RIGHT JOIN works the other way round. It will show all records from the right side table..and those matching from the left side table.

Play around with simple SELECT queries...and see how the joins..work....trying out INNER LEFT AND RIGHT. The basic rule is..."if you can SELECT it correctly ---- you can manage it perfectly......!"

take care...and let us know..where else you need help...

regards,

Asingh

[soma56]

Thanks for the last minute advice. I did get it! I got 100% for my grade.

I respect the fact that I had to learn this step by step by myself - rather then then someone here simply doing it for me.

Thanks for everyones help again!