Results 1 to 13 of 13
  1. #1
    Join Date
    May 2011
    Location
    New Zealand
    Posts
    11

    Unanswered: Combining data from two different tables into one list box

    I'm just learning how to use access and I have to design a video rental database. If I was designing it just for renting videos I wouldn't be having half the problems that I am. I have to rent games and movies...(and I have to have seperate tables for each tbl_games and tbl_movies. The problem I am currently having is to combine the IDs from tbl_games and tbl_movies into one listbox (called ITEM) on my RENTING OUT form. I think everyone starts off with designing some kind of rental database so hopefully someone can help me.
    Thanks!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You'd combine fields from 2 tables (you have to?) with a UNION query:

    SELECT FieldName
    FROM Table1
    UNION ALL
    SELECT FieldName
    FROM Table2
    Paul

  3. #3
    Join Date
    May 2011
    Location
    New Zealand
    Posts
    11

    Combining data from two different tables into one list box

    Thanks for your reply, Paul. I've tried doing the union query but I'm not sure if I'm doing it wrong. I've created the union query using code as to what you have provided. Then I created the listbox control using the Wizard, I pick the union query that I want the values to come from. Then on the left hand screen Available Fields, the field that appears in the list, is whatever fieldName and Table I wrote in my code first. In this case, its the Game_ID. So, then access asks which column in my listbox contains the value I want to store or use in my database. The only option is Game_ID, so I pick Game_ID.

    So far it works.

    Then, I go into my form in FormView and I enter data to test. I pick a Game to hire, it works. I pick a movie to hire, and I get this error message:

    You cannot add or change a record because a related record is required in table "tblGames".

  4. #4
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Personally, depending on how far along in the design level you are (and if the information in the two tables is basically the same types of data) I would probably combine the games and movies tables into a new Item table if possible. At that point I would use a lookup table to tell the Item table whether an Item is a game or a movie.

    Rough guesstimate on how my tables would look (flying by the seat of my pants):

    tbl_Item
    ---------
    Item_ID
    Item_Name
    Item_Description
    Item_Publisher (or Publisher_ID if you want to create a table to hold publisher names)
    ItemType_ID (DVD, VHS, BluRay, PS3, GameCube, etc etc etc)
    Item_InStock (Yes/No field)

    tbl_ItemType
    -------------
    ItemType_ID
    ItemType_Name
    ItemType_Description

    tbl_Rental
    ----------
    Rental_ID
    Customer_ID

    tbl_RentalItem
    --------------
    Rental_ID
    Item_ID
    DueDate

    Definitely not a complete table layout, but, should express how I would start laying things out.

    Sam, hth
    Good, fast, cheap...Pick 2.

  5. #5
    Join Date
    May 2011
    Location
    New Zealand
    Posts
    11

    Combining data from two different tables into one list box

    Thanks for your reply. Unfortunately, my project specifications require that I have two seperate tables for movies and games. Not making it very easy for me I know!

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Your project specification is daft.

    HOWEVER

    The error message that you've mentioned is triggered by a breach of referential integrity. I'm guessing that you've set up relationships from your games table and videos table to the rental table. This will prevent you from ever setting up a record in the rental table, because what you've effectively done (if I'm right) is to tell the database that each record in the rentals table must link back to both the videos and games tables.

    What you can try is deleting those relationships and setting one up from the union query. In order for this to work, though, the games and videos will need to have totally different keys.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    LoL I agree with Weejas.

    Well if you *must* have two tables, then I would create two junction tables "tbl_RentalMovie" and "tbl_RentalGame" and use those to link the tbl_Movies/tbl_Games, respectively, to the ItemRental table to allow for a m:n relationship, or in this case a none:n.

    That way a movie isn't required for the rental or a game isn't required for a rental. You'll have to do some checking with your code to make sure *something* is rented at that point, but, it would allow you to get away without having to use UNION statements.

    Sam, just another thought
    Good, fast, cheap...Pick 2.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    crap design always causes problems down the line
    ditch what you've done
    have a single table which contains stuff about products
    have sub-tables which contain music films or whatever with a foreign key that refers to the product table
    have an additional sub table again with a foreign key referring to product which identifies a single instance of whatever the product is
    Last edited by healdem; 05-24-11 at 09:28.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    healdem - from the tone of the question, I would imagine that this is an academic assignment or similar, and therefore there will be some things that the OP can't change. Unless, of course, the tutor is an evil SOB who has set the assignment thus to see who will break the rules!

    SCrandell - there's something to your suggestion that I prefer to mine. If both of the Rental tables include the customer and have a numeric key, the ItemRental table would simply need that key, a flag to indicate rental type and the dates (date out, due due back, date actually back).
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just because the academic is setting the example doesn't mean that its not crap design
    the proposal Im suggesting maintains the daft 'two' table design but allows for the sub / super type model, and furthermore allows for a single table holding instances of items actually capable of being rented out... form which I'd expect another two tables (one to hold details of customers and one to indicate what items customers have rented

    looks like a win win to me
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    @Weejas

    Hmm, that's definitely an interesting concept. I was actually shooting more for the primary rental table to hold the customerID and pertinent rental information. Then use the additional two tables to hold the MovieID/GameID, Due Date for that item, rental fees for that item, etc etc. Then have the primary movie/game tables hold their details. Would be a cinch to run an invoice from, historical rental data, etc etc. That way he maintains his project requirements (that we all seem to be in agreement that they're bizarre) but theoretically doesn't break normalization/data flow too badly.

    If my day wasn't blowing up in my face like it is I'd throw an example diagram together. If things settle down today I'll see what I can do. Images always work better than how I put things into words.
    Good, fast, cheap...Pick 2.

  12. #12
    Join Date
    May 2011
    Location
    New Zealand
    Posts
    11

    Combining data from two different tables into one list box

    Thanks heaps for all your replies. Seeing as I'm so new at this, much of what has been sent back and forth went over my head. What I did in the end (and this would probably make my design even crappier!), is I have created two seperate forms for movies and games. On my switchboard I have buttons for renting a movie and returning a movie, renting a game and returning a game. The renting and returning have seperate forms but its based one table (for each). If that makese sense.

    I would've liked to have had a table called tlbITEMS where I could have both movies and games. Sadly, I cannot.

    As an aside, my project specs also require me to have all my forms for data input to be tabular, which SUCKS! I hate the tabular set up.

    If anyone is interested in seeing what I have done so far, I can send my database through...? I am doing my course by correspondence so I send emails to my instructors back and forth, which makes it alot harder. I end up waiting for replies for days... which I why I post questions on the forum.

  13. #13
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I had a class assignment, similar to this, years ago, by what I call a 'real world' instructor, i.e. one who actually worked as a program analyzer/designer as well as working as an instructor, and who emphasized the importance of developers guiding, with a stick, if necessary, the 'suits' setting requirements.

    The prize, for the correct solution, was an A on the final exam. My submitted answer was to inform the 'suits' that their 'idea' was crap (my exact word) and design the program appropriately.

    I didn't have to sit for the final exam!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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