duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table ... tblRoutes that include the fields id, routeno, fairdate,
booth, collcode and collcod2. I need to know if I can limit limit duplicates
on two fields. The fairdate would have 38 booths, so the 38 booths will
repeat each fairdate but not repeat for a set fairdate. For example, booth 1
would be included in fairdate 9/28/07 and 9/29/07 but should only appear once
for each date.

I'm guessing I need to do this within a query, but what is the SQL statement?

Thanks for any help.
 
Hi Margaret,
To prevent duplicate values from being entered into a combination of fields.
Open the table in design view. Click indexes on the toolbar. In the first
blank row in the Index Name column type a name for the index. In the Field
Name column, click the arrow and select the first field for the index
(fairdate). In the next row select your next field (booth). Leave the index
name blank for this row. Then, select the new index name in the upper part of
the index window and change the unique property to yes.
Regards
Proko
 
Hello Margaret.

margaret said:
I have a table ... tblRoutes that include the fields id, routeno,
fairdate, booth, collcode and collcod2. I need to know if I can
limit duplicates on two fields. The fairdate would have 38 booths,
so the 38 booths will repeat each fairdate but not repeat for a set
fairdate. For example, booth 1 would be included in fairdate 9/28/07
and 9/29/07 but should only appear once for each date.

I'm guessing I need to do this within a query, but what is the SQL
statement?
Click to expand...

In the database window, when viewing queries, if you click "New",
Access you to use the find duplicates wizard. He creates this query:

SELECT tblRoutes.fairdate, tblRoutes.booth, tblRoutes.id,
tblRoutes.routeno, tblRoutes.collcode, tblRoutes.collcod2
FROM tblRoutes WHERE (((tblRoutes.fairdate) In
(SELECT [fairdate] FROM [tblRoutes] As Tmp GROUP BY [fairdate],[booth]
HAVING Count(*)>1 And [booth] = [tblRoutes].[booth])))
ORDER BY tblRoutes.fairdate, tblRoutes.booth;
 
Back
Top