Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make Table Query (XP)

    Never done this B4 - Help! I need to make a table from two tables related one-to-many. It's too big to attach. The relationship is between Work Order numbers. One table has all the work order data - the other table is the names of the people who worked on the work order, their pay rate and how many hours they worked on the particular work order. This table has an Access-generated ID number field.

    I want the table made from any work orders completed prior to January 1, 2003 - there are over 130,000 records in this database. When I leave the relationship intact and run the query, I get only 20 records! If I undo the relationship - what happens?

    If someone can understand this - I'd appreciate any help I can get.

    Thanks

    Linda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table Query (XP)

    Start by creating a selection query (the standard type you get when you create a query in design view) based on the two tables, joined on the work order number field. Don't put anything in the Criteria: line for the moment. Does this query return all the records you expect? If not, there must be something wrong with the join - either the join is on the wrong fields, or the work order number hasn't been filled in correctly.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table Query (XP)

    Thanks Hans - the Work Order Table has 129,969 records. The Labor table has 99,138 records. A select query, using all fields from both tables with the join entact returns 39,003!!!!!!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table Query (XP)

    Is that reasonable? It would mean that most work orders have no labor at all. Not knowing the situation, I can't judge that.

    Anyway, put <#1/1/2003# in the Criteria line for the completion date field. How many records do you get now?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table Query (XP)

    Had to send you a private email - lounge said all my messages were too big!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table Query (XP)

    Did you try to attach a file over 100 KB? Messages can be a lot longer than what you just posted. Anyway, here is the text from your PM:
    <hr>The design of both the work order and labor tables uses the work order # as the primary key. The labor table was designed to have Access automatically generate an ID number. The work order table is the "one" and the labor table is the "many". If I try to "enforce referential integrity" I'm told I can't because possibly there are records relating to an employee in the related table but no record for the employee in the primary table. Edit the data so that records in the primary table exist for all related records.

    Do I need referential integrity? How do I check all these records to make sure there's data in both tables?<hr>
    The labor table shouldn't have just the work order number as primary key, that would create a one-to-one relationship. The labor table should have a primary key consisting of the combination of work order number and employee ID. One work order number can occur many times (with different employee ID's) and one employee ID can occur many times (with different work order numbers.)

    You do need referential integrity, otherwise you'll end up in the situation you have now: poor little orphan records without a parent in the other table. To find those orphan records, you can use the Find Unmatched Query wizard.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table Query (XP)

    We don't have employee ID numbers??? Another suggestion for a 2nd primary key? I'll try the Find Unmatched Query. Thanks for all your help - I think I'm going to suggest she just start another database - done correctly - and start from there.

    Again - thanks.

    Linda

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table Query (XP)

    I assume there must be some kind of unique field to identify an employee. Do you have a separate employee table at all? If you do, and if it doesn't have a primary key, add an AutoNumber field, and use that. You will have to do some work to get the correct values from the employee table into the labor table.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table Query (XP)

    Your assumption would be incorrect - our school district is run by imbeciles, mostly - how sad is that! There has always been an AutoNumber field, but I never thought to use that as the primary key - should I use that along with the work order # do you think?

Posting Permissions

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