Aliases in Tables for Queries

[mxinter]

Hi guys,

Im having a little problem here. I have a database with
over 100'000 records and need your help.

If I run a query to pull total stock units for a location
known operationally as say "ABC" but for another
departement it is also known as say "FGH" and information
has been entered as such. I have at least 58 locations
with upto 4-5 different names within the 100'000 records.
What I want to do is run a query and that the result
for "ABC" gives me both the "ABC + FGH" totals. Like in
the example below when I query ABC the total of stock
units is 46
---------------------------------------------
example

ID Stock Units

ABC 25
CDF 29
FGH 21
IJK 23
LMN 23
---------------------------------------------

can I create some sort of code/condition/table so that
when I run my query I dont have to do all this manual
mapping...to allocate who's is which..

Thx for your help,

mxinter

 

[Peter Hoyle]

Yes, you could just create a location mapping table.

ID1 ID2

ABC ABC
FGH ABC
IJK IJK
LMN LMN

Add the table to the grouping query with ID1 linked to the current location
and grouping by the location given in ID2.

You could use a quick make table query by grouping by just the location
in the first place to give you all the data for ID1.

Set the primary key for the mapping table to be a composite key
including both ID1 and ID2 to make sure there are no duplications
that would upset the later count.

Cheers,
Peter