Restrict Duplicates In A Query

P

perplexed

I have a query that combines two table. The Schedule Table and the Daily
Journal Table. The relationship join is set to "Only Include Rows where the
join fields from both tables are equal. " Yet I am getting a duplicate record
of each (i.e. I have two records with mirror data of ID 2262; I assume one
for the schedule table and one for the daily journal table). I have my
properties set for Unique Records: YES. My SQL Code looks like this: SELECT
DISTINCTROW [Table: Schedule].ID, [Table: Schedule].[Appt Date], [Table:
Schedule].[Appt Time], [Table: Schedule].Customer, [Table: Schedule].[PO #],
DAILY_JOURNAL.[PO #], [Table: Schedule].[Trucking Co], DAILY_JOURNAL.[Actual
Ship Date], DAILY_JOURNAL.[Appt Time], DAILY_JOURNAL.Customer,
DAILY_JOURNAL.[LOL Order Number], DAILY_JOURNAL.[Trucking Co],
DAILY_JOURNAL.[Orig Ship Date], DAILY_JOURNAL.[Checked - IN],
DAILY_JOURNAL.[Checked Out], DAILY_JOURNAL.Comments, DAILY_JOURNAL.[Order
Shipped On Time], DAILY_JOURNAL.[NFDM Incoming], DAILY_JOURNAL.[Freight
Incoming], DAILY_JOURNAL.[Pallets Incoming], DAILY_JOURNAL.[Pkg Incoming],
DAILY_JOURNAL.[Salt Incoming], DAILY_JOURNAL.[Trays Incoming],
DAILY_JOURNAL.[Rescheduled by Denmark], DAILY_JOURNAL.[Rescheduled by
Customer], DAILY_JOURNAL.[No Show], DAILY_JOURNAL.[Show/No Appt],
DAILY_JOURNAL.[Carrier Issue], DAILY_JOURNAL.[DELETE RECORD & APPT]
FROM [Table: Schedule] INNER JOIN DAILY_JOURNAL ON ([Table: Schedule].[PO #]
= DAILY_JOURNAL.[PO #]) AND ([Table: Schedule].[Appt Time] =
DAILY_JOURNAL.[Appt Time]) AND ([Table: Schedule].Customer =
DAILY_JOURNAL.Customer) AND ([Table: Schedule].[Appt Date] =
DAILY_JOURNAL.[Actual Ship Date])
GROUP BY [Table: Schedule].ID, [Table: Schedule].[Appt Date], [Table:
Schedule].[Appt Time], [Table: Schedule].Customer, [Table: Schedule].[PO #],
DAILY_JOURNAL.[PO #], [Table: Schedule].[Trucking Co], DAILY_JOURNAL.[Actual
Ship Date], DAILY_JOURNAL.[Appt Time], DAILY_JOURNAL.Customer,
DAILY_JOURNAL.[LOL Order Number], DAILY_JOURNAL.[Trucking Co],
DAILY_JOURNAL.[Orig Ship Date], DAILY_JOURNAL.[Checked - IN],
DAILY_JOURNAL.[Checked Out], DAILY_JOURNAL.Comments, DAILY_JOURNAL.[Order
Shipped On Time], DAILY_JOURNAL.[NFDM Incoming], DAILY_JOURNAL.[Freight
Incoming], DAILY_JOURNAL.[Pallets Incoming], DAILY_JOURNAL.[Pkg Incoming],
DAILY_JOURNAL.[Salt Incoming], DAILY_JOURNAL.[Trays Incoming],
DAILY_JOURNAL.[Rescheduled by Denmark], DAILY_JOURNAL.[Rescheduled by
Customer], DAILY_JOURNAL.[No Show], DAILY_JOURNAL.[Show/No Appt],
DAILY_JOURNAL.[Carrier Issue], DAILY_JOURNAL.[DELETE RECORD & APPT]
HAVING ((([Table: Schedule].Customer) Is Not Null))
ORDER BY [Table: Schedule].[Appt Date], [Table: Schedule].[Appt Time];

Any clues how I can fix this?
 
S

strive4peace

instead of DISTINCTROW, try DISTINCT

you would not want distinct records because you most likly have a
primary key on each row, so this will do nothing to limit the records.
Instead of Unique Records, you probably want Unique Values

I formatted the SQL to be easier to read:

SELECT DISTINCTROW [Table: Schedule].ID
, [Table: Schedule].[Appt Date]
, [Table: Schedule].[Appt Time]
, [Table: Schedule].Customer
, [Table: Schedule].[PO #]
, DAILY_JOURNAL.[PO #]
, [Table: Schedule].[Trucking Co]
, DAILY_JOURNAL.[Actual Ship Date]
, DAILY_JOURNAL.[Appt Time]
, DAILY_JOURNAL.Customer
, DAILY_JOURNAL.[LOL Order Number]
, DAILY_JOURNAL.[Trucking Co]
, DAILY_JOURNAL.[Orig Ship Date]
, DAILY_JOURNAL.[Checked - IN]
, DAILY_JOURNAL.[Checked Out]
, DAILY_JOURNAL.Comments
, DAILY_JOURNAL.[Order Shipped ON Time]
, DAILY_JOURNAL.[NFDM Incoming]
, DAILY_JOURNAL.[Freight Incoming]
, DAILY_JOURNAL.[Pallets Incoming]
, DAILY_JOURNAL.[Pkg Incoming]
, DAILY_JOURNAL.[Salt Incoming]
, DAILY_JOURNAL.[Trays Incoming]
, DAILY_JOURNAL.[Rescheduled by Denmark]
, DAILY_JOURNAL.[Rescheduled by Customer]
, DAILY_JOURNAL.[No Show]
, DAILY_JOURNAL.[Show/No Appt]
, DAILY_JOURNAL.[Carrier Issue]
, DAILY_JOURNAL.[DELETE RECORD & APPT]
FROM [Table: Schedule]
INNER JOIN DAILY_JOURNAL
ON ([Table: Schedule].[PO #] = DAILY_JOURNAL.[PO #])
AND ([Table: Schedule].[Appt Time] = DAILY_JOURNAL.[Appt Time])
AND ([Table: Schedule].Customer = DAILY_JOURNAL.Customer)
AND ([Table: Schedule].[Appt Date] = DAILY_JOURNAL.[Actual Ship Date])
GROUP BY [Table: Schedule].ID
, [Table: Schedule].[Appt Date]
, [Table: Schedule].[Appt Time]
, [Table: Schedule].Customer
, [Table: Schedule].[PO #]
, DAILY_JOURNAL.[PO #]
, [Table: Schedule].[Trucking Co]
, DAILY_JOURNAL.[Actual Ship Date]
, DAILY_JOURNAL.[Appt Time]
, DAILY_JOURNAL.Customer
, DAILY_JOURNAL.[LOL Order Number]
, DAILY_JOURNAL.[Trucking Co]
, DAILY_JOURNAL.[Orig Ship Date]
, DAILY_JOURNAL.[Checked - IN]
, DAILY_JOURNAL.[Checked Out]
, DAILY_JOURNAL.Comments
, DAILY_JOURNAL.[Order Shipped ON Time]
, DAILY_JOURNAL.[NFDM Incoming]
, DAILY_JOURNAL.[Freight Incoming]
, DAILY_JOURNAL.[Pallets Incoming]
, DAILY_JOURNAL.[Pkg Incoming]
, DAILY_JOURNAL.[Salt Incoming]
, DAILY_JOURNAL.[Trays Incoming]
, DAILY_JOURNAL.[Rescheduled by Denmark]
, DAILY_JOURNAL.[Rescheduled by Customer]
, DAILY_JOURNAL.[No Show]
, DAILY_JOURNAL.[Show/No Appt]
, DAILY_JOURNAL.[Carrier Issue]
, DAILY_JOURNAL.[DELETE RECORD & APPT]
HAVING ((([Table: Schedule].Customer) Is Not Null))
ORDER BY [Table: Schedule].[Appt Date]
, [Table: Schedule].[Appt Time];

Woah! This is a lot of information. I see you have a key field in
here: [Table: Schedule].ID -- this means you will automatically get all
records from [Table: Schedule] since you are including its key field.

You really should consider renaming your fields and tables to not
include special characters (like #, &, :, /, etc). Personally I do not
even use spaces. Underscore _ is okay. Names should always start with
a letter

For more information, download and read this:

Access Basics
http://allenbrowne.com/tips.html
Tips for Casual Users
Access Basics: free tutorial - Word document by Crystal (Access MVP)

30-page Word document on Access Basics -- it doesn't cover VBA, but
prepares you for it because it covers essentials in Access. Pay close
attention to the section on Normalization.


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I have a query that combines two table. The Schedule Table and the Daily
Journal Table. The relationship join is set to "Only Include Rows where the
join fields from both tables are equal. " Yet I am getting a duplicate record
of each (i.e. I have two records with mirror data of ID 2262; I assume one
for the schedule table and one for the daily journal table). I have my
properties set for Unique Records: YES. My SQL Code looks like this: SELECT
DISTINCTROW [Table: Schedule].ID, [Table: Schedule].[Appt Date], [Table:
Schedule].[Appt Time], [Table: Schedule].Customer, [Table: Schedule].[PO #],
DAILY_JOURNAL.[PO #], [Table: Schedule].[Trucking Co], DAILY_JOURNAL.[Actual
Ship Date], DAILY_JOURNAL.[Appt Time], DAILY_JOURNAL.Customer,
DAILY_JOURNAL.[LOL Order Number], DAILY_JOURNAL.[Trucking Co],
DAILY_JOURNAL.[Orig Ship Date], DAILY_JOURNAL.[Checked - IN],
DAILY_JOURNAL.[Checked Out], DAILY_JOURNAL.Comments, DAILY_JOURNAL.[Order
Shipped On Time], DAILY_JOURNAL.[NFDM Incoming], DAILY_JOURNAL.[Freight
Incoming], DAILY_JOURNAL.[Pallets Incoming], DAILY_JOURNAL.[Pkg Incoming],
DAILY_JOURNAL.[Salt Incoming], DAILY_JOURNAL.[Trays Incoming],
DAILY_JOURNAL.[Rescheduled by Denmark], DAILY_JOURNAL.[Rescheduled by
Customer], DAILY_JOURNAL.[No Show], DAILY_JOURNAL.[Show/No Appt],
DAILY_JOURNAL.[Carrier Issue], DAILY_JOURNAL.[DELETE RECORD & APPT]
FROM [Table: Schedule] INNER JOIN DAILY_JOURNAL ON ([Table: Schedule].[PO #]
= DAILY_JOURNAL.[PO #]) AND ([Table: Schedule].[Appt Time] =
DAILY_JOURNAL.[Appt Time]) AND ([Table: Schedule].Customer =
DAILY_JOURNAL.Customer) AND ([Table: Schedule].[Appt Date] =
DAILY_JOURNAL.[Actual Ship Date])
GROUP BY [Table: Schedule].ID, [Table: Schedule].[Appt Date], [Table:
Schedule].[Appt Time], [Table: Schedule].Customer, [Table: Schedule].[PO #],
DAILY_JOURNAL.[PO #], [Table: Schedule].[Trucking Co], DAILY_JOURNAL.[Actual
Ship Date], DAILY_JOURNAL.[Appt Time], DAILY_JOURNAL.Customer,
DAILY_JOURNAL.[LOL Order Number], DAILY_JOURNAL.[Trucking Co],
DAILY_JOURNAL.[Orig Ship Date], DAILY_JOURNAL.[Checked - IN],
DAILY_JOURNAL.[Checked Out], DAILY_JOURNAL.Comments, DAILY_JOURNAL.[Order
Shipped On Time], DAILY_JOURNAL.[NFDM Incoming], DAILY_JOURNAL.[Freight
Incoming], DAILY_JOURNAL.[Pallets Incoming], DAILY_JOURNAL.[Pkg Incoming],
DAILY_JOURNAL.[Salt Incoming], DAILY_JOURNAL.[Trays Incoming],
DAILY_JOURNAL.[Rescheduled by Denmark], DAILY_JOURNAL.[Rescheduled by
Customer], DAILY_JOURNAL.[No Show], DAILY_JOURNAL.[Show/No Appt],
DAILY_JOURNAL.[Carrier Issue], DAILY_JOURNAL.[DELETE RECORD & APPT]
HAVING ((([Table: Schedule].Customer) Is Not Null))
ORDER BY [Table: Schedule].[Appt Date], [Table: Schedule].[Appt Time];

Any clues how I can fix this?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is Cross Tab Query The Right Choice 1
Need to Make Query Run Faster - SLQ? 3
How do I calculate totals in a query 2
How to make a module out of a query 4
Need Help With Design of a Query (for Report) 0
Find duplicates 3
What the Hell is going on here? 3
Slow VBA-Generated SQL Involving Totals Query 9

Top