Help with calculation query

[SLW612]

Hi,
I am new to Access and could use some help with a "count-if" type query. I
have 3 tables - t_Comm, t_Ven, and t_Details.

The t_Comm table has three columns: Comm_ID (Primary key), CommName,
CommAbrev.

t_Ven has two columns: Ven_ID (primary key), VenName. These tables are used
as lists only, for lookup purposes.

t_Details has columns: D_ID (primary key), Ven (values from t_Ven), Comm
(values from t_Comm), Int_ID. Here is a brief example of the t_Details
table:

ID Ven Comm Int_ID
1 Ven_A Com_1
2 Ven_B Com_1
3 Ven_A Com_1
4 Ven_A Com_2
5 Ven_A Com_1

What I want to do is keep track of the number of times the Ven-Comm
combination appears and count up from there, keeping track of this number in
field Int_ID, and combine it with the CommAbrev. So eventually, the table
would look like this:

ID Ven Comm Int_ID
1 Ven_1 Com_A C1-1
2 Ven_2 Com_A C1-1
3 Ven_1 Com_A C1-2
4 Ven_1 Com_B C2-1
5 Ven_1 Com_A C1-3

And so on. Now, I'm thinking a query is how I would go about this but I
have no idea how to write one - I am much more versed with Excel VBA but
haven't spent much time with Access. Hopefully this makes enough sense to
work with!

Thanks in advance!

 

[Jeff Boyce]

Would this approach require you to be continuously updating that field?
Except in certain situations (and I'm not sure this is one), you don't need
to and probably shouldn't store a calculated value. Just run a query that
"counts" to get a fully-up-to-date count (see "Totals" queries in Access
HELP).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

It appears you want a ranking query based on the ID and the VEN.

SELECT A.D_ID, A.Ven, A.Comm
, 1 + Count(B.ID) As Rank
FROM T_Details as A LEFT JOIN T_Details as B
ON A.Ven = B.Ven
AND A.D_ID > B.D_ID
GROUP BY A.D_ID, A.Ven, A.Comm

Once that is working you should be able to add in the other tables to get the
abbreviations, etc.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County