Allocating one record to two categories

[jeh]

My Tbl_Main has a Primary Key, a secondary key called Category and
several other fields including one for Cost. All entries are in one
of four categories, A, A1, A2 and B. Category A, effectively an
umbrella category, deals with situations when expenditure on A1 and A2
is lumped together in a single check. This simplifies data entry but
poses a problem for Reports. I need a Report covering each of the
categories A1, A2 and B, with category A entries being allocated at
half cost to each of A1 and A2. Splitting the cost is trivially easy,
but can one construct a query that would allocate this half to each of
A1 and A2? For audit reasons I can't disturb the original table.

Suppose the table looks like:
Cat Date Cost:
A 12 Jan 20
A1 11 Feb 12
A2 15 Feb 15
B 19 Mar 25

The report, which is grouped by category, needs to look like:
A1
12 Jan 10
11 Feb 12

A2
12 Jan 10
15 Feb 15

B
19 Mar 25

Any suggestions would be appreciated

John

 

[Allen Browne]

Are you able to add another table, teaching Access the connections between
your categories?

CatDef table fields:
CatMainID The main category
CatSubID The sub category
Multiplier Fraction to assign to each category.
Example data:
CatMainID CatSubID Multiplier
A A1 0.5
A A2 0.5

You could then create a query using this CatDef table, along with your
original. Use an outer join. Type this into a fresh column in the Field row
to define the category to show on the report:
Cat: Nz(CatDef.CatSubID, Table1.Cat)
And type this into another column in the Field row to show the amount split
between categries:
Amount: Cost * Nz(CatDef.Multiplier, 1)

Actually, JET 4 (Access 2000 and later) is likely to misunderstand that last
calculation, so you probably need:
Amount: CCur(Nz(Cost * Nz(CatDef.Multiplier, 1),0))

 

[jeh]

Thanks Allen, particularly for this time at a weekend. Your elegant
solution should work nicely.

cheers

John