Help needed with counts

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
Hi there- in my database i am entering orders for cards. I have multiple orders for each customer. What I need to fiqure out is the best way to create labels that will give me one label per customer with the total # of cards orders on it. The card is the same buy the way and is called "1 Day".


The label should look like:
1 Day-4
Mary Smith
123 Main St
Anytown, Me, 01234
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
How about first of all creating a query that returns the required data?

Then you could use Word mail merge to create your labels using that query as the source.
 
Upvote 0
Thats what i tried but its not adding the total of cards per customer. Its just listing all order information.
I sure would apprecaite your help.
My columns in the query are as follows:
CustomerID,FirstName,LastName,Address,Address1,City,State,Zip,Source,Batch,
CardNameReq,CardReq,QtyCardsReq



And the sample data
28,Mary,Smith,123 Main Street,Overland Park,KS,66062,KCI,1,1 Day,TRUE,1
I have critera prompts set for the batch and source columns
they are
=[Enter Source]and=[Enter Batch#]
I set everything to group by except for the QtyCardsReq which i set to sum.
 
Upvote 0
Don't use Totals to get the count use DCount.
 
Upvote 0
I'm sorry I can't really give much more help but something like this should return the number of times the customer ID appears in the table.

SELECT DISTINCT [Orders Table].[Customer Name], DCount("[CustomerID]","[Orders Table]","[CustomerID]=" & [CustomerID]) AS NoCards
FROM [Orders Table];
 
Upvote 0
hi- its not that i am looking for the number of times the custoomer id appears but how many cards each customer ordered so that I can get a total # of cards per customer.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top