Refine Query

[George Too]

Hi All,
This might have been answer on another occasion but I can't seem to find anything that looks similar.

The following SQL

SELECT EDGW_HOLD.RunDate, CCOEORDE_RIZZO.PROJNO, CCOEORDE_RIZZO.ONUM, CCOEORDE_RIZZO.FPNUM, EDGW_HOLD.Ticket, EDGW_HOLD.Defect, EDGW_HOLD.Footage
FROM (CCOEORDE_RIZZO INNER JOIN EDGW_HOLD
ON CCOEORDE_RIZZO.PROJNO = EDGW_HOLD.Project)
INNER JOIN CCOEITEM_RIZZO ON CCOEORDE_RIZZO.FPNUM = CCOEITEM_RIZZO.INUM
GROUP BY EDGW_HOLD.RunDate, CCOEORDE_RIZZO.PROJNO, CCOEORDE_RIZZO.ONUM, CCOEORDE_RIZZO.FPNUM, EDGW_HOLD.Ticket, EDGW_HOLD.Defect, EDGW_HOLD.Footage
HAVING (((EDGW_HOLD.RunDate)>#1/1/2007#) AND ((EDGW_HOLD.Defect)="Dirty Print"));

gives me the results attached (see pic attached)

I guess the duplication is expected but how can I do the following:
A. Get number of tickets issued not counting duplicates
B. Sum footage for each ticket not counting duplicates

Any help would be appreciated.
Thanks.

 

[jdraw]

Hi All,
This might have been answer on another occasion but I can't seem to find anything that looks similar.

The following SQL

SELECT EDGW_HOLD.RunDate, CCOEORDE_RIZZO.PROJNO, CCOEORDE_RIZZO.ONUM, CCOEORDE_RIZZO.FPNUM, EDGW_HOLD.Ticket, EDGW_HOLD.Defect, EDGW_HOLD.Footage
FROM (CCOEORDE_RIZZO INNER JOIN EDGW_HOLD
ON CCOEORDE_RIZZO.PROJNO = EDGW_HOLD.Project)
INNER JOIN CCOEITEM_RIZZO ON CCOEORDE_RIZZO.FPNUM = CCOEITEM_RIZZO.INUM
GROUP BY EDGW_HOLD.RunDate, CCOEORDE_RIZZO.PROJNO, CCOEORDE_RIZZO.ONUM, CCOEORDE_RIZZO.FPNUM, EDGW_HOLD.Ticket, EDGW_HOLD.Defect, EDGW_HOLD.Footage
HAVING (((EDGW_HOLD.RunDate)>#1/1/2007#) AND ((EDGW_HOLD.Defect)="Dirty Print"));

gives me the results attached (see pic attached)

I guess the duplication is expected but how can I do the following:
A. Get number of tickets issued not counting duplicates
B. Sum footage for each ticket not counting duplicates

Any help would be appreciated.
Thanks.

How are you defining duplicate?
What constitutes a Ticket Issued?

 

[George Too]

jdraw, thanks for the reply.

Every 'projno' can have several distict 'ticket' numbers. With every 'projno' there could be several distinct 'onum'. Both 'onum' and 'ticket' are not related but can be tied up by using the 'projno'.

When I join all tables and run the query I get both 'onum', 'ticket', and 'footage' duplication, I am assuming that it is expected. I would like to get 'ticket' and 'footage' reported only once.

Hope this clears it a bit more...

 

[apr pillai]

Try the Query after adding the word DISTINCTROW immediately after the word SELECT

SELECT DISTINCTROW EDGW_HOLD.RunDate, ........

http://msaccesstips.com

 

[George Too]

Thanks aprpillai, but I have tried that already and it does not work. Any other ideas?

 

[apr pillai]

Copy the tables and Query into a database, zip it and send to my e-mail address : apr_pillai@yahoo.com, if it is not too late.

Let me have a look at it.