Records Quadrupling in Query - TableDesign Prob? Pls Help

[Ross]

Hi,
I posted this to the querydesign group and was told I may have a
design problem, so I'm posting it here in hope that someone can help.
I am trying to create a recordset from a query to display customer
workorders, parts used, the employee who serviced the account and
amounts to be billed. The results are to be formatted as per customer
request and exported to an Excel spreadsheet.

Here are the tables and fields I am using to build this query:

tbl_Customers
tbl_Customers.FirstName
tbl_Customers.LastName
tbl_Customers.HomePhone

tbl_Employees
tbl_Employees.FirstName
tbl_Employees.LastName

tbl_Workorders
tbl_Workorders.WorkorderID <- primary key
tbl_Workorders.AccountNumber
tbl_Workorders.InstallDate

tbl_WO_Parts
tbl_WO_Parts.PartID
tbl_WO_Parts.SerialNumber

tbl_WO_ClaimAmt
tbl_WO_ClaimAmt.ClaimCode
tbl_WO_ClaimAmt.RequestType
tbl_WO_ClaimAmt.ClaimAmount

When I create the query using both tbl_WO_ClaimAmt and tbl_WO_Parts,
the records returned appear to have quadrupled for each customer.
Running the query with ONLY tbl_WO_Parts and the other tables(omitting
tbl_WO_Claim) returns the correct amount of records for each customer.
Running the query with ONLY tbl_WO_Claim and the other tables(omitting
tbl_WO_Parts) returns the correct amount of records for each customer.
As soon as I add both of these tables to the query is when the record
duplication occurs.

Here is the SQL, I hope it will be helpful:

SELECT DISTINCTROW tbl_WorkorderParts.PartID,
tbl_WorkorderParts.SerialNumber, tbl_Customers.[First Name],
tbl_Customers.[Last Name], tbl_Customers.[Home Phone],
tbl_MIGWorkorder.[Install Date], tbl_Employees.FirstName,
tbl_Employees.LastName, tbl_MIGWorkorder.AccountNum,
tbl_WorkOrderClaimAmt.ClaimCode, tbl_WorkOrderClaimAmt.RequestType,
tbl_WorkOrderClaimAmt.ClaimAmount
FROM (tbl_Employees INNER JOIN ((tbl_Customers INNER JOIN
tbl_MIGWorkorder ON tbl_Customers.CustomerID =
tbl_MIGWorkorder.CustomerID) LEFT JOIN tbl_WorkOrderClaimAmt ON
tbl_MIGWorkorder.WorkOrderID = tbl_WorkOrderClaimAmt.WorkOrderID) ON
tbl_Employees.EmployeeID = tbl_MIGWorkorder.EmployeerID) INNER JOIN
tbl_WorkorderParts ON tbl_MIGWorkorder.WorkOrderID =
tbl_WorkorderParts.WorkorderID;

I'm not sure if the order I've added the fields makes a difference
here, but this is how the client wants the data to appear.

Each workorder only has 1 CustomerID and 1 WorkorderID, but as you can
imagine many different parts and serial numbers and many different
claim codes and prices can be associated to 1 customer.
So we might have something like:

WorkOrderID
CustomerName
AccountNumber
PartID1
PartID2
PartID3
SerialNum1
SerialNum2
SerialNum3
ClaimCode1
ClaimCode2
ClaimCode3
ClaimAmount1
ClaimAmount2
ClaimAmount3

As noted earlier omitting either the tbl_Parts or tbl_ClaimAmt from
the query produces the correct results. But as soon as both
tables.fields are used the records quadruple. I'v also tried creating
the left joins in seperate queries and using those to generate the
records I need, this also produces the duplicate records.

Any help would be greatly appreciated.

Best Regards,
Ross

 

[Guest]

Where does tbl_MIGWorkorder come from? Or did I miss something?

Hi,
I posted this to the querydesign group and was told I may have a
design problem, so I'm posting it here in hope that someone can help.
I am trying to create a recordset from a query to display customer
workorders, parts used, the employee who serviced the account and
amounts to be billed. The results are to be formatted as per customer
request and exported to an Excel spreadsheet.

Here are the tables and fields I am using to build this query:

tbl_Customers
tbl_Customers.FirstName
tbl_Customers.LastName
tbl_Customers.HomePhone

tbl_Employees
tbl_Employees.FirstName
tbl_Employees.LastName

tbl_Workorders
tbl_Workorders.WorkorderID <- primary key
tbl_Workorders.AccountNumber
tbl_Workorders.InstallDate

tbl_WO_Parts
tbl_WO_Parts.PartID
tbl_WO_Parts.SerialNumber

tbl_WO_ClaimAmt
tbl_WO_ClaimAmt.ClaimCode
tbl_WO_ClaimAmt.RequestType
tbl_WO_ClaimAmt.ClaimAmount

When I create the query using both tbl_WO_ClaimAmt and tbl_WO_Parts,
the records returned appear to have quadrupled for each customer.
Running the query with ONLY tbl_WO_Parts and the other tables(omitting
tbl_WO_Claim) returns the correct amount of records for each customer.
Running the query with ONLY tbl_WO_Claim and the other tables(omitting
tbl_WO_Parts) returns the correct amount of records for each customer.
As soon as I add both of these tables to the query is when the record
duplication occurs.

Here is the SQL, I hope it will be helpful:

SELECT DISTINCTROW tbl_WorkorderParts.PartID,
tbl_WorkorderParts.SerialNumber, tbl_Customers.[First Name],
tbl_Customers.[Last Name], tbl_Customers.[Home Phone],
tbl_MIGWorkorder.[Install Date], tbl_Employees.FirstName,
tbl_Employees.LastName, tbl_MIGWorkorder.AccountNum,
tbl_WorkOrderClaimAmt.ClaimCode, tbl_WorkOrderClaimAmt.RequestType,
tbl_WorkOrderClaimAmt.ClaimAmount
FROM (tbl_Employees INNER JOIN ((tbl_Customers INNER JOIN
tbl_MIGWorkorder ON tbl_Customers.CustomerID =
tbl_MIGWorkorder.CustomerID) LEFT JOIN tbl_WorkOrderClaimAmt ON
tbl_MIGWorkorder.WorkOrderID = tbl_WorkOrderClaimAmt.WorkOrderID) ON
tbl_Employees.EmployeeID = tbl_MIGWorkorder.EmployeerID) INNER JOIN
tbl_WorkorderParts ON tbl_MIGWorkorder.WorkOrderID =
tbl_WorkorderParts.WorkorderID;

I'm not sure if the order I've added the fields makes a difference
here, but this is how the client wants the data to appear.

Each workorder only has 1 CustomerID and 1 WorkorderID, but as you can
imagine many different parts and serial numbers and many different
claim codes and prices can be associated to 1 customer.
So we might have something like:

WorkOrderID
CustomerName
AccountNumber
PartID1
PartID2
PartID3
SerialNum1
SerialNum2
SerialNum3
ClaimCode1
ClaimCode2
ClaimCode3
ClaimAmount1
ClaimAmount2
ClaimAmount3

As noted earlier omitting either the tbl_Parts or tbl_ClaimAmt from
the query produces the correct results. But as soon as both
tables.fields are used the records quadruple. I'v also tried creating
the left joins in seperate queries and using those to generate the
records I need, this also produces the duplicate records.

Any help would be greatly appreciated.

Best Regards,
Ross

 

[Guest]

You have fields in the query that are not in the tables ---
tbl_Customers.CustomerID
tbl_MIGWorkorder.CustomerID
tbl_WorkOrderClaimAmt.WorkOrderID

Your table names do not match the tables listed.

Your tables do not have fields that relate to the other tables, That is
based on the field names.

Hi,
I posted this to the querydesign group and was told I may have a
design problem, so I'm posting it here in hope that someone can help.
I am trying to create a recordset from a query to display customer
workorders, parts used, the employee who serviced the account and
amounts to be billed. The results are to be formatted as per customer
request and exported to an Excel spreadsheet.

Here are the tables and fields I am using to build this query:

tbl_Customers
tbl_Customers.FirstName
tbl_Customers.LastName
tbl_Customers.HomePhone

tbl_Employees
tbl_Employees.FirstName
tbl_Employees.LastName

tbl_Workorders
tbl_Workorders.WorkorderID <- primary key
tbl_Workorders.AccountNumber
tbl_Workorders.InstallDate

tbl_WO_Parts
tbl_WO_Parts.PartID
tbl_WO_Parts.SerialNumber

tbl_WO_ClaimAmt
tbl_WO_ClaimAmt.ClaimCode
tbl_WO_ClaimAmt.RequestType
tbl_WO_ClaimAmt.ClaimAmount

When I create the query using both tbl_WO_ClaimAmt and tbl_WO_Parts,
the records returned appear to have quadrupled for each customer.
Running the query with ONLY tbl_WO_Parts and the other tables(omitting
tbl_WO_Claim) returns the correct amount of records for each customer.
Running the query with ONLY tbl_WO_Claim and the other tables(omitting
tbl_WO_Parts) returns the correct amount of records for each customer.
As soon as I add both of these tables to the query is when the record
duplication occurs.

Here is the SQL, I hope it will be helpful:

SELECT DISTINCTROW tbl_WorkorderParts.PartID,
tbl_WorkorderParts.SerialNumber, tbl_Customers.[First Name],
tbl_Customers.[Last Name], tbl_Customers.[Home Phone],
tbl_MIGWorkorder.[Install Date], tbl_Employees.FirstName,
tbl_Employees.LastName, tbl_MIGWorkorder.AccountNum,
tbl_WorkOrderClaimAmt.ClaimCode, tbl_WorkOrderClaimAmt.RequestType,
tbl_WorkOrderClaimAmt.ClaimAmount
FROM (tbl_Employees INNER JOIN ((tbl_Customers INNER JOIN
tbl_MIGWorkorder ON tbl_Customers.CustomerID =
tbl_MIGWorkorder.CustomerID) LEFT JOIN tbl_WorkOrderClaimAmt ON
tbl_MIGWorkorder.WorkOrderID = tbl_WorkOrderClaimAmt.WorkOrderID) ON
tbl_Employees.EmployeeID = tbl_MIGWorkorder.EmployeerID) INNER JOIN
tbl_WorkorderParts ON tbl_MIGWorkorder.WorkOrderID =
tbl_WorkorderParts.WorkorderID;

I'm not sure if the order I've added the fields makes a difference
here, but this is how the client wants the data to appear.

Each workorder only has 1 CustomerID and 1 WorkorderID, but as you can
imagine many different parts and serial numbers and many different
claim codes and prices can be associated to 1 customer.
So we might have something like:

WorkOrderID
CustomerName
AccountNumber
PartID1
PartID2
PartID3
SerialNum1
SerialNum2
SerialNum3
ClaimCode1
ClaimCode2
ClaimCode3
ClaimAmount1
ClaimAmount2
ClaimAmount3

As noted earlier omitting either the tbl_Parts or tbl_ClaimAmt from
the query produces the correct results. But as soon as both
tables.fields are used the records quadruple. I'v also tried creating
the left joins in seperate queries and using those to generate the
records I need, this also produces the duplicate records.

Any help would be greatly appreciated.

Best Regards,
Ross

 

[mmrr]

You have fields in the query that are not in the tables ---
tbl_Customers.CustomerID
tbl_MIGWorkorder.CustomerID
tbl_WorkOrderClaimAmt.WorkOrderID

Your table names do not match the tables listed.

Your tables do not have fields that relate to the other tables, That is
based on the field names.

I'm sorry for the confusion. I have done some tuning of the tables
since I first posted this last week. I should have updated the post
instead of just re-sending it.

Here is the proper SQL, all tables and fields do indeed exist.

SELECT DISTINCTROW tbl_WorkorderParts.PartID,
tbl_WorkorderParts.SerialNumber, tbl_MIGWorkorder.WorkOrderID,
tbl_Customers.[First Name], tbl_Customers.[Last Name],
tbl_Customers.[Home Phone], tbl_MIGWorkorder.[Install Date],
tbl_Employees.EmpFirstName, tbl_Employees.EmpLastName,
tbl_MIGWorkorder.AccountNum, tbl_WorkOrderClaimAmt.ClaimCode,
tbl_WorkOrderClaimAmt.RequestType, tbl_WorkOrderClaimAmt.ClaimAmount
FROM ((tbl_Customers INNER JOIN (tbl_Employees INNER JOIN
tbl_MIGWorkorder ON tbl_Employees.EmployeeID =
tbl_MIGWorkorder.EmployeerID) ON tbl_Customers.CustomerID =
tbl_MIGWorkorder.CustomerID) INNER JOIN tbl_WorkorderParts ON
tbl_MIGWorkorder.WorkOrderID = tbl_WorkorderParts.WorkorderID) INNER
JOIN tbl_WorkOrderClaimAmt ON tbl_MIGWorkorder.WorkOrderID =
tbl_WorkOrderClaimAmt.WorkOrderID
WHERE (((tbl_MIGWorkorder.[Install Date]) Between #1/23/2006# And
#2/4/2006#) AND ((tbl_MIGWorkorder.Status)="Closed"));

As I stated in my previous post, if I remove either tbl_WorkorderParts
or tbl_WorkOrderClaimAmt the records are returned as expected.
I also have created a query to perform the left join and used it in
the above to see if this would make a difference, it doesn't.


SQl:
SELECT tbl_WorkOrderClaimAmt.ClaimCode,
tbl_WorkOrderClaimAmt.RequestType, tbl_WorkOrderClaimAmt.ClaimAmount,
tbl_WorkOrderClaimAmt.cxPaid, tbl_MIGWorkorder.WorkOrderID,
tbl_MIGWorkorder.[Install Date], tbl_MIGWorkorder.Status
FROM tbl_MIGWorkorder LEFT JOIN tbl_WorkOrderClaimAmt ON
tbl_MIGWorkorder.WorkOrderID=tbl_WorkOrderClaimAmt.WorkOrderID
WHERE (((tbl_MIGWorkorder.[Install Date]) Between #1/23/2006# And
#2/4/2006#) AND ((tbl_MIGWorkorder.Status)="closed"));

I am a complete loss. Any ideas?

Thanks,
Ross

Hi,
I posted this to the querydesign group and was told I may have a
design problem, so I'm posting it here in hope that someone can help.
I am trying to create a recordset from a query to display customer
workorders, parts used, the employee who serviced the account and
amounts to be billed. The results are to be formatted as per customer
request and exported to an Excel spreadsheet.

Here are the tables and fields I am using to build this query:

tbl_Customers
tbl_Customers.FirstName
tbl_Customers.LastName
tbl_Customers.HomePhone

tbl_Employees
tbl_Employees.FirstName
tbl_Employees.LastName

tbl_MIGWorkorders
tbl_Workorders.WorkorderID <- primary key
tbl_Workorders.AccountNumber
tbl_Workorders.InstallDate

tbl_WO_Parts
tbl_WO_Parts.PartID
tbl_WO_Parts.SerialNumber

tbl_WO_ClaimAmt
tbl_WO_ClaimAmt.ClaimCode
tbl_WO_ClaimAmt.RequestType
tbl_WO_ClaimAmt.ClaimAmount

When I create the query using both tbl_WO_ClaimAmt and tbl_WO_Parts,
the records returned appear to have quadrupled for each customer.
Running the query with ONLY tbl_WO_Parts and the other tables(omitting
tbl_WO_Claim) returns the correct amount of records for each customer.
Running the query with ONLY tbl_WO_Claim and the other tables(omitting
tbl_WO_Parts) returns the correct amount of records for each customer.
As soon as I add both of these tables to the query is when the record
duplication occurs.

Here is the SQL, I hope it will be helpful:

SELECT DISTINCTROW tbl_WorkorderParts.PartID,
tbl_WorkorderParts.SerialNumber, tbl_Customers.[First Name],
tbl_Customers.[Last Name], tbl_Customers.[Home Phone],
tbl_MIGWorkorder.[Install Date], tbl_Employees.FirstName,
tbl_Employees.LastName, tbl_MIGWorkorder.AccountNum,
tbl_WorkOrderClaimAmt.ClaimCode, tbl_WorkOrderClaimAmt.RequestType,
tbl_WorkOrderClaimAmt.ClaimAmount
FROM (tbl_Employees INNER JOIN ((tbl_Customers INNER JOIN
tbl_MIGWorkorder ON tbl_Customers.CustomerID =
tbl_MIGWorkorder.CustomerID) LEFT JOIN tbl_WorkOrderClaimAmt ON
tbl_MIGWorkorder.WorkOrderID = tbl_WorkOrderClaimAmt.WorkOrderID) ON
tbl_Employees.EmployeeID = tbl_MIGWorkorder.EmployeerID) INNER JOIN
tbl_WorkorderParts ON tbl_MIGWorkorder.WorkOrderID =
tbl_WorkorderParts.WorkorderID;

I'm not sure if the order I've added the fields makes a difference
here, but this is how the client wants the data to appear.

Each workorder only has 1 CustomerID and 1 WorkorderID, but as you can
imagine many different parts and serial numbers and many different
claim codes and prices can be associated to 1 customer.
So we might have something like:

WorkOrderID
CustomerName
AccountNumber
PartID1
PartID2
PartID3
SerialNum1
SerialNum2
SerialNum3
ClaimCode1
ClaimCode2
ClaimCode3
ClaimAmount1
ClaimAmount2
ClaimAmount3

As noted earlier omitting either the tbl_Parts or tbl_ClaimAmt from
the query produces the correct results. But as soon as both
tables.fields are used the records quadruple. I'v also tried creating
the left joins in seperate queries and using those to generate the
records I need, this also produces the duplicate records.

Any help would be greatly appreciated.

Best Regards,
Ross

 

[mmrr]

Where does tbl_MIGWorkorder come from? Or did I miss something?

Sorry my mistake, I've updated in another post.

Thanks for taking a look.

Ross

 

[Guest]

So what is the table structure?

You have fields in the query that are not in the tables ---
tbl_Customers.CustomerID
tbl_MIGWorkorder.CustomerID
tbl_WorkOrderClaimAmt.WorkOrderID

Your table names do not match the tables listed.

Your tables do not have fields that relate to the other tables, That is
based on the field names.

I'm sorry for the confusion. I have done some tuning of the tables
since I first posted this last week. I should have updated the post
instead of just re-sending it.

Here is the proper SQL, all tables and fields do indeed exist.

SELECT DISTINCTROW tbl_WorkorderParts.PartID,
tbl_WorkorderParts.SerialNumber, tbl_MIGWorkorder.WorkOrderID,
tbl_Customers.[First Name], tbl_Customers.[Last Name],
tbl_Customers.[Home Phone], tbl_MIGWorkorder.[Install Date],
tbl_Employees.EmpFirstName, tbl_Employees.EmpLastName,
tbl_MIGWorkorder.AccountNum, tbl_WorkOrderClaimAmt.ClaimCode,
tbl_WorkOrderClaimAmt.RequestType, tbl_WorkOrderClaimAmt.ClaimAmount
FROM ((tbl_Customers INNER JOIN (tbl_Employees INNER JOIN
tbl_MIGWorkorder ON tbl_Employees.EmployeeID =
tbl_MIGWorkorder.EmployeerID) ON tbl_Customers.CustomerID =
tbl_MIGWorkorder.CustomerID) INNER JOIN tbl_WorkorderParts ON
tbl_MIGWorkorder.WorkOrderID = tbl_WorkorderParts.WorkorderID) INNER
JOIN tbl_WorkOrderClaimAmt ON tbl_MIGWorkorder.WorkOrderID =
tbl_WorkOrderClaimAmt.WorkOrderID
WHERE (((tbl_MIGWorkorder.[Install Date]) Between #1/23/2006# And
#2/4/2006#) AND ((tbl_MIGWorkorder.Status)="Closed"));

As I stated in my previous post, if I remove either tbl_WorkorderParts
or tbl_WorkOrderClaimAmt the records are returned as expected.
I also have created a query to perform the left join and used it in
the above to see if this would make a difference, it doesn't.


SQl:
SELECT tbl_WorkOrderClaimAmt.ClaimCode,
tbl_WorkOrderClaimAmt.RequestType, tbl_WorkOrderClaimAmt.ClaimAmount,
tbl_WorkOrderClaimAmt.cxPaid, tbl_MIGWorkorder.WorkOrderID,
tbl_MIGWorkorder.[Install Date], tbl_MIGWorkorder.Status
FROM tbl_MIGWorkorder LEFT JOIN tbl_WorkOrderClaimAmt ON
tbl_MIGWorkorder.WorkOrderID=tbl_WorkOrderClaimAmt.WorkOrderID
WHERE (((tbl_MIGWorkorder.[Install Date]) Between #1/23/2006# And
#2/4/2006#) AND ((tbl_MIGWorkorder.Status)="closed"));

I am a complete loss. Any ideas?

Thanks,
Ross

Hi,
I posted this to the querydesign group and was told I may have a
design problem, so I'm posting it here in hope that someone can help.
I am trying to create a recordset from a query to display customer
workorders, parts used, the employee who serviced the account and
amounts to be billed. The results are to be formatted as per customer
request and exported to an Excel spreadsheet.

Here are the tables and fields I am using to build this query:

tbl_Customers
tbl_Customers.FirstName
tbl_Customers.LastName
tbl_Customers.HomePhone

tbl_Employees
tbl_Employees.FirstName
tbl_Employees.LastName

tbl_MIGWorkorders
tbl_Workorders.WorkorderID <- primary key
tbl_Workorders.AccountNumber
tbl_Workorders.InstallDate

tbl_WO_Parts
tbl_WO_Parts.PartID
tbl_WO_Parts.SerialNumber

tbl_WO_ClaimAmt
tbl_WO_ClaimAmt.ClaimCode
tbl_WO_ClaimAmt.RequestType
tbl_WO_ClaimAmt.ClaimAmount

When I create the query using both tbl_WO_ClaimAmt and tbl_WO_Parts,
the records returned appear to have quadrupled for each customer.
Running the query with ONLY tbl_WO_Parts and the other tables(omitting
tbl_WO_Claim) returns the correct amount of records for each customer.
Running the query with ONLY tbl_WO_Claim and the other tables(omitting
tbl_WO_Parts) returns the correct amount of records for each customer.
As soon as I add both of these tables to the query is when the record
duplication occurs.

Here is the SQL, I hope it will be helpful:

SELECT DISTINCTROW tbl_WorkorderParts.PartID,
tbl_WorkorderParts.SerialNumber, tbl_Customers.[First Name],
tbl_Customers.[Last Name], tbl_Customers.[Home Phone],
tbl_MIGWorkorder.[Install Date], tbl_Employees.FirstName,
tbl_Employees.LastName, tbl_MIGWorkorder.AccountNum,
tbl_WorkOrderClaimAmt.ClaimCode, tbl_WorkOrderClaimAmt.RequestType,
tbl_WorkOrderClaimAmt.ClaimAmount
FROM (tbl_Employees INNER JOIN ((tbl_Customers INNER JOIN
tbl_MIGWorkorder ON tbl_Customers.CustomerID =
tbl_MIGWorkorder.CustomerID) LEFT JOIN tbl_WorkOrderClaimAmt ON
tbl_MIGWorkorder.WorkOrderID = tbl_WorkOrderClaimAmt.WorkOrderID) ON
tbl_Employees.EmployeeID = tbl_MIGWorkorder.EmployeerID) INNER JOIN
tbl_WorkorderParts ON tbl_MIGWorkorder.WorkOrderID =
tbl_WorkorderParts.WorkorderID;

I'm not sure if the order I've added the fields makes a difference
here, but this is how the client wants the data to appear.

Each workorder only has 1 CustomerID and 1 WorkorderID, but as you can
imagine many different parts and serial numbers and many different
claim codes and prices can be associated to 1 customer.
So we might have something like:

WorkOrderID
CustomerName
AccountNumber
PartID1
PartID2
PartID3
SerialNum1
SerialNum2
SerialNum3
ClaimCode1
ClaimCode2
ClaimCode3
ClaimAmount1
ClaimAmount2
ClaimAmount3

As noted earlier omitting either the tbl_Parts or tbl_ClaimAmt from
the query produces the correct results. But as soon as both
tables.fields are used the records quadruple. I'v also tried creating
the left joins in seperate queries and using those to generate the
records I need, this also produces the duplicate records.

Any help would be greatly appreciated.

Best Regards,
Ross

 

[Guest]

So what is the table structure?

Here are the relevant tables and fields:


tbl_Customers
tbl_Customers.CustomerID (primary key)
tbl_Customers.FirstName
tbl_Customers.LastName
tbl_Customers.HomePhone

tbl_Employees
tbl_Employees.EmployeeID (primary key)
tbl_Employees.EMPFirstName
tbl_Employees.EMPLastName

tbl_MIGWorkorders
tbl_MIGWorkorders.WorkorderID (primary key)
tbl_MIGWorkorders.CustomerId (foreign key)
tbl_MIGWorkorders.EmployeeID (foreign key)
tbl_MIGWorkorders.AccountNum
tbl_MIGWorkorders.[Install Date]

tbl_WorkorderParts
tbl_WorkorderParts.WorkOrderPartID (primary Key)
tbl_WorkorderParts.WorkorderId (foreign key)
tbl_WorkorderParts.PartId (foreign key, not used in query)
tbl_WorkorderParts.WOPartID
tbl_WorkorderParts.SerialNumber

tbl_WorkOrderClaimAmt
tbl_WorkOrderClaimAmt.ClaimCodeID (primary key)
tbl_WorkOrderClaimAmt.WorkOrderID (foreign key)
tbl_WorkOrderClaimAmt.CodeID (foreign key, not used in query)
tbl_WorkOrderClaimAmt.ClaimCode
tbl_WorkOrderClaimAmt.RequestType
tbl_WorkOrderClaimAmt.ClaimAmount
tbl_WorkOrderClaimAmt.cxPaid

When I create the query using both tbl_WorkOrderClaimAmt and
tbl_WorkorderParts, the records returned appear to have quadrupled for
each customer. Running the query with ONLY tbl_WorkorderParts and the
other tables(omitting tbl_WorkOrderClaimAmt) returns the correct
amount of records for each customer. Running the query with ONLY
tbl_WorkOrderClaimAmt and the other tables(omitting
tbl_WorkorderParts) returns the correct amount of records for each
customer. As soon as I add both of these tables to the query is when
the record duplication occurs.

Here is the SQL from the query:

SELECT DISTINCTROW tbl_WorkorderParts.RecieverType,
tbl_WorkorderParts.SerialNumber, tbl_MIGWorkorder.WorkOrderID,
tbl_Customers.[First Name], tbl_Customers.[Last Name],
tbl_Customers.[Home Phone], tbl_MIGWorkorder.[Install Date],
tbl_Employees.EmpFirstName, tbl_Employees.EmpLastName,
tbl_MIGWorkorder.AccountNum, tbl_WorkOrderClaimAmt.ClaimCode,
tbl_WorkOrderClaimAmt.RequestType, tbl_WorkOrderClaimAmt.ClaimAmount,
tbl_WorkOrderClaimAmt.cxPaid
FROM ((tbl_Customers INNER JOIN (tbl_Employees INNER JOIN
tbl_MIGWorkorder ON tbl_Employees.EmployeeID =
tbl_MIGWorkorder.EmployeerID) ON tbl_Customers.CustomerID =
tbl_MIGWorkorder.CustomerID) INNER JOIN tbl_WorkorderParts ON
tbl_MIGWorkorder.WorkOrderID = tbl_WorkorderParts.WorkorderID) INNER
JOIN tbl_WorkOrderClaimAmt ON tbl_MIGWorkorder.WorkOrderID =
tbl_WorkOrderClaimAmt.WorkOrderID
WHERE (((tbl_MIGWorkorder.[Install Date]) Between #1/23/2006# And
#2/4/2006#) AND ((tbl_MIGWorkorder.Status)="Closed"));

I have also tried creating left joins in separate queries using
tbl_Workorders and tbl_WorkOrderClaimAmt then using the resulting
query in the main query. Same with tbl_Workorders and
tbl_WorkorderParts. Unfortunately adding both queries also produces
duplicate records.

Hope this helps, please advise if you need more info.

Thanks for your time.

Ross

 

[Guest]

In your database, is there more than one part (tbl_WorkorderParts.PartID) for
each (tbl_MIGWorkorder.WorkorderID)? Because you will have the same number
(that is, there will be multiple instances) of any particular WorkorderID
that you have multiple PartID's. But since you are not showing the PartID's
in your query, you may not have noticed it. If you had continued on to
creating a form from that particular query, you would probably have a subform
that would show those multiple PartID's for each particular WorkorderID.

By the way, the tables and fields that you have do not match the SQL code.
It is much harder to help you when you give us the wrong information.

 

[Amy Blankenship]

He does have a form that does this, and that IS his problem, as I told him
in the query NG. What I told him to do is post back with the business
requirements his tables are intended to fulfill so that maybe the people on
here could help him redesign them to actually meet those requirements rather
than fixing a query problem that CANNOT be resolved because the DESIGN IS
WRONG!

Gees.

 

[Guest]

Ross, every time you post something it is different, and the tables you post
are not the same as the SQL codes you are posting. Amy is right, you need to
tell us what you are trying to do, rather than posting erroneous information
and expecting us to miraculously pull a rabbit out of the hat. Since you do
not seem to understand what is going on, you need to start at the beginning,
rather than jumping into the middle of database creation and expecting it to
just work.

We need to know the basis for this database. What raw information do you
put in? What sort of information do you expect to come out? Designing
tables is the single biggest part of a successful database. If they are
wrong, nothing you do will make them right.

From what I can see, you have customers, employees, work orders, parts and
claim amounts. Can you have multiple parts per work order? Can you have
multiple claim amounts per work order? Are the parts tied in to the claim
amounts? Why do you need a work order part ID, as well as a part ID? Is the
Account number tied to a customer, or to a work order? Why would an account
number be tied to a work order? Do you have a code ID table? If there can
be multiple part ID's for a work order, is there a table for part ID's? Why
are you trying to set up a query that calls on both the work order parts
table, and work order claimant table, since those two tables are not directly
connected in any way?

 

[Guest]

Ross, every time you post something it is different, and the tables you post
are not the same as the SQL codes you are posting. Amy is right, you need to
tell us what you are trying to do, rather than posting erroneous information
and expecting us to miraculously pull a rabbit out of the hat. Since you do
not seem to understand what is going on, you need to start at the beginning,
rather than jumping into the middle of database creation and expecting it to
just work.

First off, let me apologize to you and Amy for being a bit dense.
In my original post to the queries group, I thought I had provided
enough information to help others understand what I was trying to do,
obviously this is not the case. I'll try again.

We need to know the basis for this database. What raw information do you
put in? What sort of information do you expect to come out? Designing
tables is the single biggest part of a successful database. If they are
wrong, nothing you do will make them right.

The purpose of the database is: to record all the initial information
required to service an account, to provide an interface for the
installers to complete a workorder so reports can be created.
The primary business is a satellite television installation company.
Initially they get a request from the satellite tv company to perform
an installation. The info is never complete because some of it
(contract length, account number and other information) is in the
hands of the customer. The installer will only get this information
upon arrival at the customer address.
The initial information received is:

Customer tombstone information.
Retailer name, location and sales person.
Retailers are places like Best Buy, Visions, etc, they sell home
theater and satellite equipment and provide some of the initial
information to the satellite tv companies; rental agreement numbers,
and receiver model numbers, number of receivers, the type of
installation to be performed (whether it is a rental install, service
call, move, upgrade etc). This information is then passed on to us.

Dispatch will then call the customer to confirm the install date,
gather some information about the property (lines of site, access to
areas for wiring, what type of building, do they own or rent (this is
required for access to the building) roof pitch, and other information
relating to the property.

This information is entered into the database and an initial workorder
is posted to the installer.

Upon arrival at a customer location the installer will receive the
account number, the receivers serial numbers, smart card numbers, and
software revisions. He will record the signal strength and the actual
install date, his time in and time out. This information then needs to
be entered into the database in order to track the business and create
the report. Also any changes to the initial workorder need to be
recorded, these will only be available from the customer and passed on
to the installer. Eventually this information will be entered directly
into the database online by the installers.

From what I can see, you have customers, employees, work orders, parts and
claim amounts. Can you have multiple parts per work order?
Yes.

Can you have multiple claim amounts per work order?

Yes. For instance an initial rental install is one type of claim and
covers 1 receiver. The customer may have several televisions and
require several receivers, this will involve both claim amounts and
parts.

Are the parts tied in to the claim amounts?

Yes. See below.

Why do you need a work order part ID, as well as a part ID?

I believed that I had to have a "live" table that tracked what part
was used and the serial number of the parts used in each installation.
There are a limited number of parts but an infinite a amount of serial
numbers, I thought I needed to separate the parts look-up table from
the parts/serial numbers that actually get used on an installation.

Is the Account number tied to a customer, or to a work order?
Why would an account number be tied to a work order?

Some customers have both Bell and Star Choice tv service, so I thought
it best to record this in the workorder table and tie it to the
customer id. Although not all that common, it does happen.

Do you have a code ID table?

Yes. Another table I use as a lookup.

If there can be multiple part ID's for a work order, is there a table for part ID's?

Yes. I created a Parts table to be used as lookup for parts used on a
workorder. The parts used on a workorder have to have a serial number
associated with them.

Why are you trying to set up a query that calls on both the work order parts
table, and work order claimant table, since those two tables are not directly
connected in any way?

They are connected in that if more than 1 part is used, then a
corresponding claim must be made in order for the company to be paid.
The client requires that information to report back to the satellite
tv company for billing.

For instance, a customer orders a rental install, an additional
receiver and a new part for his satellite dish:

Model: 4100
Serial: 1234567
ClaimCode: RC5
RequestType: Rental Install

Model: 4100
Serial: 2345678
ClaimCode: RC5
RequestType: Secondary

Model: 3830
Serial: 3456789
ClaimCode: RC6
RequestType: LNBF

Claims must be made for each receiver, or other parts used on the
installation as they are a determining factor in the amounts to be
paid for an installation.

I realize I've FUBAR'ed the design and haven't been very clear in my
requests for help. I hope this makes more sense than my previous
attempts. I'm fairly new to this so I hope you will forgive my initial
stupidity. Any assistance you can provide to help me learn the correct
way of doing things is greatly appreciated. Thanks for your patience.

Regards,
Ross

 

[Pat Hartman\(MVP\)]

Aside from the query/table definition conflicts and the lack of relevant
foreign keys, you need to understand that when the table design is
finalized, you are still not going to be able to create a SINGLE query to
return data from all of these tables at once. The reason for that is that
there are separate 1-many relationships in your structure. What is the
purpose of exporting to Excel? Would an Access report suffice? If a report
will do, you will need to make a main report with one or more subreports to
handle the separate 1-many relationships without duplicating data.

Hi,
I posted this to the querydesign group and was told I may have a
design problem, so I'm posting it here in hope that someone can help.
I am trying to create a recordset from a query to display customer
workorders, parts used, the employee who serviced the account and
amounts to be billed. The results are to be formatted as per customer
request and exported to an Excel spreadsheet.

Here are the tables and fields I am using to build this query:

tbl_Customers
tbl_Customers.FirstName
tbl_Customers.LastName
tbl_Customers.HomePhone

tbl_Employees
tbl_Employees.FirstName
tbl_Employees.LastName

tbl_Workorders
tbl_Workorders.WorkorderID <- primary key
tbl_Workorders.AccountNumber
tbl_Workorders.InstallDate

tbl_WO_Parts
tbl_WO_Parts.PartID
tbl_WO_Parts.SerialNumber

tbl_WO_ClaimAmt
tbl_WO_ClaimAmt.ClaimCode
tbl_WO_ClaimAmt.RequestType
tbl_WO_ClaimAmt.ClaimAmount

When I create the query using both tbl_WO_ClaimAmt and tbl_WO_Parts,
the records returned appear to have quadrupled for each customer.
Running the query with ONLY tbl_WO_Parts and the other tables(omitting
tbl_WO_Claim) returns the correct amount of records for each customer.
Running the query with ONLY tbl_WO_Claim and the other tables(omitting
tbl_WO_Parts) returns the correct amount of records for each customer.
As soon as I add both of these tables to the query is when the record
duplication occurs.

Here is the SQL, I hope it will be helpful:

SELECT DISTINCTROW tbl_WorkorderParts.PartID,
tbl_WorkorderParts.SerialNumber, tbl_Customers.[First Name],
tbl_Customers.[Last Name], tbl_Customers.[Home Phone],
tbl_MIGWorkorder.[Install Date], tbl_Employees.FirstName,
tbl_Employees.LastName, tbl_MIGWorkorder.AccountNum,
tbl_WorkOrderClaimAmt.ClaimCode, tbl_WorkOrderClaimAmt.RequestType,
tbl_WorkOrderClaimAmt.ClaimAmount
FROM (tbl_Employees INNER JOIN ((tbl_Customers INNER JOIN
tbl_MIGWorkorder ON tbl_Customers.CustomerID =
tbl_MIGWorkorder.CustomerID) LEFT JOIN tbl_WorkOrderClaimAmt ON
tbl_MIGWorkorder.WorkOrderID = tbl_WorkOrderClaimAmt.WorkOrderID) ON
tbl_Employees.EmployeeID = tbl_MIGWorkorder.EmployeerID) INNER JOIN
tbl_WorkorderParts ON tbl_MIGWorkorder.WorkOrderID =
tbl_WorkorderParts.WorkorderID;

I'm not sure if the order I've added the fields makes a difference
here, but this is how the client wants the data to appear.

Each workorder only has 1 CustomerID and 1 WorkorderID, but as you can
imagine many different parts and serial numbers and many different
claim codes and prices can be associated to 1 customer.
So we might have something like:

WorkOrderID
CustomerName
AccountNumber
PartID1
PartID2
PartID3
SerialNum1
SerialNum2
SerialNum3
ClaimCode1
ClaimCode2
ClaimCode3
ClaimAmount1
ClaimAmount2
ClaimAmount3

As noted earlier omitting either the tbl_Parts or tbl_ClaimAmt from
the query produces the correct results. But as soon as both
tables.fields are used the records quadruple. I'v also tried creating
the left joins in seperate queries and using those to generate the
records I need, this also produces the duplicate records.

Any help would be greatly appreciated.

Best Regards,
Ross

 

[Guest]

Ross, you have obviously been working on this for a while, and have really
gotten quite far into this process of designing and implementing your
database. I'm going to ask you to do something that is very very hard to do,
and that is to go back to the beginning, and look at your table structures,
and how they relate to each other. Forget about queries and forms and things
like that. We need to get the tables correct from the beginning, or nothing
else will work out.

It looks like you have a lot of customer information. I'm going to assume
that your customer table has all of this non-changing information in one
place. You have condensed it in your post just to simplify things. That is
OK. So we have

tbl_Customers
CustomerID (PK)
CustomerFirstName
CustomerLastName
CustomerHomePhone

The reason I am specifying "customer" as part of the field name, is because
you have employees that you are also tracking. It is just good programming
to use unique names whenever possible. With that, we go to your employees
table:

tbl_Employees
EmployeeID (PK)
EmployeeFirstName
EmployeeLastName

Again, I assume this is a shortened version of your actual table. I am
putting in a unique primary key (PK) for each of these tables, just as good
programming practice.

Now, we get to the table for listing your work orders. This is where I need
to stop and ask some specific questions (some of which you may have answered,
but this will keep things in one place). These questions are an important
part of how the tables are going to be designed. The initial design of the
work order table will look like this:

tbl_WorkOrders
WorkOrderID (PK)

Each work order will have a unique primary key, and will apply to a specific
installation of one satellite television. At this point, YOU need to make a
decision about whether there is anything else that should make this work
order unique. That is, should installation of any other devices be
considered under a separate work order? How many devices are normally
installed as part of an installation? Do you need to track those devices in
some way? Do you want to have a primary work order, and then some way of
tracking all of the discrete devices and auxiliary things that need to be
done (such as climbing onto the roof, drilling through walls, tacking cable
along a wall), and perhaps even keeping track of the supplies that are used
during these events. If there is always a certain normal amount of supplies
that are expended, then perhaps you do not need to take note of those in this
database. However, if there is ever a question of why one installation took
30 minutes, and another one took six hours, then it might be good to have a
way of noting some details of the installation.

Something else to think about is dates. You have an installation date.
Would it be handy to also have a date when the order is first placed? Should
there be a follow-up date, to call and ask the customer if everything is
working OK? If there are problems with the installation, do you create a new
work order, or should there be some way of linking repairs into the original
work order?

Then there is the employee who works the order. Is there always just one
for any particular order? What if they get sick, and someone else finishes
the job? If there is a problem with the installation, is it always the same
employee who does repairs?

These are the sorts of things you need to consider as part of designing this
particular table. Depending on how you want to track these various bits of
information will then give us options on how to relate the tables, and how to
create others. After these initial questions are answered, you will then be
ready to create parts and claims tables.

 

[Brian Bastl]

Ross,

don't know which version of Access you're using, but if it's 2003, then
perhaps you should download the Service Call Management database. It won't
necessarily have the functionality you require, but perhaps it'll give you
ideas about how to structure your db.

http://office.microsoft.com/en-us/templates/TC010184671033.aspx?CategoryID=CT011217631033

Brian

 

[Guest]

Ross, you have obviously been working on this for a while, and have really
gotten quite far into this process of designing and implementing your
database. I'm going to ask you to do something that is very very hard to do,
and that is to go back to the beginning, and look at your table structures,
and how they relate to each other. Forget about queries and forms and things
like that. We need to get the tables correct from the beginning, or nothing
else will work out.

First I'd just like to thank you for taking the time to help me design
the database properly. Yes, I'm pretty deep into this, but if I have
to re-design to get it right, I'm fine with that. Hopefully I'll be
able to learn not to make the same mistakes again.

I'm going to consider the questions you've asked very carefully before
I answer. I don't want to waste your time by giving you erroneous
information because I haven't thought it through properly.

I've posted a screenshot of the table relationships at this site:

http://www.mts.net/~mmrr/satex.html

I'm not sure if it will help you understand what I've done so far, it
may only provide you with a good laugh.

I'll post a reply to your questions as soon as I can formulate answers
that will help the most. Thanks again for your time and patience.

Regards,
Ross

 

[Guest]

You have a lot of junction tables. A junction table is one where two other
tables connect to it with one-to-many relationships, with the many going into
the junction table. Some of these make sense, such as your
tbl_WorkorderParts. You can have a workorder that has many parts, and you
can have a part that goes to many workorders. Please apply that logic to all
of your junction tables, and make sure that you really need them.

Logic: You can have a workorder that has many x's, and you can have an x
that goes to many workorders. Fill in the x with the fields from your
junction tables. I see a lot of duplicate fields in your junction tables,
and the table they join to, and I don't always see a logical reason for
having a junction table.

Should not have duplicate fields. Ever. Each table should have special
discrete information that fits naturally with all the other information in
the table. Really look at your tables, and decide what information should go
into those tables. You might even print out your relationship chart, and
make notes on it for what you expect to see in those tables.

Now, when you start creating queries, there should be a reason for the
query. You should not just dump all information into the query. A query
should be based on a question that you are asking. It could be as simple as
how many people own their own home versus renting. It could be a little more
complicated, such as how much is being spent on parts, depending on who the
retail company is. Queries should be specific, and they should not include
any information other than what is actually needed to answer the question.
Again, that is why it is so important to not duplicate information in your
tables. If a part has a price, then that price should be in one place only.
If there is a description of material, then that description should be in one
place only.

 

[Guest]

You have a lot of junction tables. A junction table is one where two other
tables connect to it with one-to-many relationships, with the many going into
the junction table. Some of these make sense, such as your
tbl_WorkorderParts. You can have a workorder that has many parts, and you
can have a part that goes to many workorders. Please apply that logic to all
of your junction tables, and make sure that you really need them.

Thanks for this tip and the definition of a junction table, I had
mistakenly thought they were look-up tables.
I am a little confused inasmuch as tbl_WorkrderParts makes sense to
you, and by the same logic tbl_ClaimAmounts should also meet the
requirements. Still the combination of using both in a query seems to
duplicate records. I'm sorry to keep harping on this query, but it
seems to be the indicator of my problem.

Logic: You can have a workorder that has many x's, and you can have an x
that goes to many workorders. Fill in the x with the fields from your
junction tables.

I can have a workorder that has many parts, and I can have a part that
goes to many workorders. I can have a workorder that has many claims
and I can have a claim that goes to many workorders. They seem the
same, yet used in conjunction do not produce the expected outcome.

I see a lot of duplicate fields in your junction tables,
and duplicate fields in the table they join to,

Would it make sense to create the junction tables using only the
junction table primary key, the many-to-many primary key and the
junction parent primary key? Is that what you mean?
I've been researching junction tables and it seems that the rule is;
at the most they should contain 4 fields, usually comprised of a
primary and related foreign keys. Yet I don't understand how it would
make a difference to the query results I'm getting. I will try it and
report back.

and I don't always see a logical reason for having a junction table.

Can you be more specific?
Which junction tables seem redundant to you?
Can you explain why they are redundant?

Should not have duplicate fields. Ever.

Okay, I'll hunt them down and go Cheney on 'em ;-P

Each table should have special
discrete information that fits naturally with all the other information in
the table. Really look at your tables, and decide what information should go
into those tables. You might even print out your relationship chart, and
make notes on it for what you expect to see in those tables.

This sounds like a great idea. It'll probably take me a while to do,
but I understand your intent.

Now, when you start creating queries, there should be a reason for the
query. You should not just dump all information into the query. A query
should be based on a question that you are asking. It could be as simple as
how many people own their own home versus renting. It could be a little more
complicated, such as how much is being spent on parts, depending on who the
retail company is. Queries should be specific, and they should not include
any information other than what is actually needed to answer the question.

I thought the query was specific. It is based upon the requirements
for the report, and they seem to make sense. I am trying to create a
recordset from a query to display customer workorders, install dates,
parts used, the employee who serviced the account and amounts to be
billed. A little simplified but the entire report only requires 12
fields, it involves different information from different tables, but
the records exist in the database. What I'm TRYING to do makes sense,
it's the underlying structure that's the problem. And I haven't
figured out where I've gone wrong. I must be thick as a brick.

Again, that is why it is so important to not duplicate information in your
tables. If a part has a price, then that price should be in one place only.
If there is a description of material, then that description should be in one
place only.

Understood.
I really appreciate you making my brian work. Whether or not that will
produce the desired result is another question. I'm going to keep
trying based on the information you've given me. If I continue to hit
the wall, would you consider helping me as a paid consultant?

Thanks Again,
Ross

 

[Guest]

I have a similar problem with one of my workplace databases. I inherited it
and it is a vital system, so root and branch modification has not been
possible.

As a new bod to this database lark, I struggled for solutions, though I
found one, I suspect the knowledgeable chaps on here will cringe at it's
brutality, but it worked, a quick and very dirty fix.

Queries can be made to output unique values or unique records. I used a
series of "builder" queries. Start off with one table, then make a new join
between that query and the next table and so on. It's a real dirty fix, and
you need to very closely monitor the output at each stage to make sure it is
what you need.

Alec

Hi,
I posted this to the querydesign group and was told I may have a
design problem, so I'm posting it here in hope that someone can help.
I am trying to create a recordset from a query to display customer
workorders, parts used, the employee who serviced the account and
amounts to be billed. The results are to be formatted as per customer
request and exported to an Excel spreadsheet.

Here are the tables and fields I am using to build this query:

tbl_Customers
tbl_Customers.FirstName
tbl_Customers.LastName
tbl_Customers.HomePhone

tbl_Employees
tbl_Employees.FirstName
tbl_Employees.LastName

tbl_Workorders
tbl_Workorders.WorkorderID <- primary key
tbl_Workorders.AccountNumber
tbl_Workorders.InstallDate

tbl_WO_Parts
tbl_WO_Parts.PartID
tbl_WO_Parts.SerialNumber

tbl_WO_ClaimAmt
tbl_WO_ClaimAmt.ClaimCode
tbl_WO_ClaimAmt.RequestType
tbl_WO_ClaimAmt.ClaimAmount

When I create the query using both tbl_WO_ClaimAmt and tbl_WO_Parts,
the records returned appear to have quadrupled for each customer.
Running the query with ONLY tbl_WO_Parts and the other tables(omitting
tbl_WO_Claim) returns the correct amount of records for each customer.
Running the query with ONLY tbl_WO_Claim and the other tables(omitting
tbl_WO_Parts) returns the correct amount of records for each customer.
As soon as I add both of these tables to the query is when the record
duplication occurs.

Here is the SQL, I hope it will be helpful:

SELECT DISTINCTROW tbl_WorkorderParts.PartID,
tbl_WorkorderParts.SerialNumber, tbl_Customers.[First Name],
tbl_Customers.[Last Name], tbl_Customers.[Home Phone],
tbl_MIGWorkorder.[Install Date], tbl_Employees.FirstName,
tbl_Employees.LastName, tbl_MIGWorkorder.AccountNum,
tbl_WorkOrderClaimAmt.ClaimCode, tbl_WorkOrderClaimAmt.RequestType,
tbl_WorkOrderClaimAmt.ClaimAmount
FROM (tbl_Employees INNER JOIN ((tbl_Customers INNER JOIN
tbl_MIGWorkorder ON tbl_Customers.CustomerID =
tbl_MIGWorkorder.CustomerID) LEFT JOIN tbl_WorkOrderClaimAmt ON
tbl_MIGWorkorder.WorkOrderID = tbl_WorkOrderClaimAmt.WorkOrderID) ON
tbl_Employees.EmployeeID = tbl_MIGWorkorder.EmployeerID) INNER JOIN
tbl_WorkorderParts ON tbl_MIGWorkorder.WorkOrderID =
tbl_WorkorderParts.WorkorderID;

I'm not sure if the order I've added the fields makes a difference
here, but this is how the client wants the data to appear.

Each workorder only has 1 CustomerID and 1 WorkorderID, but as you can
imagine many different parts and serial numbers and many different
claim codes and prices can be associated to 1 customer.
So we might have something like:

WorkOrderID
CustomerName
AccountNumber
PartID1
PartID2
PartID3
SerialNum1
SerialNum2
SerialNum3
ClaimCode1
ClaimCode2
ClaimCode3
ClaimAmount1
ClaimAmount2
ClaimAmount3

As noted earlier omitting either the tbl_Parts or tbl_ClaimAmt from
the query produces the correct results. But as soon as both
tables.fields are used the records quadruple. I'v also tried creating
the left joins in seperate queries and using those to generate the
records I need, this also produces the duplicate records.

Any help would be greatly appreciated.

Best Regards,
Ross

 

[Guest]

Let's simplify your database in order to illustrate what is happening with
your original query:

tbl_WorkOrder
WorkOrderID
WorkOrderNumber

tbl_WorkOrderParts
WorkOrderPartID
WorkOrderID
PartID

tbl_Parts
PartID
PartName
PartDescription

The junction table is, of course, tbl_WorkOrderParts. For each work order,
there can be many parts, and for each part, there can be many work orders.

You create a query that includes all of the fields from tbl_WorkOrderParts.
Let us say that there are three work orders, and each work order has four
parts associated with it. The results of your query will be 12 lines long,
because there will be four lines for every work order. Just looking at the
query may be confusing, because you just want to see what part is associated
with a work order, and it is apparently displaying a lot of duplicate lines.
However, most of the time you will not be using queries to look at the result
of a junction table, because of the duplication of data in some of the fields.

You need, in this case, a form and subform. The subform will contain, in
list form, your parts. All by itself, it will not make sense, because it
will just be a very long list of all of the parts used in all of the work
orders. But, it does not stand by itself. You will combine it with a form.
The form will have a field on it that shows which work order you are looking
at. There will be some way of moving from one work order to another (perhaps
a drop-down list, or just paging through). But, included on this form will
be your subform, which is a listing of the parts. As you combine the form
and subform, you will link the two together with a common field, which will
probably be the work order ID. This will cause the subform to display just
those parts that go with the current work order number that is displayed on
the main form.

It might look a little like this:

[Main Form]
Work Order Number: 103

[SubForm]
Parts:
Widget $45.43
Doohickey $13.78
Thingey $98.43
Gadget $12.12

This is the nature of a relational database. You will have long lists of
data, which use simple numbers to relate that data to other long lists. The
forms and reports work the magic of making those relationships appear
together in a way that makes sense. A query can sometimes make sense on its
own, but only if you are very careful in phrasing how that query is put
together. You might want to try just looking at very discrete information at
first, until you get the hang of it. Just look at the parts used in an
installation, without any other information.

Keep working on those tables. There needs to be a specific reason for each
table, and every field in it should, by its nature, belong to that table.
When you use a junction table, you will probably want to use a form and
subform to display the information that is associated through that junction
table. It can work the other way, too. You could have a main form that
displays the parts you use, and have a subform that lists which work orders
used that particular part.

 

[Guest]

Okay,
Not quite, but understood.
It's solved because I now realize it's impossible.

I'd like to thank mnature for all the time on this and pointing me in
the right direction. I'd also like to thank Pat Hartman for telling me
that to begin with. Wish I'd listened. Thanks a ton.

Here is the explanation that made the light go on:

"here's a challenge --

tom has 3 pets, a dog, a cat, and a bird, and tom likes 2 fruits,
apples and bananas

fred has 2 pets, a snake and a gerbil, and likes 3 fruits, apples,
peaches, and pears

pretend these come from two one-to-many relationships (people to pets,
people to fruit), and show me how you would present this data in a
row/column grid

then work your way backwards to see if you can come up with the sql to
return the results the way you've laid them out

hint: you can't ;o)"


Thanks to Rudy Limeback for that. (http://r937.com/)

So, I guess I'll have to run 2 queries and see if I can display the
results in a report, sub-report. If it works, I bet binding that to a
command button on a form is going to be fun, never mind synchronizing
the queries...

Anyway, thanks for all the help from everyone.
I'm sure I'll be back with more stupid questions.

Best Regards,
Ross