Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 41

Thread: Querying multiple tables for a report

  1. #21
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location
    [vba]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));
    [/vba]
    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
    If you have posted the same question at multiple forums, please read this IMPORTANT INFO.

    Please use the thread tools to mark your thread Solved


    Please review the Knowledge Base
    for samples and solutions , or to submit your own!




  2. #22
    VBAX Regular
    Joined
    Dec 2006
    Posts
    14
    Location
    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.

  3. #23
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location
    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.
    If you have posted the same question at multiple forums, please read this IMPORTANT INFO.

    Please use the thread tools to mark your thread Solved


    Please review the Knowledge Base
    for samples and solutions , or to submit your own!




  4. #24
    VBAX Regular
    Joined
    Dec 2006
    Posts
    14
    Location
    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?

  5. #25
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location
    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.
    If you have posted the same question at multiple forums, please read this IMPORTANT INFO.

    Please use the thread tools to mark your thread Solved


    Please review the Knowledge Base
    for samples and solutions , or to submit your own!




  6. #26
    VBAX Regular
    Joined
    Feb 2006
    Posts
    8
    Location
    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

  7. #27
    VBAX Regular
    Joined
    Dec 2006
    Posts
    14
    Location
    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.

  8. #28
    VBAX Regular
    Joined
    Feb 2006
    Posts
    8
    Location
    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

  9. #29
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    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.

  10. #30
    VBAX Regular
    Joined
    Feb 2006
    Posts
    8
    Location

    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

  11. #31
    VBAX Regular
    Joined
    Dec 2006
    Posts
    14
    Location
    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

  12. #32
    VBAX Regular
    Joined
    Feb 2006
    Posts
    8
    Location
    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.

  13. #33
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Post your current DB on the site referenced above.

  14. #34
    VBAX Regular
    Joined
    Dec 2006
    Posts
    14
    Location
    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

  15. #35
    VBAX Regular
    Joined
    Feb 2006
    Posts
    8
    Location
    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.

  16. #36
    VBAX Regular
    Joined
    Jan 2007
    Location
    Sydney
    Posts
    6
    Location

    Suggestions for improvement

    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

  17. #37
    VBAX Regular
    Joined
    Feb 2006
    Posts
    8
    Location
    soma56 I have made a word doc for you I hope it will help
    Last edited by Colin; 01-22-2007 at 10:37 AM. Reason: Make attachment

  18. #38
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    This site was linked in another thread, it has some really helpful information on design of databases.

    HTH,
    Brandtrock




  19. #39
    VBAX Regular
    Joined
    Dec 2006
    Posts
    14
    Location
    I think I finally got it !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

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

  20. #40
    VBAX Mentor asingh's Avatar
    Joined
    Jul 2005
    Posts
    307
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •