Queries :: Aggregate Sales Totals Within Specific Time-frame?
Mar 23, 2013
stuck with this problem on trying to calculate the aggregate sales totals of a product within a specific time frame. The query that I have built instead divides each sum by date, where it should be grouped according to product instead.
Here are some screen shots as to how it looks in Access. [also see attached ZIP if you don't want to unbreak links ]
[URL]
As you can see, the PRODUCT_ID column is not combining together according to their IDs.
[URL]
This how my Design View looks.
The SQL for my current query is:
SELECT PRODUCTS.PRODUCT_ID, Sum(SALES.SALES) AS SumOfSALES, SALES.TRANSDATE
FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID
GROUP BY PRODUCTS.PRODUCT_ID, SALES.TRANSDATE
HAVING (((SALES.TRANSDATE)>=#9/1/2008# And (SALES.TRANSDATE)<=#12/31/2008#));
I have an error database that I'm attempting to build. I need the results of the query to show User ID when there are no errors during a defined time frame.
I'm trying to create a query that will sum the total time of a specific field. It seems to be doing it, however the value is off by by a couple minutes on all my examples.
Table
- BusArrivalTime - BusDepartTime
Query
In my Query I'm making a new field like this. It correctly figures out the difference.
Next I'm displaying that Query information inside of a Report by putting this in the Text Box on the Report. It correctly displays the time in the Hours/Minutes format.
=[BusWaitTime]60 & Format([BusWaitTime] Mod 60,":00")
Lastly, I'm using another Text Box on the Report to Sum the Grand Total of the Wait Time for all my records. Here is what Im putting in the Control Source
=Sum([BusWaitTime])60 & Format([BusWaitTime] Mod 60,":00")
...and it's summing my records, but the value is off by a few minutes and I cant figure out why.
In my example, I have 3 records with times of 3:14, 1:35, and 3:20. It should be totaling a figure of 8:09 but its coming to 8:14 instead.
The only problem is that in the two tables I was given in my assignment, there were multiple dates for the purchase, thus even though I have 74 products there is multiple listings i.e.
110-10 Sofa Chair 5x $2 profit:$10 - october 11 110-10 Sofa Chair 6x $2 profit:$12 - october 12
I need to group it so that it becomes this.
110-10 Sofa Chair 11x $2 profit:$22
The date/location is not important, only the aggregate sales. Here is my SQL ....
SELECT SALES.PRODUCT_ID, PRODUCTS.ITEM, PRODUCTS.PRICE, SALES.QUANTITY, PRODUCTS.GROSS_MARGIN, Sum([Quantity]*[Price]) AS Sales, [Quantity]*[Price]*[Gross_Margin] AS Profit, PRODUCTS.WIDTH, PRODUCTS.DEPTH FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID GROUP BY SALES.PRODUCT_ID, PRODUCTS.ITEM, PRODUCTS.PRICE, SALES.QUANTITY, PRODUCTS.GROSS_MARGIN, [Quantity]*[Price]*[Gross_Margin], PRODUCTS.WIDTH, PRODUCTS.DEPTH, SALES.TRANSDATE HAVING (((SALES.TRANSDATE)>=#9/1/2011# And (SALES.TRANSDATE)<=#12/31/2011#)) ORDER BY Sum([Quantity]*[Price]) DESC;
I have a report that is calculating account revenue for my sales reps. I am able to generate the report, group by the sales rep and then total their total account revenue. Now I want to group on the total field that I just calculated. How do I do that?
I am VERY, and I mean VERY new to Access. I've been racking my brain all afternoon and googling like crazy. I just completed two levels of training on Access 2010 and have never worked with the program before. I already set up my tables and now I am on to querying. I have a table with several columns, two of which are "Start Time" and "End Time". I already created a query using the DateDiff function to calculate the time difference for each record. It output a new field with the time difference in hours. Now, I want to sum the totals of the time differences by month and I cannot for the life of me figure it out. My new query has Date (m/dd/yyy) and Hours.
Hi guys, need your help again with the following problem i'm having. I'll give an example to make the problem clear. I have a table, eg table 1 with the following records: Tag________Desc________Msgno____DateTime 016GB001___Alarm OFF___1403______21/4/06 11:02:02 016GB001___Alarm OFF___1605______21/4/06 11:02:02 034GB005___Alarm ON____1403______21/4/06 11:02:04 016GB001___Alarm ON____1403______21/4/06 11:02:07 016GB001___Alarm ON____1605______21/4/06 11:02:07 048GB001___Alarm OFF___1403______21/4/06 11:05:31 048GB001___Alarm OFF___1605______21/4/06 11:05:32 048GB001___Alarm ON____1403______21/4/06 11:06:51 048GB001___Alarm ON____1605______21/4/06 11:06:52
I need a query that looks at records with similar Tag fields that occur with a 1 second difference between them and deletes those with "1403" in the Msgno field. I'm a newbie and i've got no idea where to start. The query should return the following records: I have a table, eg table 1 with the following records: Tag________Desc________Msgno____DateTime 016GB001___Alarm OFF___1605______21/4/06 11:02:02 034GB005___Alarm ON____1403______21/4/06 11:02:04 016GB001___Alarm ON____1605______21/4/06 11:02:07 048GB001___Alarm OFF___1605______21/4/06 11:05:32 048GB001___Alarm ON____1605______21/4/06 11:06:52
I hope the problem is clear. I know this is going to be one heck of a challenge, and i'm not sure whether this can actually be done in access!! Anyhelp from anybody would be enormously appreciated. Hope to see a solution soon!!
I have a database being used to track and bill therapy units. I have a table that stores the pre-authorizations that tells me within a [start date] and [end date] I am limited with X amount of units (that is all the client's insurance and/or funding source will pay for within the time period). I also have about 4 different codes I can be authorized for which is also stored in this table - each code a separate pre-auth record.
Another table is where I enter in the type of therapy (service code) I've completed with the client. I put in the Service date, start time and end time and it will automatically give me the amount of units to bill (1 hour=1 unit). If I happen to choose a code or enter in a service date that does not match up with a client's pre-auth (Service date is between start date and end date and matches code from pre-auth table) I have a simple text box that says "No auth for this code or service date" due to an IIF statement in a query. I'd like to take this a step further and give me the remaining units for the pre-authorization left when it does find a match.
What is happening is when it finds the authorization, it's not taking into consideration all the services I've done for that time frame. For example: I have a pre-auth from 12/1/12 to 1/31/13 for code 90806 for 12 units. I served the client for that code a total of 10 units thus far. I can't figure out how to link my 4 service records dated 12/3/12 (2 units), 12/16/12 (4 units), 12/27/12 (2 units), and 1/3/13 (2 units) to add together and then subtract from my auth for 12 units. I know the dates are the key but I'm lost.
i need domain aggregate functions to count the occurrences of specific value in a field, and when it exceeds 1, display null. or 'group by' subqueries with count() to see if you should populate the field or not.
I have a weekly list of transactions that come in the format DD/MM/YYYY HH:MM:SS, I need to tag these individual transactions with a week number. The problem is, I can't use the Datepart function etc. as the day is classed as running from 8am to 8am rather than midnight. I have a list of all of the weekly date ranges for a few years (with the time) so I was able to solve this problem in excel by using the Index and Match functions. However, I'm trying to automate this process as much as possible so I'd rather perform this function in Access.
I have a pre-established database with a form that calculates total sales and tax for a time period i specify. I want to add a box that will display the number of sale records. Ex. if i had sales of 50,000.00, and that came from 200 sales i want the box to show the number 200.
Am creating a Product-Sales Database, and I would like the corresponding Sales made in the Sale Table to be automatically deducted or to be reflected in the Product Table. The product table contains all my stock and has a relationship with the Sales Table. The Sale Table does not necessarily include the Stock. How can I create possibly a Sales Form that will be used as an entry point for all the products (stock) sold and automatically register the sold products in the Sales Table and at the same time make the required adjustments in the Products Table.
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
I have a query that will return a total number of hours between to specific dates. I would also like to have that same column return the total number of ours regardless of the dates. Can anyone please help me?
I am pulling sales results per customer and month. For presenting the numbers in a diagram I would like to show 0 sales for months in which no sales were generated. At the moment the query results only the months in which there are sales > 0. Is there a trick that I don't know?
SELECT Year([InvoiceDate]) & ' ' & Month([InvoiceDate]) AS [Month], totalsales_per_invoice([invoices]![InvoiceID],[invoices]![tax]) AS Ausdr1 FROM Invoices INNER JOIN customers ON Invoices.CustomerID = customers.CustomerID WHERE (((Invoices.InvoiceDate) Between #1/1/2013# And #7/1/2013#) AND ((customers.CustomerID)=145)) GROUP BY totalsales_per_invoice([invoices]![InvoiceID],[invoices]![tax]), Year([InvoiceDate]), Month([InvoiceDate]) ORDER BY Year([InvoiceDate]) DESC , Month([InvoiceDate]) DESC;
I have the following query. I got an error when I ran it.
Code: SELECT COUNT([encounter_number] WHERE status = 'Death')/ COUNT([encounter_number]) AS Death Ratio, tbl_test.facility_type AS Type, FROM tbl_test GROUP BY tbl_test.facility_type;
I think the problem is that first WHERE clause inside.
I have a list of product sales for various regions.How do I write a query to only retrieve the record with the highest value in a region, but include the region and product code in the results?For example, If I run an aggregate query using the max function I still get the record for region:
SELECT Stats.ProdCode, Max(Stats.Sales) AS MaxOfSales, Stats.Region FROM Stats GROUP BY Stats.ProdCode, Stats.Region;
I realise that this returns the record for each region because the 'Group By' is applied to that field, but if I remove it then that field is not going to be available in the results(???)
Basically trying to create a chart through a Query.
Table is as follow:
Applebrand Date Volume per week Category ------------ ------ -------------------- -----------
I am trying to display the aggregated volume in a chart. The volumes can change depending on the demand from the buyers and its always the latest volume for the specific applebrand that is in play.
Example
Apple A 20150101 10 Retail Apple B 20150202 100 Restaurants Apple B 20150303 200 Retail Apple A 20150404 50 Retail
The chart i would like to see is a aggregated volume for category "Retail" displaying a line from 10 to 210 to 250.
Instead my chart displays 10 to 200 to 50
Is there a way a solve this or should i use a different approach?
I need to change the below to a where clause to fit inside a union query that is just where clauses.
tblNEWNONTODATA.DateOfVisit) AS FirstOfDateOfVisit FROM tblNEWNONTODATA GROUP BY tblNEWNONTODATA.EVX, tblNEWNONTODATA.TCGDecision HAVING (((tblNEWNONTODATA.TCGDecision)="Adopted" Or (tblNEWNONTODATA.TCGDecision)="Hot Tasked") AND ((First(tblNEWNONTODATA.DateOfVisit))>=#10/1/2014#));
I have a query that will be assisting me find pricing based upon quantity ranges of specific equipment for a given FY.
I'm no access expert, and I keep getting "You tried to execute a query that does not include the specified expression...as part of an aggregate function".
I have tried several things, but cannot seem to figure this one out.
The SQL of my query is as follows:
SELECT IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 1 And 4,[04b Pricing Products]![01-04],IIf(Nz(Sum([Current Orders]![Quantity]))+ Nz([forms]![04c Test Query for ROM Support]![Quantity]) Between 5 And 10,[04b Pricing Products]![05-10], IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity])
Product (Table PDZRN1) Whse (Table PDZRN1) Description (Table PDZRN1) On Hand (Table PDZRN1) Unit Cost (Table PDZRN1) Unit Price (Table PDZRN1) LastDate: Date (Table Sales_History) (using the Max function)
Where I am stuck is I want 4 additional columns to pull the last 3 fiscal years and total sales for each Product for the 3 previous fiscal years. The Field with sales amount is "Sales" it is located in Table "Sales_History", and the dates for the invoices are in the field "Date". I tried the following query, but I'm sure it didn't work because I failed to push the data back to the date field:
Field - FY2013Sales: Sales Table - Sales_History Total - Sum Criteria - <= 04/01/2012 and <= 03/31/2013
how to link my criteria back to the "Date" field, or if I'm even going down the write path.
I want to get the sum of sales of the top 5 best agents by state.
I have been able to do the following, however, this only gives me the state of "vic". I want it to show all states.
SELECT TOP 5 filedata.Debtor, Debtors.FullName, Sum(filedata.NetTotal) AS SumOfNetTotal, Debtors.State, Count(filedata.BorQ) AS CountOfBorQ FROM filedata INNER JOIN Debtors ON filedata.Debtor = Debtors.Code WHERE (((filedata.BorQ)="b") AND ((filedata.DepDate) Between [Forms]![F-financemenu].[startdate] And [Forms]![F-financemenu].[enddate])) GROUP BY filedata.Debtor, Debtors.FullName, Debtors.State HAVING (((Debtors.State)="vic")) ORDER BY Sum(filedata.NetTotal) DESC;
I'm trying to build a query that adds up the total sales for a given product per date. The problem I'm running into is some products have $0 sales on a given day and don't appear in the table I'm querying. How would I get the query to add a record for that day for the given product with 0 in the sales field?
To give more details, I currently have one table showing the sales data. It has a record for every sale that includes the product of the sale, the amount, and the date. In the query, I'm summing the sales for a given day and product. Then I plan to export to Excel where I will run additional analysis.
If there are no sales for the product in a day, I'd like it to list the date and product with a $0 in the sales column.
tblmntlyalloc (Three fields and 12 rows i.e. one row for each month) 2015mnth, 2015allocation, 2015wrkdays jan 98 20 feb 93 19 etc etc etc
So in order to handle seasonality of sales, the sales department is given 1,200 points. you can then allocation any number of points (75 - 125) per month. But the total number of points must equal 1,200. So I created a table with the monthly allocation and workdays. I could hard code the % and work days into the formula like this
And this does work very well to get me my daily sales amount per month/wrkday
However I know sales will change the monthly allocation (still totals 1200) and HR could even have a change to the holiday schedule. So I want to utilize the table.
So my query brings in both tables, but they are not linked. And this is my formula.
I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo. I am about to create a historical table of RRP.
What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.
My new table "tblRRP" Could contain Year int, Week int, CountryCode nvarchar (2), ProductId nvarchar (15), RRP float;
The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.
So that when I do year on year revenue calculations it works properly.
I'm having a problem with queries, and I can't seem to find a solution in books - I looked through about ten of them and none of them addressed the problem. This may be because it has a painfully obvious solution...
A little background: I am designing a database for a debt-collection law firm. One of the functions it must have is to keep track of various different sorts of financial transactions which can pertain to a given debtor (ie, a received payment, a cost expended, and a few other things).
The problem is that, in generating reports, I need to use queries to find several sums of only those transactions which fall into specific categories (for instance, to calculate the amount a debtor has paid against his balance, it needs to sum only those entries which are both linked to that debtor's ID number and whose type field reads "payment", and then subtract from that those entries whose type field reads "cost"). The problem is this: not all debtors may have "costs" entries, and when there are none, the report comes up blank with a single "#Error" written in the name field and nothing else present.
I believe the problem is that the Sum aggregate is returning a null value when the query finds nothing that meets the criteria. I have been unable to find a way around this; the Nz() and IIf() with IsNull() functions don't seem to be helping.
The query runs as intended when there are entries for every relevant type; however, it is undesired to have to enter a "payment" of $0, "cost" of $0 etc for every entry just so that this function works.
Is there anything I can do about this? Any input would be appreciated, as I'm fairly inexperienced with the use of Office Access. (If it matters, I am using Office 2003).
