Solved DCOUNT

[Space Cowboy]

Good Evening Good People,

I need to go through a query table and count the number of times that an item has been sold and put the total in a new column.
Can this be done in the same table or do I need to do a new query?
I would like the figure in the same query if possible
I forsee the the item appearing in the list many times, with the total number of individual sales listed by the side of each item.
My plan was then to select for no duplicates, so I can do more calculations with the figure.

Am I on the right lines or is there a better way?

 

[jdraw]

Generally you would run a query to calculate the total. You would not store the total in a table. You would put the Total value on a form or report as needed.

What is the ultimate goal? What additional calculations?

 

[tvanstiphout]

You're providing very little information, so we only have wild guesses available as to what you want.
Maybe this, using the Northwind 2.3 OrderDetails table:
SELECT OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID, DCount("*","OrderDetails","ProductID=" & [OrderDetails].[ProductID]) AS CountOfProductID
FROM OrderDetails
GROUP BY OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID;

 

[Space Cowboy]

I will divide the total of the profit margin for each item, by the number of sales of the item to determine average profit per sale so that I can compare the resulting figure against all other items.

I have managed to calculate the sum of the profit for each occurrence, just need to average it out

 

[Space Cowboy]

You're providing very little information, so we only have wild guesses available as to what you want.
Maybe this, using the Northwind 2.3 OrderDetails table:
SELECT OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID, DCount("*","OrderDetails","ProductID=" & [OrderDetails].[ProductID]) AS CountOfProductID
FROM OrderDetails
GROUP BY OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID;

Thank you Tom,

That is exactly the calculation that I need to perform.
So its new query only or could I incorporate into the data in the same existing query?

PS. is it safe to activate the Northwind demo? I am getting a security warning about VBA macros

 

[The_Doc_Man]

If you have the profit per sale as a field (in a table OR as a computed field in a query) you can do a GROUP BY per item and use the aggregate function AVG or the domain aggregate DAVG. From this link below you can find a LOT of useful domain aggregate functions.

Functions (category list)

Office VBA reference topic

learn.microsoft.com

 

[Space Cowboy]

Thank you @The_Doc_Man

 

[Space Cowboy]

This worked out very simple in the end

Select saleitem.sales, avg(sales.price) As AvgOfprice
From Sales
Group by saleitem.sales;

far simpler than my thought process, I just wish I knew what does what. 67,000 rows took a matter of seconds.
thank you all for you help and direction to relevant info.

Some results come back with
#########
Why is that?

 

[Gasman]

Column is too narrow?

 

[Space Cowboy]

Column is too narrow?

Thank you Gasman, that appears to be it.
Whats causing it is recurring number on the decimal place.
Is it possible to limit that to 2 decimal places?

 

[CJ_London]

Use the format property?

 

[jdraw]

Is Sales.Price Currency datatype?

 

[The_Doc_Man]

I'll expand on what CJ and JDraw mentioned.

In Access, you have datatypes SINGLE and DOUBLE that are scientific numbers that have trouble with some fractions. Since everything is in binary, you have to compute things with binary fractions - but for currency using dollars and cents, many common fractions don't come out even. You see, 1/10 factors to 1/2 x 1/5, so the 1/2 is easy in binary... but 1/5 is NOT. Just as 1/3 in decimal is repeating .333333..., 1/5 in binary is .0011001100110011... but once you get past a certain point, that sequence has to TRUNCATE ('cause it ran out of bits). That truncation makes for all sorts of rounding errors that can eventually build up.

JDraw's suggest attacks the problem by storing the number differently. IF you use datatype CURRENCY for your money-related things, it has a fixed number of decimal places (4) and has no repetition beyond that 4th decimal place.

CJ's approach goes the other way to prevent the oddity by using a FORMAT() function to limit the decimal places. And in form controls, query field properties, and table field properties, there is a "decimal places" property that you can also use for currency display. The stored number doesn't change but the way you display it does.

 

[CJ_London]

a FORMAT() function to limit the decimal places.

small correction - I was suggesting the format property which will hide the surplus characters. This would be OK if you are not going to add (such as adding sales values) but you don't add averages - or if you do, the result would be meaningless

Using the format function, converts the value to text which in this situation is probably OK

 

[Pat Hartman]

The Format property does not alter what data is stored so you may round to two decimal places for viewing but if you sum the values, you WILL get accumulation errors. The only way to avoid rounding or accumulation errors is to round as you store and to use the Currency data type which is actually a scaled integer. So 95.67 is stored as 956700 and divided by 1000 to correctly place the decimal point for display.

I would suggest NEVER formatting anything at the table level. All that does is to obfuscate the actual stored value. People run into trouble all the time by formatting dates to hide the time element. Then they wonder why their queries don't select the records they think should be selected. Dates ALWAYS contain a time element. It just may be .0 or Midnight so you don't see any time with the date. But if you made the mistake of using Now() when you really meant Date(), you end up with time in your dates and so you never get a match when you're looking for 6/3/24.

 

[Space Cowboy]

Thank you all so much,
This little problem has been a fantastic learning experience for me.
I think that I now understand the "group by" operator. My goodness, that is one powerful command.
Formatting through the field properties sorted out the display issue.
Apologies for being PITA but every problem is whole new experience.
Thanks again.

 

[June7]

What is ultimate goal? A report? Consider using report Sorting & Grouping and aggregate calcs in textbox. This allows display of detail data as well as summary calculations.

 

[Space Cowboy]

What is ultimate goal? A report? Consider using report Sorting & Grouping and aggregate calcs in textbox. This allows display of detail data as well summary calculations.

Hi June7,

I am new to access, I do not even know what is possible never mind how to do it.
I have ordered 2016 access bible and 2010 programmers reference so I can try to get a handle on whats possible. At the minute I am trying to translate my thoughts into data, I have hundreds of questions to ask of the database.
Sadly it is not some neat and tidy little project. Its expansive and will develop as my my thoughts and ideas do. It will be never ending I hope.

 

[The_Doc_Man]

I have hundreds of questions to ask of the database.

Old programmer's rule #2 - the database cannot tell you anything you didn't tell it first, or at least tell it how to compute what you wanted based on what you DID tell it first.

Corollary - it pays to decide ahead of time what questions you will want to ask so that you can assure that the answer or the potential for an answer WILL be present in the final product.

 

[Space Cowboy]

@The_Doc_Man
The database is already in existence, it has 50 tables with 20-25 fields in each table and100's of thousands of rows in each table. I cannot do anything about it really.
I do not like the output that it generically spits out so I am trying to learn how to manipulate whats in there, so that I get what I want instead of what I am given.