AVG problem - duplicate records

[sloth]

Hi there, this problem should have a simple solution, i just cant think of it ..

got two tables, tblSales and tblInvoiceItems, invoice items has a costprice in there - product cost price
tblsales works fine when theres only one occurance of this costprice but when we create another invoice and change the costprice, tblsales now has duplicated records (which i understand) with all fields being the same except showing both the old costprice and the new one..


i want to average the costprice out so that the query only returns the average of both records

i.e. say old costprice was 10

sales table would have

transactionday costprice product id
Today 10 A100

if i add another invoice (say cost price now changes) to 12

sales then would have two records

transactionday costprice productid
today 10 A100
today 12 A100

i want it to just have

transactionday costprice productid
today 11 A100

i know i need to use AVG but not sure where, do i need to average on the duplicate records ? ive tried using AVG on costprice but it still pulls up the same number of records, which im thinking is cause im averaging it in the wrong place as its only averaging one record
ive tried using AVG elsewhere to try to get it to work but no luck!

if anyone can help i would be appreciative!

 

[Jon K]

Try this query. It should give you the average cost price of duplicate records in the table for each transaction day:

SELECT TransactionDay, ProductID, sum(CostPrice) AS Sum_cost,
count(*) AS NumOfRecords, avg(CostPrice) AS AerageCostPrice
FROM tblSales
GROUP BY TransactionDay, ProductID
HAVING count(*)>1;

 

[sloth]

Ill try that, but i still need all the sales, including the ones without duplicates too.

its based on cost averaging the costprice so i still need to list all the sales but the sales that appear twice or more due to the duplicate costprice records in the other table need to be averaged out

does that make it clearer at all?

 

[Jon K]

Just delete:
HAVING count(*)>1

This should include the non-duplicate records in the result.

Besides, the "sum(CostPrice) AS Sum_cost" and "count(*) AS NumOfRecords" in the SELECT clause are only for information and may also be deleted.

 

[sloth]

yeh i tr ied all of the above, i still get the same results, im not sure if im averaging it in the right spot, do i need two queries one to calculate the sum and one to average it?
its basically for a Point of sale system

does anyone know of sites with sample access databases whom maybe i could look at ?

 

[David R]

It sounds like you need two queries

You cannot ask Access to simultaneously ignore duplicates for one calculation and use all records for another in the same query.

 

[Jon K]

For a table of

transactionday costprice productid
today 10 A100
today 12 A100
etc

the SQL select query

SELECT transactionday, productid, avg(costprice) as AverageCostPrice
FROM tblSales
GROUP BY transactionday, productid

should work like a charm, returning the average costprice of each group of "transactionday + productid".

Since it still pulls up the same number of records, it sounds like the grouping has failed. The only reason I can think of is that the transactionday field contains not only the transaction day, but also the transaction time.

If the transactionday field is of Data Type "Date/Time" and its contents do include transaction time, then the SQL query need to be altered to retrieve(i.e. group by) only the date from the transactionday field:

SELECT format(transactionday,"Short Date"), productid,
avg(costprice) as AverageCostPrice
FROM tblSales
GROUP BY format(transactionday,"Short Date"), productid


However, if the transactionday field is not of Data Type "Date/Time",
please post back its field type together with some of its field contents.