Make Table Query (XP)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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!!!!!!

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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:[indent]

---

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?

---

[/indent]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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:[indent]

---

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?

---

[/indent]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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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!!!!!!

Reply | Quote

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.

Reply | Quote