MS Access 03 - Query Expression to Add like ID Numbers

Question

So I have a query that is a Top Nth aggregate query, and I have another query built from that one that returns all the offices/locations grouped for each of the top sales. I want to make a report that counts the number of offices associated with each of these top Nth ID values that are returned in this query. I want to use a domain aggregate expression in text boxes on the report so that I do not have to spend time each month looking up those IDs to determine what needs to go into the expression.

So is there an expression I can add to the second query that will assign a number descending number to the IDs?

The first query looks like: /

ID#    ITEM             Sold  /
765    Lawnmowers        75  /
764    Weed trimmers     64  /
etc

the second query looks like: /

ID#    ITEM             Sold         Location   /
 /
765    Lawnmowers        75          New York  /
765    Lawnmowers        75          Maryland  /
765    Lawnmowers        75          Ohio     /
765    Lawnmowers        75          Virginia   /
764    Weed trimmers     64          Florida
764    Weed trimmers     64          north Carolina   /

I need: /

ID#    ITEM             Sold         Location          IDGroup#   /

765    Lawnmowers        75          New York                 1   /
765    Lawnmowers        75          Maryland                 1   /
765    Lawnmowers        75          Ohio                     1   / 
765    Lawnmowers        75          Virginia                 1   /
764    Weed trimmers     64          Florida                  2   /
764    Weed trimmers     64          north Carolina           2   /

please help. thanks! i am pulling my hair on this one!

what i am trying to do is be able to report the number of items sold (done), how many locations sold the item (need dynamically) for a given time period.

i was trying to use an DCount expression but if I use the Product ID, it does me no good because these figures change monthly (this is all based from a Top Ten query to begin with)

i know i confuse myself with this question :)

Answer 1

Hi Justin, To answer your question, yes it's possible. You can use the Switch function. Ex: Switch("LawnMower",1,"Weed trimmers",2) etc. Of course this requires you to put an entry for every unique value in the table and is pretty much a horrible id for anything but a throw-away query with a small number of groups.

A more orthodox approach would be to create a lookup table with two fields: "Item" and "Group". Make item the primary key and make "Group" a AutoNumbered field. Next you will need to create a Select Distinct query to get the unique items in the original table. Turn this query into an append query and pipe the values into your lookup table (sorting however you like). The autonum field will automatically assign sequential IDs to these records.

Now you can just take your original query and add a join to your lookup table to pull in a group id.

Addendum: If more than one item can be in a group, simply use the ITEM make the ID field a non-unique numeric long and then key the entries manually (it shouldn't take too long since it will be unique entries). Either way, with this approach you should be able to maintain your groups with relative ease going forward.