Count query without using totals to avoid redesign of existing functioning query

[davesaintjay]

Hi there guys. First time on this forum so be gentle :-) Any help would be great.

All I need is an extra column to show the number of items that were bought in any one order. That way I can calculate postage paid (plus invoicing and envelope cost) divided by that extra column to reflect that any one item in a multiple order would cost (in this case) a third of the postage paid in the order and not the full amount of the postage paid for the whole order. This would mean that I can then use it analyse profits for a particular product more accurately.

Here is my existing query which works fine:-

SELECT Customers.schoolname, Orders.invoiceid, Orders.orderdate, products.ProductName, [Order Details].Quantity, Orders.postage, Orders.postagepaid, mailshots.mailshotname, mailshots.sellerrate, [Order Details].discount, Round([sellerrate]/100*[europaid],2) AS sellercut, [Quantity]*[UnitPrice]-[discount]+[postage] AS Sub, [Sub]-[postagepaid]-[sellercut] AS [Sub Total], products.UnitPrice, mailshots.taxrate, Round([sellercut]*[taxrate]/100,2) AS sellercuttax, mailshots.fixed, mailshots.variable, Round([fixed]*[taxrate]/100,2) AS fixedtax, Round([variable]*[taxrate]/100,2) AS variabletax, [Sub Total]-[sellercut]-[sellercuttax]-[fixed]-[fixedtax]-[variable]-[variabletax] AS Total, [UnitPrice]*[Quantity]-[discount] AS soldfor, [sellercut]+[sellercuttax]+[fixed]+[fixedtax]+[variable]+[variabletax] AS sellerfees, Orders.paymentmethod, mailshots.postagefee, Round([postage]*[postagefee]/100,2) AS ebaypostagefee, mailshots.paypalcharge, mailshots.paymentrate, Round(([soldfor]+[postage])*[paymentrate]/1000,2) AS paymentcharge, [paymentcharge]+[paypalcharge] AS paypalfees, products.printquantity, mailshots.invoicing, Orders.ordersource, 1.19 AS eurorate, Round([fixed]*[eurorate],2) AS fixedeuro, Round([fixedtax]*[eurorate],2) AS fixedtaxeuro, [fixedeuro]+[fixedtaxeuro] AS fixedeurototal, Round([variable]*[eurorate],2) AS variableeuro, Round([variabletax]*[eurorate],2) AS variabletaxeuro, [variableeuro]+[variabletaxeuro] AS variableeurototal, Round(([sellercut]+[sellercuttax])*[eurorate],2) AS sellercuttotaleuro, Round([soldfor]*[eurorate],2) AS soldforeuro, [sellercuttotal]+[eurovariabletotal]+[eurofixedtotal] AS totalfeeseuro, Orders.eurotrans, 0.84639 AS eurotoukrate, Round([newtranseuro]*[eurotoukrate],2) AS transuk, products.Pages, products.[Cover Pages], supplyorders.supplycost, supplyorders.supplyquantity, supplyorders.supplybought, products.colourink, products.blackink, [supplycost]/[supplyquantity] AS supplypercost, blackprint.blackcost, blackprint.blackquantity, [blackcost]/[blackquantity] AS blackcostper, colourprint.colourcost, colourprint.colourquantity, [colourcost]/[colourquantity] AS colourcostper, [Quantity]*([blackcostper]*[Pages]) AS blackprinttotal, [Quantity]*([colourcostper]*[Cover Pages]) AS colourprinttotal, [Quantity]*([supplypercost]*[Pages]) AS blackpagestotal, cello.cellocost, cello.celloquantity, [cellocost]/[celloquantity] AS cellounit, addresslabels.addresslabelcost, addresslabels.addresslabelquantity, burning.burningcost, burning.burningquantity, cdlabels.cdlabelscost, cdlabels.cdlabelsquantity, sellotape.sellotapecost, sellotape.sellotapequantity, wallets.walletcost, wallets.walletquantity, dvdcases.dvdcasecost, dvdcases.dvdcasequantity, [dvdcasecost]/[dvdcasequantity] AS dvdcaseunit, [walletcost]/[walletquantity] AS walletunit, [sellotapecost]/[sellotapequantity]/100 AS sellotapeunit, [cdlabelscost]/[cdlabelsquantity] AS cdlabelsunit, [burningcost]/[burningquantity] AS burningunit, [addresslabelcost]/[addresslabelquantity] AS addresslabelunit, [purchaseunit]+[cellounit]+[dvdcaseunit]+[burningunit]+[dvdprintunit]+[coverpagestotal]+[blackpagestotal]+[blackprinttotal]+[colourprinttotal]+[sellotapeunit]+[cdlabelsunit]+[addresslabelunit]+[walletunit]+[barcodeprint]+[cdlabelprintunit] AS productoverhead, [Cover Pages]*[colourcostper] AS dvdcoverunit, [supplypercost]*[Cover Pages] AS coverpagestotal, dvdprint.dvdprintcost, dvdprint.dvdprintquantity, [dvdprintcost]/[dvdprintquantity]/2 AS dvdprintunit, [blackpagestotal]+[coverpagestotal]+[blackprinttotal]+[colourprinttotal] AS inhouseprintcost, [blackcostper]/16 AS barcodeprint, [colour2cost]/[colour2quantity]/2 AS cdlabelprintunit, cdlabels.cdlabelsprint, colourprint2.colour2cost, colourprint2.colour2quantity, colourprint2.colour2bought, invoiceoverheads.invoiceoverheadcost, invoiceoverheads.invoicetype, [invoiceoverheadcost]/10000000000000000 AS invoicetotalcost, envelopes.envelopecost, envelopes.envelopequantity, envelopes.envelopebought, [envelopecost]/[envelopequantity] AS envelopepercost, [envelopepercost]+[invoicetotalcost] AS orderoverheads, [transuk]-[productoverhead]-[orderoverheads]-[postagepaid] AS profit, Purchase.orderref, Purchase.purchasequantity, Purchase.purchaseamount, [purchaseamount]/[purchasequantity] AS purchaseunit, Orders.postageeuro, [Order Details].europaid, [sellercut]+[sellercuttax] AS sellercuttotal, mailshots.eurovariable, Round([eurovariable]*[taxrate]/100,2) AS eurovariabletax, [eurovariable]+[eurovariabletax] AS eurovariabletotal, mailshots.eurofixed, Round([eurofixed]*[taxrate]/100,2) AS eurofixedtax, [eurofixed]+[eurofixedtax] AS eurofixedtotal, [totalfeeseuro]/[eurorate] AS totalfees, [europaid]+[europostage]-[totalfeeseuro] AS newtranseuro, mailshots.europostage, [Order Details].itemno
FROM mailshots INNER JOIN (Customers INNER JOIN (Purchase INNER JOIN (envelopes INNER JOIN (invoiceoverheads INNER JOIN (colourprint2 INNER JOIN (dvdprint INNER JOIN (dvdcases INNER JOIN (wallets INNER JOIN (sellotape INNER JOIN (cdlabels INNER JOIN (burning INNER JOIN (addresslabels INNER JOIN (cello INNER JOIN (colourprint INNER JOIN (blackprint INNER JOIN (supplyorders INNER JOIN ((Orders INNER JOIN [Order Details] ON Orders.invoiceid = [Order Details].OrderID) INNER JOIN products ON [Order Details].productid = products.ProductID) ON supplyorders.ID = products.paperused) ON blackprint.ID = products.blackink) ON colourprint.ID = products.colourink) ON cello.ID = products.celloused) ON addresslabels.ID = products.barcodelabel) ON burning.ID = products.burningused) ON cdlabels.ID = products.cdlabel) ON sellotape.ID = products.sellotape) ON wallets.ID = products.walletused) ON dvdcases.ID = products.dvdcase) ON dvdprint.ID = products.dvdprint) ON colourprint2.ID = products.colourink2) ON invoiceoverheads.ID = Orders.invoiceused) ON envelopes.ID = Orders.envelopeused) ON Purchase.purchaseno = products.purchaseused) ON Customers.customerid = Orders.customerid) ON mailshots.ID = Orders.ordersource
WHERE (((Orders.ordersource)=38) AND ((Orders.eurotrans)>0))
ORDER BY Orders.invoiceid DESC;

Here is the separate query which shows the count column I need. However I can't figure out to add in this functionality to the main code so I could calculate the postage paid divided by the count of items bought in all orders.

I believe I need to avoid DCount as I need the query to show more than one OrderID.

SELECT Count([Order Details].itemno) AS CountOfitemno, [Order Details].OrderID
FROM [Order Details]
GROUP BY [Order Details].OrderID;

.

Any help would be much appreciated!

[aytee111]

Is this to be displayed on a form/report/VBA? You may not need to join them but treat them separately.

[davesaintjay]

Hi there! It's to be included on a report once I'm happy that I've done as much as I can do within the query. I take your point but would that mean that I would need to do a sub report etc.?

[aytee111]

You could use the report to do it. In the group footer you can do your calc in the control source of a text box: =Count(*) will give you the count of records for that group.

[davesaintjay]

Thanks so much that's working great when it comes to showing this data!

The problem I have now is that if I wnat to run a query on just one product it won't be able to do the count properly as the other products in the order won't show on the report.

Sorry to expand on this but is there a way I could run an update query to take the total items and feed them into a new field? That way it would work when querying just one product profit.

Thanks again :-)

[aytee111]

Update query - no. You never want to store calculated data. When would you want to run the query on just one product? If it is this report, then the selection would come from a form and you would open this report with a filter. If it is any other time, then we are back to having its own query.

[InsuranceGuy]

Thanks so much that's working great when it comes to showing this data!

The problem I have now is that if I wnat to run a query on just one product it won't be able to do the count properly as the other products in the order won't show on the report.

Sorry to expand on this but is there a way I could run an update query to take the total items and feed them into a new field? That way it would work when querying just one product profit.

Thanks again :-)

Use your code, slightly modified, as an additional column in your query.

CountOfitemno: (SELECT Count([OD].itemno) AS 
FROM [Order Details] as OD
where [OD].OrderID = [Order Details].OrderID
GROUP BY [OD].OrderID)

Cheers,

Jeff

[davesaintjay]

Right, so the report would filter out the product name based on the form selection and not altering the query to ask for productname input?

[aytee111]

Correct. Open the report with a filter: DoCmd.OpenReport "name",type,,"OrderNumber=" & me!ordernumber - or whatever your fields are.

[davesaintjay]

Thanks so much for your help on this :-) I have now built a report that reflects the profit correctly for each order which is perfect for this goal but I can't get the profit total on the report footer to calculate correctly. Keep getting an error questioning Text47 which is the new correct profit for each order. Any suggestions? This is the code for Text47 but I can't Sum these values to equal 29.65 in the report footer.

=Sum([transuk]-[productoverhead])-[orderoverheads]-[postagepaid]

[aytee111]

Not enough details for me to answer this. If you are in the footer section and those are the correct names from your record source then - what exactly is the error that you are getting?

[davesaintjay]

Not enough details for me to answer this. If you are in the footer section and those are the correct names from your record source then - what exactly is the error that you are getting?

Sorry my bad. Text47 is a calculation within just this report, in the invoiceid footer which is the grouping level, but if I try to get a total for all the invoice profits in the page footer or report footer and run the report, I get Text47 and an empty box like it doesn't understand what Text47 is.

Any ideas?

Cheers
Dave

[aytee111]

Text47 is not "a calculation", it is the name of an object on your report, the code you posted above is not code, it may be the control source of a text box on your report? See why I am confused!

There are three amounts showing for each line above, is any of them the "code" you posted above? What are the names of those fields? Can you post your database, or at least this portion of it? That will help to understand why this problem is happening.

[davesaintjay]

Text47 is not "a calculation", it is the name of an object on your report, the code you posted above is not code, it may be the control source of a text box on your report? See why I am confused!

There are three amounts showing for each line above, is any of them the "code" you posted above? What are the names of those fields? Can you post your database, or at least this portion of it? That will help to understand why this problem is happening.

Sorry I'll be the first to admit that my skills are amateur in both database deisgn and posting to this forum atm.

I didn't know what to include exactly but anyway I have posted the db with only the 1 report and the 1 query it is based on....

Many thanks for your patience with this....

[aytee111]

Oh right - you can't refer to the name of a control, you must refer to fields on the record source. On your report footer you have copied the first two totals directly from the InvoiceID footer, you must do the same for the third field as well. Its control source must match the field on the invoice id footer.