reduce duplicate values in query 1

[THWatson]

Using Access 2000

I am going to try to explain this in narrative form.

A query that populates a report has 3 tables, joined by an OrderNbr field. One of the tables has a Materials field. Another table has, in addition to the OrderNbr field an ID field, which means that sometimes there will be more than one row showing for the same OrderNbr, and this causes the Materials to show extra times.

The reason for this is that 3 different departments (say Dept A, Dept B and Dept C) might participate in the same OrderNbr, and each Dept would have a separate ID, but the Materials be entered only against one Dept.

This causes the values in the report to be exaggerated, and I am looking for a way to reduce the duplicated values. Actually, I believe the problem lies with the table design (which I didn't do but am trying to sort out), but am wondering if there is an answer which I have overlooked.

Thanks.

Tom

 

[PHV]

The usual ways to reduce dups is to play with DISTINCT or GROUP BY.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[THWatson]

PHV
Thanks for your reply.

I have tried both Distinct and Group By, but duplicate values still persist. By using Distinct, I can reduce to only one value in a query, but that query then has to be joined to another query in order to pull everything together, and that's when the duplicate values show up.

Tom

 

[PHV]

has to be joined to another query
Perhaps you may also play with DISTINCT or GROUP BY in a copy of this joined query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

You should post the structures of your tables and the current SQL for your queries. You probably need to adjust your join criteria or, as PHV said, use DISTINCT in a query against one of the tables first and then include that query in your join.

John

http://www.interautomateconsulting.com

 

[THWatson]

This the main SQL
SELECT SO_MAIN.SONUM, SO_MAIN.STR_NAME, Nz([STR_NUMBER],"X") AS XSTR_NUMBER, SO_PE.PE_DATE, SO_PE.PE_TYPE, SO_MAIN1.COMP_DATE, SO_MAIN.REPORTED, SO_MAIN.REP_BY, SO_MAIN.REQ_BY, SO_PE.PE_HOURS, SO_PE.ID, SO_MAIN1.JOB_CODE, JOBCODES.DESC, SO_PE.OP_NAME, IIf([SO_PE]![PE_TYPE]="P" And [SO_PE]![ID]<>"0000",[SO_PE]![PE_HOURS],0) AS EmployeeHours, IIf([SO_PE]![PE_TYPE]="E",[SO_PE]![PE_HOURS],0) AS EquipmentHours, IIf([SO_PE]![PE_TYPE]="P" And [SO_PE]![ID]="0000",[SO_PE]![PE_HOURS],0) AS InmateHours
FROM ((SO_MAIN INNER JOIN SO_MAIN1 ON SO_MAIN.SONUM = SO_MAIN1.SONUM) INNER JOIN SO_PE ON SO_MAIN1.SONUM = SO_PE.SONUM) INNER JOIN JOBCODES ON SO_MAIN1.JOB_CODE = JOBCODES.JOB_CODE
WHERE (((SO_MAIN1.COMP_DATE) Between [Forms]![Form2]![txtStartDate] And [Forms]![Form2]![txtEndDate]));

This is the SQL that pulls the materials cost, and I have set it up to use DISTINCT
SELECT DISTINCT SO_MAIN.SONUM, SO_MAIN1.COMP_DATE, INVOIC88.COST
FROM INVOIC88 INNER JOIN (SO_MAIN INNER JOIN SO_MAIN1 ON SO_MAIN.SONUM = SO_MAIN1.SONUM) ON INVOIC88.SONUM = SO_MAIN.SONUM
WHERE (((SO_MAIN1.COMP_DATE) Between [Forms]![Form2]![txtStartDate] And [Forms]![Form2]![txtEndDate]))
ORDER BY SO_MAIN.SONUM;

Because there can be more than one value in SO_PE!ID, for a given SONUM, even though an INVOIC88.COST is only entered in one of the three SO_PE!ID associated with its SONUM, when I join the second query to the first, I get multiple values.

Tom

 

[PHV]

even though an INVOIC88.COST is only entered in one of the three SO_PE!ID associated with its SONUM
Can't you add the associated SO_PE.ID in the second query and join the 2 queries on this field too ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[THWatson]

PHV
Thanks PHV. I had tried that too, but as soon as I pull SO_PE.ID into the second query I get the multiple values there.

Something's out of whack here and I haven't yet found it.

Tom

 

[JonFer]

Can you show the primary keys for the tables in the first query? If the primary key for a table is just an autonumber field, then show instead the combination of fields that should make each record unique in that table.

Following up on PHV's suggestion, what about also adding a WHERE clause of "INVOIC88.COST Is Not Null" to the second query. Then you should only get one record per SONUM from what you said - make sure this is the case before linking the two queries.

By the way, are you getting duplicate SONUMs from the first query itself or only when you join the two queries?

John

http://www.interautomateconsulting.com

 

[THWatson]

John
To answer your questions...

1. The primary keys for SO_MAIN and SO_MAIN1 are both SONUM. There isn't a primary key for either SO_PE or JOBCODES

2. Adding a WHERE clause to the second query makes no difference. But the second query works quite fine by itself. It's only when joined to the first query that duplicate values show up.

3. Yes, there are duplicate SONUMs in the first query. That's because more than one employee might have worked on the same ServiceOrder (that's what SONUM stands for).

The problem...I think...is due to the fact that the table data isn't properly normalized. As I indicated in my first post, I didn't design the process, but am trying to put some things together.

Actually, I can get results by using a subreport based on the second query. It's not quite as neat as I would like, but it is the best I have been able to accomplish.

Thanks for your interest and help.

Tom

 

[JonFer]

You should add a primary key on JOB_CODE in table JOBCODES. If that gives an error, you have duplicate JOB_CODES and hopefully you can fix that.

Since it looks like you want to show information for each employee and multiple employees are on a SONUM, you'll get repeating information for each SONUM no matter what. Use aggregate functions like Max() to avoid inflating numbers that relate to SONUM (as opposed to employees).

John

http://www.interautomateconsulting.com

 

[THWatson]

John
I added the primary key on JOB_CODE. No problems there.

Yes, I agree with what you say about getting repeating information "no matter what." That's what's happening.

Unfortunately, using Max doesn't do it. I'm still of the opinion that the problem lies in the design.

Thanks for sticking with this. For the moment, I'm going with the subreport, as this provides a summary solution, even though the Cost of Materials can't be shown in the main report with its respective SONUM there.

I think you've worked hard enough to deserve a star.

Tom

 

[JonFer]

You'll have to post the structure of SO_PE for help with the design but it's probably better to show what you want in your final output. Then I can help show how to avoid inflating numbers.

For instance, in your report do you show the contribution from each employee or just the totals for each type?

John

http://www.interautomateconsulting.com

 

[THWatson]

John
Here's an example of the way the report looks...I see that it doesn't translate fully accurately when posted, so you will have to mentally push the entries in the columns a few spaces to the right in each instance...

SONUM: 74743 Traffic Sign Construction
Location: James Street
Situation: Repair sign
Requested by: Public Works
Completion Date: 11/22/04
Job Code: 503

START DATE # EMPLOYEES HOURS EQUIPMENT HOURS MATERIALS
11/22/04 0 0.00 3 6.00 ?
11/23/04 0 0.00 3 18.00 ?
TOTALS 0 0.00 6 24.00 ?

------------------------------------------------

SONUM: 74731 Traffic Sign Construction
Location: Walker Street
Situation: Replace sign
Requested by: Police
Completion Date: 11/19/04
Job Code: 503

START DATE # EMPLOYEES HOURS EQUIPMENT HOURS MATERIALS
11/17/04 1 1.00 0 0.00 ?
11/19/04 0 0.00 4 4.00 ?
TOTALS 1 1.00 4 4.00 ?

---------------------------------------------------

It's the Materials Cost that's the problem. In the two entry examples above, say that there was a Materials cost for only one of the entries under each SONUM the Materials Cost would still show against both. And the difficulty is that the Materials Cost comes from a different table which is an invoicing table.

The structure of SO_PE is as follows...
SONUM Number
PE_DATE Date
PE_TYPE Text
ID Text
PE_HOURS Number
OP_NAME Text

Entries in the PE_TYPE field are either a "P or an "E", the P standing for Personnel (employees) and the E standing for Equipment.

Does any of this suggest a direction?
Tom

 

[JonFer]

Is the materials cost by day, too, or is it for a certain date range or what? Are you allocating it to days?

I would rollup the employee/equipment data from SO_PE by date and SONUM in a query instead of in the report. Then use this query in the report. The materials cost data should be from a query with a date value, too, so you can LEFT JOIN it with the other data on SO_NUM and date to avoid duplicating it.


John

http://www.interautomateconsulting.com

 

[THWatson]

John
The Materials Cost applies to a particular SONUM, but only once to that SONUM. So if there are several entries for that SONUM, because of different job codes, or a P or E entry, that's what causes the multiples when the join occurs.

I am going to try your suggestion in your second paragraph.

Thanks again.

Tom

 

[JonFer]

Shouldn't you show the materials cost then along with the other SONUM information and not in the daily detail? In the report, you can have a footer section which totals the hours and instead of using Sum([Materials Cost]) you should try Max([Materials Cost]). If you do this, you can keep the queries as you had them because materials cost won't be multiplied on the report.


John

http://www.interautomateconsulting.com

 

[THWatson]

John
THANK YOU!

Putting Max in the SONUM footer works. I only get one value for each place where MaterialCosts have been shown.

So far so good.

Now I have to work out how to get a Grand Total of the Materials cost at the end of the report in the report footer.

Tom

 

[THWatson]

John
Figured it out.

I can use DSum in the report footer.

Tom