Complex Filter

[Kevin199]

I have a table (tblCustInv) with customer numbers and invoice numbers.
Monthly, I make another table (tblCust5) with only customers with 5 or more
invoices. I would like to make tblCust5 using a query or code. It would
save a lot of time as there are over 10,000 records in tblCustInv and 1200 in
tblCust5. Any suggestions would be great.

 

[John W. Vinson]

I have a table (tblCustInv) with customer numbers and invoice numbers.
Monthly, I make another table (tblCust5) with only customers with 5 or more
invoices. I would like to make tblCust5 using a query or code. It would
save a lot of time as there are over 10,000 records in tblCustInv and 1200 in
tblCust5. Any suggestions would be great.

Well, you really should NOT create this redundant extra table. It's perfectly
straightforward to create a Query which will dynamically calculate the records
with five or more invoices. Assuming that you also have a tblCust with the
customer names and information, you could use

SELECT tblCust.CustID, tblCust.LastName, tblCust.FirstName, <etc>
FROM tblCust INNER JOIN tblCustInv
ON tblCustInv.CustID = tblCust.CustID
GROUP BY tblCust.CustID, tblCust.LastName, tblCust.FirstName, <etc>
HAVING Count(*) >= 5


as the basis of a form or report.

If you have some good reason to store the same customer information
redundantly in two different tables, please explain!

 

[kc-mass]

Hi Kevin,

You do not need or want the tblCust5. Use one query to get the customers
had more than X invoices ( I used the number 3).

Reference that query to get all the invoices or the total cost, etc.

Query examples are below. You can paste them into the design panel.

' This query gives you all the Customers with more than 3 invoices
' in Dec 2009

SELECT tblCustInv.CustNo
FROM tblCustInv
WHERE (((Month([InvDate]))=12) AND ((Year([InvDate]))=2009))
GROUP BY tblCustInv.CustNo;


' This uses the above query results and gives you all the invoices for
' all the customers who had more than 3 invoices in 12/09
SELECT tblCustomers.CustNo, tblCustInv.InvoiceNo, tblCustInv.InvDate,
Month([InvDate]) AS InvMonth, Year([InvDate]) AS InvYear
FROM (tblCustInv RIGHT JOIN qryGrtThan3 ON tblCustInv.FKCustNo =
qryGrtThan3.FKCustNo) LEFT JOIN tblCustomers ON qryGrtThan3.FKCustNo =
tblCustomers.CustNo
WHERE (((Month([InvDate]))=12) AND ((Year([InvDate]))=2009));



Regards

Kevin

 

[Kevin199]

Thanks John and Kevin. We pass this table on to a third party ad firm. I
made a maketable query to accomplish what I needed. I have another problem
now, I am getting brave. I have another table (tblCustProd) with customer
numbers and products. I would like to group the customers by common
products.
Info:
CustID Prod CustID Prod CustID Prod
1 18 3 6 5 5
1 20 3 19 5 6
1 22 3 20 5 19
1 26 3 23 5 23
1 27 3 24 5 24
2 19 3 25 5 25
2 20 4 19
2 23 4 20
2 24 4 23
2 25 4 24
Output 1:
Prod 5 – 5 Prod 19 – 2,3,4,5 Prod 23-2,3,4,5 Prod 26-1
Prod 6 – 3, 5 Prod 20 – 1,2,3,4 Prod 24-2,3,4,5
Prod 18 – 1 Prod 22- 1 Prod 25-2,3,5
Output 2:
GroupA – Prod 5 GroupC- Prod 18,22,26 GroupE-Prod 20
GroupB – Prod 6 GroupD-Prod 19,23,24 GroupF-Prod 25

We will use this information to run correlations on customer demographic
information.

Hi Kevin,

You do not need or want the tblCust5. Use one query to get the customers
had more than X invoices ( I used the number 3).

Reference that query to get all the invoices or the total cost, etc.

Query examples are below. You can paste them into the design panel.

' This query gives you all the Customers with more than 3 invoices
' in Dec 2009

SELECT tblCustInv.CustNo
FROM tblCustInv
WHERE (((Month([InvDate]))=12) AND ((Year([InvDate]))=2009))
GROUP BY tblCustInv.CustNo;


' This uses the above query results and gives you all the invoices for
' all the customers who had more than 3 invoices in 12/09
SELECT tblCustomers.CustNo, tblCustInv.InvoiceNo, tblCustInv.InvDate,
Month([InvDate]) AS InvMonth, Year([InvDate]) AS InvYear
FROM (tblCustInv RIGHT JOIN qryGrtThan3 ON tblCustInv.FKCustNo =
qryGrtThan3.FKCustNo) LEFT JOIN tblCustomers ON qryGrtThan3.FKCustNo =
tblCustomers.CustNo
WHERE (((Month([InvDate]))=12) AND ((Year([InvDate]))=2009));



Regards

Kevin

I have a table (tblCustInv) with customer numbers and invoice numbers.
Monthly, I make another table (tblCust5) with only customers with 5 or
more
invoices. I would like to make tblCust5 using a query or code. It would
save a lot of time as there are over 10,000 records in tblCustInv and 1200
in
tblCust5. Any suggestions would be great.

 

[John W. Vinson]

Thanks John and Kevin. We pass this table on to a third party ad firm.

Well... no. You don't. You pass on *THE DATA* to a third party ad firm.

An Access Table has no existance outside the .mdb file. You can EXPORT the
data in a query to an external file - comma separated value text, an Excel
spreadsheet, a dBase .dbf file, etc. - but short of giving them an entire .mdb
database file containing a table (and other objects, necessarily) you cannot
pass on "the table".

I made a maketable query to accomplish what I needed. I have another problem
now, I am getting brave. I have another table (tblCustProd) with customer
numbers and products. I would like to group the customers by common
products.
Info:
CustID Prod CustID Prod CustID Prod
1 18 3 6 5 5
1 20 3 19 5 6
1 22 3 20 5 19
1 26 3 23 5 23
1 27 3 24 5 24
2 19 3 25 5 25
2 20 4 19
2 23 4 20
2 24 4 23
2 25 4 24
Output 1:
Prod 5 – 5 Prod 19 – 2,3,4,5 Prod 23-2,3,4,5 Prod 26-1
Prod 6 – 3, 5 Prod 20 – 1,2,3,4 Prod 24-2,3,4,5
Prod 18 – 1 Prod 22- 1 Prod 25-2,3,5
Output 2:
GroupA – Prod 5 GroupC- Prod 18,22,26 GroupE-Prod 20
GroupB – Prod 6 GroupD-Prod 19,23,24 GroupF-Prod 25

We will use this information to run correlations on customer demographic
information.

If you're trying to convert a series of records into a comma separated text
string you can use the code at
http://www.mvps.org/access/modules/mdl0004.htm

The example you post is confusing because it's not at all clear where the word
"Prod" is coming from.