Solved Counting records with certain criteria in a recordset clone (1 Viewer)

[MarionD]

good morning all,

I hope I can explain my problem "understandably"

On my main form (Customer) I have a sub form where concert tickets are selected (Category 1 or 2) for the selected customer.
Now I would like to add a new record to a "summary" sub form.
I have made a recordset clone of the selected tickets in the 1st subform (i have 10 records, 3 Category 1 and 7 category 2)
I have a recordset of the summary table then I

summary.addnew
Total nr. of tickets - recordcount of clone
category 1 - ?
category 2- ?
String value of tickets numbers: 001;002;056;078 etc...
summary.update

Can anyone help me a bit here? How do I count the records in the recordset.clone with certain criteria?
And how can I create a string of the ticket numbers in the recordset.clone

Thanks so much

 

[arnelgp]

you can just create a Query to count Each Category by Customer:

select CustomerID,
DCount("1","ticketTable","CustomerID = " & [CustomerID & " And Category = 'Category 1'") As [Category 1 Total],
DCount("1","ticketTable","CustomerID = " & [CustomerID & " And Category = 'Category 2'") As [Category 2 Total],
DCount("1","ticketTable","CustomerID = " & [CustomerID) As [Total Tickets] FROM CustomerTable;

 

[MarionD]

Thanks. so much I am working on it.

 

[Pat Hartman]

If you have the room, make a little subform that shows the summary. Put it in either the header or the footer of the main form. This avoids you having to hard-code a number of domain functions to get the summary. Or add a button to open a popup form if the number of items is too many to show as a subform.

Select Category, Count(*), CustomerID
From YourTable where CustomerID = Forms!YourForm!CustomerID
Group by Category;

 

[MarionD]

Thanks Pat!

Feel so honoured that you answered

I have it working now with some help from ArnelGP and a little research.
I have the Summary in a subform and edit/add as needed. Works fine thanks again to all

 

[Pat Hartman]

I'm happy to help