cMinor cMinor - 10 days ago 5
SQL Question

Inner join with index query MS Access

I have two tables

Customers

CustomerID name x2 x3
--------------------------------
14tr Joe att2 att3
11ty Jack att2 att3


Deposit

Depositid CustomerID quantity date att3 att4
--------------------------------------------------
13 14tr 100 12-12-12 5 6
14 14tr 200 12-13-12 8 1
15 14tr 140 12-16-12 9 6
16 11ty 10 12-19-12 1 6
17 11ty 20 12-19-12 1 1
18 11ty 114 12-21-12 1 6


I want the following result:

Result

No name quantity date att3 att4 x2 x3
-----------------------------------------------------------------------
1 Joe 100 12-12-12 5 6 att2 att3
1 Joe 200 12-13-12 8 1 att2 att3
1 Joe 140 12-16-12 9 6 att2 att3
2 Jack 10 12-19-12 1 6 att2 att3
2 Jack 20 12-19-12 1 1 att2 att3
2 Jack 114 12-21-12 1 6 att2 att3


To do so I am doing

SELECT
b.name,
a.quantity,
a.date,
a.att3,
a.att4,
b.x2,
b.x3
FROM
Deposit a INNER JOIN Customer b
ON a.CustomerID = b.CustomerID;


How can I get the No. column counter for each different customer like in the example?

Is there a better way to display the inner join between these two tables?

Answer

You can :

SELECT DCount("*","Customer","CustomerID <='" & b.CustomerID & "'") AS Ct, 
b.name, 
a.quantity, 
a.Date, 
a.att3, 
a.att4, 
b.x2, 
b.x3
FROM Deposit AS a 
INNER JOIN Customer AS b ON a.CustomerID = b.CustomerID
ORDER BY b.CustomerID;

Result:

Ct  name    quantity    Date    att3  att4  x2      x3
1   Jack    114     21/12/2012  1       6   att2    att3
1   Jack    20      19/12/2012  1       1   att2    att3
1   Jack    10      19/12/2012  1       6   att2    att3
2   Joe     140     16/12/2012  9       6   att2    att3
2   Joe     200     13/12/2012  8       1   att2    att3
2   Joe     100     12/12/2012  5       6   att2    att3
Comments