REQ HELP: Problem eliminating duplicates

**Don Leverton** · Nov 13 '05, 02:15 AM

Re: REQ HELP: Problem eliminating duplicates

Hi

I have copied and converted your data, and am re-posting it for you as CSV
text in case anyone else wants to attempt to help you, too:

"ID","Period"," Report","AgentI D","Date","Invo ice","JobID","C ountry","Gross" ,
"Comm"
1913.00,403.00, "S","DA",3/30/04 0:00:00,"I00190 13","LEP HFL DA
21","England",$ 2775.00,$555.00
1914.00,403.00, "S","HFL",3/30/04 0:00:00,"I00190 13","LEP HFL DA
21","England",$ 2775.00,$555.00
1915.00,403.00, "S","LEP",3/30/04 0:00:00,"I00190 13","LEP HFL DA
21","England",$ 2775.00,$555.00
1916.00,403.00, "S","DA",3/30/04 0:00:00,"I00190 14","LEP HFL DA
21","USA",$3000 .00,$600.00
1917.00,403.00, "S","HFL",3/30/04 0:00:00,"I00190 14","LEP HFL DA
21","USA",$3000 .00,$600.00
1918.00,403.00, "S","LEP",3/30/04 0:00:00,"I00190 14","LEP HFL DA
21","USA",$3000 .00,$600.00
1919.00,403.00, "S","HFL",3/30/04 0:00:00,"I00190 16","LEP HFL
200304","USA",$ 1000.00,$300.00
1920.00,403.00, "S","LEP",3/30/04 0:00:00,"I00190 16","LEP HFL
200304","USA",$ 1000.00,$300.00
1922.00,403.00, "S","LEP",3/30/04 0:00:00,"I00190 34","LEP HFL
200304","USA",$ 500.00,$150.00
1921.00,403.00, "S","HFL",3/30/04 0:00:00,"I00190 34","LEP HFL
200304","USA",$ 500.00,$150.00
1923.00,403.00, "S","LEP",3/31/04 0:00:00,"I00190 50","LEP 090104
F","England",$3 70.00,$111.00
1924.00,403.00, "S","JFO",3/31/04 0:00:00,"I00190 50","LEP JFO
090104","Englan d",$370.00,$111 .00

Observations:
--------------

1.) All of the data appears to be a classic case of "flat-file" database
thinking, where you seem to think that all data needs to be in a single
table. Access is a relational database, where different elements of data are
stored in different, but related tables. This methodology allows you to
enter items ONCE, and then link them to related items in another table in
order to re-combine the elements and *display* them as though they are all
in one record.

I'd "study up" a bit on relational table design, then attempt to seperate
this data into the following tables if I were you:

tblJobs
tblInvoices
tblCountries
tblAgents
tblCommissions (if each Agent gets paid a seperate commission)

2.) A lot of the data appears to be just cocatenated or formatted data from
other fields.
i.e (from the first record) JobID = LEP HFL DA 21
I'm assuming that to mean that 3 "AgentID's (LEP, HFL, and DA) all
contributed to the completion of some task or sale... What does "21"
represent?
Period = 403... does that represent the 3rd month of 2004?
Gross = The total profit on the job?
Comm = Commision paid. Is that amount paid to each Agent, or is split evenly
amongst them? Divided up in diferently-sized portions?

3.) These appear to be the relationships, although I am not sure where some
fields (i.e. Period, Report) "fit" because the data is identical in all
records:
1 Job ==> Many Invoices
1 Job ==> Many Countries
1 Invoice ==> Many Agents
1 Invoice ==> 1 Gross, 1 Comm? (This implies that all agents share
equally?)

Oh waitaminute ...
1 Invoice ==> Many Jobs also? (Inv# I0019050 -- JobID's LEP 090104 F,
and LEP JFO 090104)
If so, then we're talking about a "Many-to-Many" relationship which
requires a join table.
1 Job ==> Many - [tblJoinInvoiceJ obs] - Many <== 1 Invoice

So, there... that should get you started :-)

P.S.
I'd also like to advise you to not use *Reserved Words* like "Date" for your
field names... it *will* come back to haunt you eventually.

"MHenry" <MHenry@NoSpam. net> wrote in message
news:22irf0hl3o lb4lfaqbjamki3b gj4i8bho8@4ax.c om...[color=blue]
> Hi,
>
> I have a table with duplicate records.
> Some of the duplicates need to be eliminated from the table and some
> need not.
> A duplicate record does not need to be eliminated if the one record
> appears multiple times on one employee's report.
> However, if the same record(s) appear on any other employee's report,
> it means they collaborated on the item, and the item from the second
> employee's report should be eliminated from the table.
>
> Each record includes the following fields:
>
> ID
> Period
> Report
> AgentID
> Date
> Invoice
> JobID
> Description
> Country
> Gross
> Comm
>
> A duplicate record is identified if the following fields are NOT the
> same:
>
> ID
> AgentID
>
> the following fields are identical:
>
> Date
> Invoice
> JobID
> Description
> Country
> Gross
>
> I have created an append query to update a copy of the original table
> (structure only) with the fields that must be identical designated as
> primary key fields.
>
> Unfortunately, when this table is updated with the append query, some
> duplicates are not eliminated (seems this happens when more than two
> employees are involved so there are three or more duplicates of a
> record), and (I think) some records that should not be eliminated
> (suplicates from same employee) are eliminated.
>
> Will someone please help me figure this out?
>
> Thanks,
> MHenry
>
> Some sample data (Description column omitted)
>
> ID Period Report AgentID Date Invoice JobID
> Country Gross Comm
> 1913 403 S DA 3/30/04 I0019013 LEP HFL DA 21
> . England $2,775.00 $555.00
> 1914 403 S HFL 3/30/04 I0019013 LEP HFL DA 21
> . England $2,775.00 $555.00
> 1915 403 S LEP 3/30/04 I0019013 LEP HFL DA 21
> ... England $2,775.00 $555.00
> 1916 403 S DA 3/30/04 I0019014 LEP HFL DA 21
> . USA $3,000.00 $600.00
> 1917 403 S HFL 3/30/04 I0019014 LEP HFL DA 21
> . USA $3,000.00 $600.00
> 1918 403 S LEP 3/30/04 I0019014 LEP HFL DA 21
> ... USA $3,000.00 $600.00
> 1919 403 S HFL 3/30/04 I0019016 LEP HFL 200304
> . USA $1,000.00 $300.00
> 1920 403 S LEP 3/30/04 I0019016 LEP HFL 200304
> . USA $1,000.00 $300.00
> 1922 403 S LEP 3/30/04 I0019034 LEP HFL 200304
> . USA $500.00 $150.00
> 1921 403 S HFL 3/30/04 I0019034 LEP HFL 200304
> . USA $500.00 $150.00
> 1923 403 S LEP 3/31/04 I0019050 LEP 090104 F
> England $370.00 $111.00
> 1924 403 S JFO 3/31/04 I0019050 LEP JFO 090104
> England $370.00 $111.00
>
>[/color]

REQ HELP: Problem eliminating duplicates

REQ HELP: Problem eliminating duplicates

Comment