Duplicate data against field data

[dlbuck]

First of all I want to thank all of those out there who post and reply, it
has been a great help. I am looking at trailers that come in our yard and
then unloaded. I am pulling the Date In data from one database and the Unload
date from another. The only common denominator is the trailer number (Equip
Id). While we often have the same trailer numbers in and out, every date is
duplicated for each field. Is there a way to allow duplicate trailer
numbers, but not duplicate dates for those trailers?
My current SQL look like this.
SELECT [72 hour qry rpt 4].[Equip Id], [72 hour qry rpt 4].[Date Unloaded],
[72 hour qry rpt 4].[Actual Date in], [72 hour qry rpt 4].[Days Diff]
FROM [72 hour qry rpt 4]
GROUP BY [72 hour qry rpt 4].[Equip Id], [72 hour qry rpt 4].[Date
Unloaded], [72 hour qry rpt 4].[Actual Date in], [72 hour qry rpt 4].[Days
Diff]
ORDER BY [72 hour qry rpt 4].[Equip Id], [72 hour qry rpt 4].[Date
Unloaded], [72 hour qry rpt 4].[Actual Date in];


Thanks again

 

[Klatuu]

Create an index that includes the trailer number and the date and set Unique
to Yes for the index.

 

[dlbuck]

I took my data and made a new table Now I am getting an error "Sytax error in
CREATE TABLE statement". The term Unique is highlighed. My SQL leave a lot
to be desired

SQL looks like this
CREATE [ UNIQUE ] INDEX Trailer Number
ON 72 hour rpt tbl (Equip Id [,Date Unloaded ,Actual Date In )

What did I do wrong?
Thanks again.

Create an index that includes the trailer number and the date and set Unique
to Yes for the index.
--
Dave Hargis, Microsoft Access MVP

First of all I want to thank all of those out there who post and reply, it
has been a great help. I am looking at trailers that come in our yard and
then unloaded. I am pulling the Date In data from one database and the Unload
date from another. The only common denominator is the trailer number (Equip
Id). While we often have the same trailer numbers in and out, every date is
duplicated for each field. Is there a way to allow duplicate trailer
numbers, but not duplicate dates for those trailers?
My current SQL look like this.
SELECT [72 hour qry rpt 4].[Equip Id], [72 hour qry rpt 4].[Date Unloaded],
[72 hour qry rpt 4].[Actual Date in], [72 hour qry rpt 4].[Days Diff]
FROM [72 hour qry rpt 4]
GROUP BY [72 hour qry rpt 4].[Equip Id], [72 hour qry rpt 4].[Date
Unloaded], [72 hour qry rpt 4].[Actual Date in], [72 hour qry rpt 4].[Days
Diff]
ORDER BY [72 hour qry rpt 4].[Equip Id], [72 hour qry rpt 4].[Date
Unloaded], [72 hour qry rpt 4].[Actual Date in];


Thanks again

 

[Klatuu]

The brackets in the example in VBA Help are only there to indicate Unique is
an optional parameter. Also, since you are using spaces in your names (bad
practice), you need to enclose the names in brackets. Just use:

CREATE UNIQUE INDEX [Trailer Number] ON [72 hour rpt tbl] ([Equip Id] ,[Date
Unloaded] ,[Actual Date In] )
--
Dave Hargis, Microsoft Access MVP

I took my data and made a new table Now I am getting an error "Sytax error in
CREATE TABLE statement". The term Unique is highlighed. My SQL leave a lot
to be desired

SQL looks like this
CREATE [ UNIQUE ] INDEX Trailer Number
ON 72 hour rpt tbl (Equip Id [,Date Unloaded ,Actual Date In )

What did I do wrong?
Thanks again.

Create an index that includes the trailer number and the date and set Unique
to Yes for the index.
--
Dave Hargis, Microsoft Access MVP

First of all I want to thank all of those out there who post and reply, it
has been a great help. I am looking at trailers that come in our yard and
then unloaded. I am pulling the Date In data from one database and the Unload
date from another. The only common denominator is the trailer number (Equip
Id). While we often have the same trailer numbers in and out, every date is
duplicated for each field. Is there a way to allow duplicate trailer
numbers, but not duplicate dates for those trailers?
My current SQL look like this.
SELECT [72 hour qry rpt 4].[Equip Id], [72 hour qry rpt 4].[Date Unloaded],
[72 hour qry rpt 4].[Actual Date in], [72 hour qry rpt 4].[Days Diff]
FROM [72 hour qry rpt 4]
GROUP BY [72 hour qry rpt 4].[Equip Id], [72 hour qry rpt 4].[Date
Unloaded], [72 hour qry rpt 4].[Actual Date in], [72 hour qry rpt 4].[Days
Diff]
ORDER BY [72 hour qry rpt 4].[Equip Id], [72 hour qry rpt 4].[Date
Unloaded], [72 hour qry rpt 4].[Actual Date in];


Thanks again