Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding rows on a change (Access 2000)

    Need Help!

    I have a Payroll Table that is 20000 rows. I need to find the rows that are NOT duplicates. However, the tricky part is that I have the same payroll code for multiple rows but different Payroll numbers, which is okay, but the rest of the information may be different. Here is an example

    Payroll Code-------NO------entity-------org-------date-----
    1234-----------------234-----056---------6---------01012003
    1234-----------------456-----056---------6---------01012003
    1234-----------------896-----056---------7---------01012003
    Notice that they have the same payroll code but different NO's but I only need to know which one's have different entity or org or date. Therefore, my final result should be 1234-----------------896-----056---------7---------01012003

    Hope you can help! I know it's a bit confusing.

    Thanks in advance.
    <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding rows on a change (Access 2000)

    You can do this in two steps:

    1. Create a query that selects the duplicate records from the Payroll table (on all fields except NO)
    2. Create a query that selects all records from the Payroll table that do not occur in the first query.

    The SQL for the first query is

    SELECT [Payroll Table].[Payroll Code], [Payroll Table].[NO], [Payroll Table].[entity], [Payroll Table].[org], [Payroll Table].[date]
    FROM [Payroll Table]
    WHERE ((([Payroll Table].[Payroll Code]) In (SELECT [Payroll Code] FROM [Payroll Table] As Tmp GROUP BY [Payroll Code],[entity],[org],[date] HAVING Count(*)>1 And [entity] = [Payroll Table].[entity] And [org] = [Payroll Table].[org] And [date] = [Payroll Table].[date])));

    Let's say that you save this query as qryDuplicates. The SQL for the second query is

    SELECT [Payroll Table].[Payroll Code], [Payroll Table].[NO], [Payroll Table].[entity] [Payroll Table].[org], [Payroll Table].[date]
    FROM [Payroll Table] LEFT JOIN [qryDuplicates] ON ([Payroll Table].[date] = [qryDuplicates].[date]) AND ([Payroll Table].[org] = [qryDuplicates].[org]) AND ([Payroll Table].[entity] = [qryDuplicates].[entity]) AND ([Payroll Table].[Payroll Code] = [qryDuplicates].[Payroll Code])
    WHERE ((([qryDuplicates].[Payroll Code]) Is Null));

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Finding rows on a change (Access 2000)

    I usually do same thing when need to identify dupes or non-dupes: create 1st query to identify dupes, then 2nd query based on first. For those who insist on doing this type of thing in one query instead of two, you can use a subquery as shown in this example (list the non-dupes):

    SELECT Table1.PayrollCode, Table1.[No], Table1.Entity, Table1.Org, Table1.Date
    FROM Table1
    WHERE ((([PayrollCode] & [Entity] & [Org] & [Date]) Not In (SELECT [PayrollCode] & [Entity] & [Org] & [Date] AS Field1 FROM Table1 GROUP BY [PayrollCode] & [Entity] & [Org] & [Date] HAVING (((Count([PayrollCode] & [Entity] & [Org] & [Date]))>1))[img]/forums/images/smilies/wink.gif[/img]))
    ORDER BY Table1.PayrollCode, Table1.[No], Table1.Entity, Table1.Org, Table1.Date;

    This basically takes the SQL for 1st query that identifies the dupes & places it in the subquery used for WHERE criteria. If you want to do the opposite (list duplicate records) modify SQL slightly:

    SELECT Table1.PayrollCode, Table1.[No], Table1.Entity, Table1.Org, Table1.Date
    FROM Table1
    WHERE ((([PayrollCode] & [Entity] & [Org] & [Date]) In (SELECT [PayrollCode] & [Entity] & [Org] & [Date] AS Field1 FROM Table1 GROUP BY [PayrollCode] & [Entity] & [Org] & [Date] HAVING (((Count([PayrollCode] & [Entity] & [Org] & [Date]))>1))[img]/forums/images/smilies/wink.gif[/img]))
    ORDER BY Table1.PayrollCode, Table1.[No], Table1.Entity, Table1.Org, Table1.Date;

    Note that Access added brackets around the field "NO" (recommend do same for field named "DATE"). Also note, I don't know what type of fields these are, they appear to be text, so the SQL simply concatenates the four fields in question to identify dupes. If using any non-text fields, recommend use Format function to format numerical or date values consistently if concatenating in a string. Note that if you have 20,000 records, using nestled queries may be quicker than using subqueries.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •