What is the best method to count # of occurrences of unique values in a field?

[orange]

The code I showed is a query.



You would open the query window, design view, then click on SQL.
Then copy and paste the code into that window --you may have to adjust the code to show your fields and table names.
You will see a red exclamation mark in the window header. Click that to execute the query.
I am attaching a sample query in SQL view to show the window and header generally.

See the tutorials here
in the EXAMPLES section and the one labelled Entity relationship Diagramming. They are excellent for design concepts. You have to work through a couple of these, but you will learn.
Also you should be familiar with Normalization.
And for Access in general see this youtube series by Steve Bishop.



Good luck.

[slackerboy]

Thanks for the help on building a query, I am sure that I will learn from what you advised me on, but as i said I have already built a query prior to asking for help, that works. As I said I dont know of a way to bring those numbers out of the query into the table. The copy of the database I sent you has that query in it. Could you please advise how to get the number occurrences out of the query into the table. The query name that I have already built is "Item on Receipt Query".

[Gina Maylone]

RECEIPT & CLIENT DATABASE - Copy (2).zip

Here are a couple of things for you. See your main form. First I made a button that will calculate the number of invoices and put that number in an unbound text box. Then I added a small subform based on your query that counts invoice numbers. Since the number of invoices can change, you really don't want to commit that number to your table (as with an update query). I also changed the name of your field "Invoice #" to InvoiceNum.

I hope this gives you what you want. Happy Thanksgiving!

[slackerboy]

RECEIPT & CLIENT DATABASE - Copy (2).zip

Here are a couple of things for you. See your main form. First I made a button that will calculate the number of invoices and put that number in an unbound text box. Then I added a small subform based on your query that counts invoice numbers. Since the number of invoices can change, you really don't want to commit that number to your table (as with an update query). I also changed the name of your field "Invoice #" to InvoiceNum.

I hope this gives you what you want. Happy Thanksgiving!

Sorry to start with I should have cleaned up this database before sending for a few reasons. Anyways this has helped in some reguards but its still not what I need. The command button and the unbound text box that you provided is a great idea but it still doesnt get the numbers into the table field that i need. I think i understand why you say its not the best to have go directly in because you enter the first row and its 1 and the next row would be 2 and so on. The only row that would contain the true value would be the last row. Now if that command button was setup to run after all the rows were entered the way it is setup it would only input the number on the last row. Unfortunately i need each row to have its own correct number.
I see in the command button vba you have "ME." could you tell me how "ME." works (for me examples spelled out has been my wealth of knowledge and this being my own applications would help my understanding greatly). Another question in your macro and queries i noticed your upper and lower case capitalization didnt seem to matter to the queries or the macro? Youve told me about underscores verses spaces but do you have any tips about capitalization of names titles or fields for cell reference or anything in macro vba query tables? Does it matter for upper or lower case or is it all for formatting and visual?

[June7]

Me. is alias for the form or report the code is behind.

Access is by default not case sensitive so upper and lower case makes no difference to Access or VBA but it does to people. People read InvoiceNum easier than INVOICENUM.

[Gina Maylone]

So, I think what you really need to do, to begin with (and will help greatly toward normalizing), have 2 tables, Invoices and InvoiceDetails. they would be related (one to many) by InvoiceNum. InvoiceDetails would include each line item for the Invoice and could easily be counted, set it up similar to your Work Order form. Then you also need a Supplier table (include all information related to suppliers). Have one Cities table (instead of Parts Supplier City, Shipped to City, etc) to use in your other tables, include all cities used throughout the database. Group your objects logically. ie. all items related to an invoice go in the Invoice table and so on.

HTH.
Gina

[slackerboy]

So, I think what you really need to do, to begin with (and will help greatly toward normalizing), have 2 tables, Invoices and InvoiceDetails. they would be related (one to many) by InvoiceNum. InvoiceDetails would include each line item for the Invoice and could easily be counted, set it up similar to your Work Order form. Then you also need a Supplier table (include all information related to suppliers). Have one Cities table (instead of Parts Supplier City, Shipped to City, etc) to use in your other tables, include all cities used throughout the database. Group your objects logically. ie. all items related to an invoice go in the Invoice table and so on.

HTH.
Gina

Ok maybe ill ask a different approach. What I am trying to do is have a column or field in the all receipt info table which contains the number of items of one particular receipt using the invoice_num field. Is there another way to go about it? With each row having its proper number for calculating shipping and border fees, for calculating costs of wages and vehicles associated with however many products i picked up on that given trip. Examples: driving 10 mins away to pick up 2 parts or driving 80 mins to pick up 2 parts. There are 2 different costs associated with each of those trips to get parts. Both parts could be the same price from either supplier but obviously one costs more than the other. As well with other variations such as parts being shipped to my door or driving by 5 different suppliers to pickup 20 different parts and lets say 4 parts at each location but from each supplier you get 3 different receipts and if you were just going to one location and back it would take lets say 80 mins, but now you add 4 more stops at different supplier across the city now it has taken 200 mins plus higher vehicles costs involved. Obviously its cheaper to pickup all the parts at once verses individual trips. I was using a formula up until recently that took the percentage of the unit price to the subtotal price to figure out the % of costs associated with each part and still have to manually input the number of invoices or sales receipts into the form for each trip associated with picking up those products, but there is a problem with that. Using the percentage way measures the unit price to get the percentage to distribute. the problem is most parts arent the same price. so you pick up 2 parts at one supplier on one trip, one of the parts cost 80 bucks and the other part costs 20 bucks, now 80% of the costs associated with picking up that part is on the higher priced part. Your going to the same location to get two different priced items, the costs associated with each part should be 50%.
That is why i need that field to contain those respective numbers for each row. Because this database has been used for the last few years there are a lot of entries that need that number for calculations within the table or other queries or reports later, as well for future data. Putting the number into the form is a good idea for future data but nothing for the past. Queries are great but I am usually using forms or entry directly into the tables. So my thought would be to build a calculated field or use a macro or vba to give the respective row count for that field instead of running a query all the time. I found the programming of vba in excel to be easy, I cant say the same for access.

[June7]

Normalized structure, similar to Invoice and InvoiceDetails, would be Receipt and ReceiptDetails tables (also a 1-to-many relationship). ReceiptDetails would have the items purchased and their prices.

A Calculated field in table cannot accomplish this. This would require VBA code to update table field. Saving calculated data, especially aggregate data, to table is usually a bad idea. This data should be calculated when needed. A domain aggregate function (DSum, DAvg, etc) can be used in a query but can cause slow performance.

Reports are the best vehicle for aggregate and percentage calcs.