Help with Query

  • Thread starter Thread starter mxinter
  • Start date Start date
M

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
 
mxinter

I would suggest using unions to help you out

--------------------------------------------
exampl

ID Stock Unit

ABC 2
CDF 2
FGH 2
IJK 2
LMN 2
--------------------------------------------

SELECT "ABC" As ID,SUM([Stock Units]) As StockUnit
FROM [table
WHERE ID IN ("ABC","FGH"
UNIO
SELECT "CDF" As ID,SUM([Stock Units]) As StockUnit
FROM [table
WHERE ID IN ("CDF","..."

etc

Be careful about not having too many union queries. According to John Viescas

In early versions of JET, there was a limit of 16 tables or queries in
query. The limiting factor is usually the size of the query compil
buffer - which I believe is still 64K. One trick when you get "query to
complex" is to assign short aliases to long table and field names - but yo
can get "too complex" for other obscure reasons

If you only have 5 or 6 base categories, this should work okay

Hope this helps

Ji
 
I would make a table of equivalents and then use that in the query.

tblAliases
ID
Alias

ID Alias
ABC ABC
FGH ABC
....

Then in the query

SELECT tblAliases.Alias, Sum(YourTable.StockUnits) as TotalUnits
FROM YOURTable INNER JOIN tblAliases
 
Back
Top