Query producing duplicate records

[Guest]

I have a table invtax that holds four records for each transaction number.
Each record lists the tax on a transaction for N (national), S (State), C
(County), I (local district). I have carefully examined this table to verify
there are no duplicate records.

I need to query this table to combine certain data into one record per
transaction number. Here is my query:

SELECT invtax.trans_id, invtax.order, Sum(invtax.taxrate) AS SumOftaxrate,
Max(IIf([taxtype]="S",[staxcode],"")) AS Scode,
Sum(IIf([taxtype]="S",[tax],0)) AS Stax,
Max(IIf([taxtype]="C",[ctaxcode],"")) AS Ccode,
Sum(IIf([taxtype]="C",[tax],0)) AS Ctax,
Max(IIf([taxtype]="I",[itaxcode],"")) AS Icode,
Sum(IIf([taxtype]="I",[tax],0)) AS Itax,
Max(IIf([taxtype]="N",[ntaxcode],"")) AS Ncode,
Sum(IIf([taxtype]="N",[tax],0)) AS Ntax, Max(invtax.taxable) AS Taxable,
Max(invtax.exempt) AS Exempt
FROM invtax
WHERE (((invtax.trans_id)>168000))
GROUP BY invtax.trans_id, invtax.order
ORDER BY invtax.trans_id, invtax.order;

The problem is that each time I run the query, I get a different number of
records and different records are duplicated with zero or null fields. In
5500 transactions I will get between 5500 and 5510 records.

Does anyone have an idea why I am getting these results?

 

[[MVP] S.Clark]

I have the strange feeling that your data is Over-Normalized, which almost
never happens, but at least there is a normalization problem overall.

Anytime that you have to use IIF(), there's something fundamentally wrong.

Maybe list your table structures, and that will shed light on the entire
situation.

 

[Guest]

This is a linked FoxPro table which I cannot change. And yes, I think it is
over-normalized also but it is what I have to work with.

invtax table is as follows:

field type memo
Key trans_id number four records per transaction
order number same in all four records
inpart text same in all four records
active text
Key taxtype Text One record for 'N', one for 'S', one for 'C',
and one for 'I'
ntaxcode Text
ntaxclass Text
staxcode Text 2-character state code shows only on 'S'
staxclass Text
ctaxcode Text
ctaxclass Text
itaxcode Text
itaxclass Text
taxrate Number
taxable Number
exempt Number
tax Number

This is the only table I am working with in this query. The two fields
marked 'Key' are the compound key I set up for the link.

Appreciate any help you can give me.

I have the strange feeling that your data is Over-Normalized, which almost
never happens, but at least there is a normalization problem overall.

Anytime that you have to use IIF(), there's something fundamentally wrong.

Maybe list your table structures, and that will shed light on the entire
situation.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a table invtax that holds four records for each transaction number.
Each record lists the tax on a transaction for N (national), S (State), C
(County), I (local district). I have carefully examined this table to
verify
there are no duplicate records.

I need to query this table to combine certain data into one record per
transaction number. Here is my query:

SELECT invtax.trans_id, invtax.order, Sum(invtax.taxrate) AS SumOftaxrate,
Max(IIf([taxtype]="S",[staxcode],"")) AS Scode,
Sum(IIf([taxtype]="S",[tax],0)) AS Stax,
Max(IIf([taxtype]="C",[ctaxcode],"")) AS Ccode,
Sum(IIf([taxtype]="C",[tax],0)) AS Ctax,
Max(IIf([taxtype]="I",[itaxcode],"")) AS Icode,
Sum(IIf([taxtype]="I",[tax],0)) AS Itax,
Max(IIf([taxtype]="N",[ntaxcode],"")) AS Ncode,
Sum(IIf([taxtype]="N",[tax],0)) AS Ntax, Max(invtax.taxable) AS Taxable,
Max(invtax.exempt) AS Exempt
FROM invtax
WHERE (((invtax.trans_id)>168000))
GROUP BY invtax.trans_id, invtax.order
ORDER BY invtax.trans_id, invtax.order;

The problem is that each time I run the query, I get a different number of
records and different records are duplicated with zero or null fields. In
5500 transactions I will get between 5500 and 5510 records.

Does anyone have an idea why I am getting these results?

 

[[MVP] S.Clark]

Ok, then, just because the data has a cruddy structure, it isn't the end of
the world. Perhaps prior to doing anything, you can massage the data into a
more usable format. Create a semi-permanant table, and use action queries
to populate it. Then, do the queries to populate the report the proper way.
Make a pseudo-datawarehouse if you must, and populate that on a regular
basis.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

This is a linked FoxPro table which I cannot change. And yes, I think it
is
over-normalized also but it is what I have to work with.

invtax table is as follows:

field type memo
Key trans_id number four records per transaction
order number same in all four records
inpart text same in all four records
active text
Key taxtype Text One record for 'N', one for 'S', one for
'C',
and one for 'I'
ntaxcode Text
ntaxclass Text
staxcode Text 2-character state code shows only on 'S'
staxclass Text
ctaxcode Text
ctaxclass Text
itaxcode Text
itaxclass Text
taxrate Number
taxable Number
exempt Number
tax Number

This is the only table I am working with in this query. The two fields
marked 'Key' are the compound key I set up for the link.

Appreciate any help you can give me.

I have the strange feeling that your data is Over-Normalized, which
almost
never happens, but at least there is a normalization problem overall.

Anytime that you have to use IIF(), there's something fundamentally
wrong.

Maybe list your table structures, and that will shed light on the entire
situation.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a table invtax that holds four records for each transaction
number.
Each record lists the tax on a transaction for N (national), S (State),
C
(County), I (local district). I have carefully examined this table to
verify
there are no duplicate records.

I need to query this table to combine certain data into one record per
transaction number. Here is my query:

SELECT invtax.trans_id, invtax.order, Sum(invtax.taxrate) AS
SumOftaxrate,
Max(IIf([taxtype]="S",[staxcode],"")) AS Scode,
Sum(IIf([taxtype]="S",[tax],0)) AS Stax,
Max(IIf([taxtype]="C",[ctaxcode],"")) AS Ccode,
Sum(IIf([taxtype]="C",[tax],0)) AS Ctax,
Max(IIf([taxtype]="I",[itaxcode],"")) AS Icode,
Sum(IIf([taxtype]="I",[tax],0)) AS Itax,
Max(IIf([taxtype]="N",[ntaxcode],"")) AS Ncode,
Sum(IIf([taxtype]="N",[tax],0)) AS Ntax, Max(invtax.taxable) AS
Taxable,
Max(invtax.exempt) AS Exempt
FROM invtax
WHERE (((invtax.trans_id)>168000))
GROUP BY invtax.trans_id, invtax.order
ORDER BY invtax.trans_id, invtax.order;

The problem is that each time I run the query, I get a different number
of
records and different records are duplicated with zero or null fields.
In
5500 transactions I will get between 5500 and 5510 records.

Does anyone have an idea why I am getting these results?

 

[Guest]

I set up a table, ran four separate queries to populate the table, and the
only errors found appear to be true "problem" transactions.

Thanks so much for your help!