Completely stumped... (kinda long) (1 Viewer)

[[nateobot]]

OK I have a big table. It has a few fields but that is not the problem. This big table links with another table on two fields (2 fields on the big table (TableA) can link with one field on TableB.

I know not smart (I did not design and am not allowed to change this fact).

So TableA can join with TableB through
1. A.CompID = B.CompID
2. A.LegacyID = B.CompID
3. A.CompID and A.LegacyID = B.CompID (yes compid and legacyid can be the same)

The problem comes in that I need to do reporting on only 4 fields. These four fields need to have distinct counts done. Thus I need a distinct count of compid & legacyID's for each month.

I have gotten around the whole messiness by creating 3 tables.

1. All CompID are equal
2. All LegacyID are equal
3. Both CompID and LegacyID are equal.


What I need is a crosstab query or something like that to compile the total counts.

Right now my crosstab query looks something like this:

ChangeMonth	Type1	Type2	Type3
200201	      126	94	 890
200202	      99	79	1092
200203	      133	91	1501
200204	      177	179	1843
200205	      177	143	2140
200206	      116	95	1078

So what I will need to do is add the Legacy Counts + the CompID counts and subtract the Counts from the table that has both fields.

I don't know if I can do this in one crosstab, export to excel and let excel do the math or what.

I hope this makes some sort of sense. If you need more info or anything let me know

Thanks

 

[The_Doc_Man]

OK, I'm not sure I can help you here, because I'm a little hazy on what you want. Is it your belief that the cross-tab would double-dip when counting some things? I.e. if an item matches CompID AND matches LegacyID, it will be counted twice?

The way I might approach this involves an intermediate pair of queries. Let's say for the sake of argument that you are counting fields such as ChangeMonth and Type. (It is what was in your crosstab...) I am assuming that these two items appear in different tables or else a JOIN wouldn't be required in the first place. So something comes from each table in the JOIN.

Query1: SELECT TableB.ChangeMonth as CM, TableA.ChgType as CT, TableB.CompID as CI from TableA, TableB where TableA.CompID = TableB.CompID ;

Query2: SELECT TableB.ChangeMonth as CM, TableA.ChgType as CT, TableB.CompID as CI from TableA, TableB where (TableA.LegacyID = TableB.CompID) AND (TableA.LegacyID <> TableA.CompID) ;

UQuery: SELECT CM, CT, CI from Query1
Union
SELECT CM, CT, CI from Query2 ;

Because of the extra AND clause in Query2, that query won't capture cases where both fields match. So there will be no double dipping. You could theoretically build Query1 and Query2 from the standard query Design grid. The UNION query, however, MUST be designed from Query SQL view. Once you have UQuery, you can cross tabulate on that query as a recordsource. I would suggest that you use the crosstab wizard just to keep you from getting confused.

If I left out fields that you need to cross tabulate, add them to Query1, Query2, and UQuery. UNION queries are a bit touchy, so make sure that you apply the same alias names (the AS xx clause) for both individual queries, then make sure you select only the alias names for the UQuery.

By the way, I didn't use Type as a field name because that happens to match an Access reserved word. I called it ChgType just to assure it had a unique name that didn't match a keyword.