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

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 than necessary.

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 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/ac...474921033.aspx

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

You also need to use criteria in your queries, and this is a useful starting place:
http://office.microsoft.com/en-us/ac...666111033.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_r...ata_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.