Total Daily Sales Queries By Model/Total

Mar 8, 2008

Hi,

1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.

2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)

3) I start with daily (Lets don't be too overly ambitious).

4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.

5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?

PS: Please forgive my ignorance :o:(

Thanks (In advance) & God Bless.

View Replies


ADVERTISEMENT

Queries :: Daily Activities - Group As Weekly Total And Transpose For Reporting

Jun 5, 2013

Query of daily activities spent hours

1) to be group as weekly total
2) then need to transpose it for reporting.

My table fields are Date, SpentHrs and Code (activity code description).

View 11 Replies View Related

Queries :: Time Sheet / Payroll Database - Calculating Total Daily Hours

Feb 18, 2014

Access Query. I am creating a time sheet / pay roll database and I want to be able to get a total of the daily hours in a query.

For example I have 'Mon Start' and 'Mon Finish' for Mondays in/out times and I have a 'Mon Total' which gives me the total hours worked for Monday.

The problem I have is that Mon Total only works if the hours are say between 07:00 and 17:00, anything after midnight (00:00) like 21:00 to 07:00 and 'Mon Total' goes crazy !!

At the moment 'Mon Total' is the result of CDate 'Mon Finish' - 'Mon Start' (bit rough I know).

View 3 Replies View Related

Queries :: Self-Referencing Running Total Used To Calculate Next Total In A Query

Jul 23, 2015

I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records

I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do

If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..

View 9 Replies View Related

Daily Total Query

Sep 7, 2006

Hi,

I have a database, in which I need to add up the total number of entries made at any point of the day. I have started by creating a query with the entry's SerialID (unique) and the date it was entered, however I am stuck already.

Any help would be appreciated,

Ben

View 12 Replies View Related

Sales Query Manufacture By Month - Total Column In Design View

Jan 2, 2014

I'm trying to create a simple sales query by manufacture by month. I want the month as the column headers (with a total YTD Column). Mfg's are the row headers. The query is already completed and I figured out how to do the row total but can't figure out how to create total column. Can I not add a total column in design view? How would I do that?

View 10 Replies View Related

Modules & VBA :: Chart To Show Total Sales Ordered By Customers - Graph Method?

Sep 10, 2013

I've created an access chart to show total sales ordered by customers.

I'm using a form with 5 comboboxes to select 5 particular customers from a customer table and pass this information into a query.

This query is then used on a report to create the graph.

Finally there is a button on my form that prints this report.

My problem is that the chart displays the customers in alphabetical order and I would like to order the customers numerically based on total sales value.

If I change my query so that the total sales order by is ascending then when I run the query from the report I am asked to enter a parameter value for the total sales field. Entering nothing and pressing ok simply returns the same graph I would have got had I not changed the order option at all.

*The customer filed in the report is set to group by and my totals field is set to sum - but I need both of these set to produce the graph.

View 3 Replies View Related

Tables :: Get Total Of Clip Size And Also Total Of Clip Duration

Dec 31, 2012

I have a table with 4 field which describe clips.

ClipName (txt)
ClipSize ( Long Integer)
ClipDuration (HH:MM:SS)
Date created (dd/mm/yy).

I would like to get the total of ClipSize and also total of Clip Duration either in table down or in query.

View 13 Replies View Related

Queries :: Sum Overall Total Of Count

Aug 27, 2014

I have a query that is counting perfectly like I want, but I need to sum the overall total of that count. How do I sum a count in SQL?

View 3 Replies View Related

Queries :: Using Total Of Field In Expression?

Oct 7, 2013

I have a Table which has a column that contains a Data (last day of the month: 8/31/2013, 9/30/2013) and also contains a Balance field.

What I am trying to accomplish is a query that will provide me with the total for the last month (Sept), and then also provide me with a Percent break down from a whole balance.

I have the following SQL for it:

SELECT ATB.FC, Sum(ATB.Bal) AS SumOfBal, (select sum([Bal]) from ATB) AS Total, Sum([Bal]/[Total]) AS [Percent]
FROM ATB
GROUP BY ATB.FC, ATB.MMDDYY
HAVING (((ATB.MMDDYY)=Date()-Day(Date())));

Right now, when I run the above, the Sum(ATB.Bal) pulls just the month of September, but the Percent is pulling a percentage of each Sept. Value against the whole Balance for the Year. So, when I sum the percent column to get a total of 100% for the month of September, I am ending up with a number less than 20%.

View 1 Replies View Related

Queries :: Total By Each Month Of The Year

Feb 6, 2014

I have a query that I need to total by each month of the year. Field names and types are as follows

ID (PK)
Month (Date/Time)
PoliceResponse (1 or 0)
TerminalCheckTrue (1 or 0)
RampCheckTrue (1 or 0)
AOACheckTrue (1 or 0)
TotalTerminalRampAOA (TerminalCheckTrue + RampCheckTrue + AOACheckTrue)
TotalChecks (PoliceResponse+TerminalCheckTrue + RampCheckTrue + AOACheckTrue)

Now, I need to just get the total of each of these fields per month for the entire year. So it would look like

Jan, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Feb, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Mar, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...

Below is a sample from the query. I guess Im just not sure where to start since there are multiple months the same value, but i need them to total up..

Code:
SELECT DISTINCTROW tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy') AS [Month], Sum(IIf([CKPoliceResponse],1,0)) AS PoliceResponseTrue, Sum(IIf([CKTerminalCheck],1,0)) AS TerminalCheckTrue, Sum(IIf([CKRampCheck],1,0)) AS RampCheckTrue, Sum(IIf([CKAOACheck],1,0)) AS AOACheckTrue, ([TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalTerminalRampAOA, ([PoliceResponseTrue]+[TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalChecks
FROM tblBlotter
GROUP BY tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy'), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1;

View 14 Replies View Related

Queries :: Put A Value From Total Query Into Variable

Nov 21, 2014

I have a query like this

SELECT qryBONUS_pre.PACIENT_ID, Sum(qryBONUS_pred.TOTAL) AS SumOfTOTAL
FROM qryBONUS_pre.TOTAL
GROUP BY qryBONUS_pre.PACIENT_ID;

I like SumOfTOTAL to put into a variable

How to do it

View 2 Replies View Related

Queries :: Total Max Working - How To Get Other Data

Dec 11, 2014

I know a fair bit about access 2010. I have the following table

------------------------------------------------------------------------------
- product no ----- Branch ID --- POS Description --last sold date --
------------------------------------------------------------------------------
- 123 ------- 1 ----- fly rod ---- 19/01/2014 ----
- 123 ------- 2 ----- fly rod ---- 19/03/2014 ----
- 123 ------- 3 ----- fly rod ---- 19/05/2014 ----
- 123 ------- 4 ----- fly rod ---- 19/06/2014 ----
- 123 ------- 5 ----- fly rod ---- 19/09/2014 ----
- 633 ------- 1 ----- fly reel ---- 11/01/2014 ----
- 633 ------- 2 ----- fly reel ---- 11/03/2014 ----
- 633 ------- 3 ----- fly reel ---- 11/05/2014 ----
- 633 ------- 4 ----- fly reel ---- 11/06/2014 ----
- 633 ------- 5 ----- fly reel ---- 11/09/2014 ----
------------------------------------------------------------------------------

I need it to give me back, one row per product (product no) - the newest date (Date Last Sold)

So i am trying to find out the last time each product sold, regardless of where

Which i can do with MAX date, group by product num

SELECT SMKPLUDetail.[Product No], Last(SMKPLUDetail.[Branch ID]) AS [LastOfBranch ID], Last(SMKPLUDetail.[POS Description]) AS [LastOfPOS Description], Max(SMKPLUDetail.[Date Last Sold]) AS [MaxOfDate Last Sold]
FROM SMKPLUDetail
GROUP BY SMKPLUDetail.[Product No];

------------------------------------------------------------------------------
- product no ----- Branch ID --- POS Description --last sold date --
------------------------------------------------------------------------------
- 123 ------- 5 ----- fly rod ---- 19/09/2014 ----
- 633 ------- 5 ----- fly reel ---- 11/09/2014 ----
------------------------------------------------------------------------------

This is great, but ofcourse (last of is fine for description) as is does not change

But last of, always returns branch 11.. and i infact want the branch which is correct, the branch which the date last sold is from, the current record really

but i do not seem to be able to get it to show me, any other fields from the querry current row.

View 3 Replies View Related

Queries :: Total Excluding Duplicates?

Aug 30, 2013

I have a table with the following fields:

Customer
Month
Qty
Unit Price
Extended Price

I want to know, by month, what the total extended price is for all customers. But some customers appear more than once in a month. So I also want to know the total extended price for the month, but I only want to include the extended price for each customer once, using only the record with the highest unit price (or extended price, qty for each customer is consistent), and ignoring additional records with the same customer in that month.

I am an excel user, and I could quickly do this with pivot tables... but I have too many records for Excel to handle this efficiently (it just keeps crashing!), so I thought I'd move to access... but I don't know how to use access! So I may need a little hand-holding here.

View 10 Replies View Related

Queries :: Show Sum Of Total Hours

Nov 4, 2014

I am trying to build a query to show the total sum of hours a staff member has done in a certain department and the amount of sleep ins they have done. Each staff member has a unique ID. In the query I can get all the information but it shows on two lines dependent on department. I want this to show on a single line. Each employee will never have more than two departments. The query is called "QryForForum".

View 2 Replies View Related

Queries :: Running Total Field In Query

May 20, 2015

I have a table with dates in field1 and an amount of seconds in field2.

field1 field2
01/01/2015, 1345
02/01/2015, -132
04/01/2015, 259

I would like to produce a query that performs a running total in the third column like so:

field1 field2 field3
01/01/2015, 1345, 1345
02/01/2015, -132, 1213
04/01/2015, 259, 1472

This is quite simple to achieve in Excel. (eg =SUM($B$1:B3))

What is the query formula for Access?

View 1 Replies View Related

Queries :: Count Total Number Of Records

Dec 18, 2013

I am wanting to display in a text box or on my report the total number or records in my database. Also I have some buttons that filter the results a little, I'm wanting to display the number after I hit the button(s). Would I just add the query to the end of each button I have?

View 6 Replies View Related

Queries :: Combine And Total Query Results

Aug 8, 2013

The results of my union query of 2 separate tables looks like:

Name - Count of Participations

Joe Blow - 7
Harry Smith - 11
Kate Upshaw - 8
Joe Blow - 3
Harry Smith - 5
Kate Upshaw - 13

I need to combine the duplicate names and total their participations to make a report look like:

Name - Count of Total Participations

Joe Blow - 10
Harry Smith - 16
Kate Upshaw - 21

View 3 Replies View Related

Queries :: Adding Another Field To SUM Running Total

Jun 24, 2013

I have qry with these fields: DateOfPayment and Ammount.

I would like to add another field with running total sum. I am trying this:

RunnTot: Format(DSum("[Ammount]";"qryCFSUM"; [DateOfPayment] <=#" & [DateOfPayment] & "#" );"0 000"" Kč""").

But It still does not work.

Example of my data in "qryCFSUM":

DateOfPayment
20.1.2013
31.1.2013
30.3.2013

Amount
1 2000 Kč
15 456 Kč
23 465 Kč

And what I would like to have:

RunnTot
1 200 Kč
16 656 Kč
40 121 Kč

View 14 Replies View Related

Queries :: Showing Running Total For Each Month?

Oct 12, 2013

I have a list of products that have a loan payment associated to them. To cover these loans, we have incoming revenue for each product at different dates.

The incoming revenue is a field of running sum of revenue for each product.

Desired output:

I want to how how much % of loan (jn total; for all products) is paid in October, november and december and as such (as cumulative). i.e. total of 40% in Oct, 70% in Nov and 100% by Dec etc.

I am attaching the database, with sample data.

View 1 Replies View Related

Queries :: Getting A Total For Specific Information In A Field

Jul 29, 2013

I have a DB to keep track of my employees. I have one table with named EmployeeT with the basic information like name, address, phone number, etc. And another table named EmployeeHistoryT which tell me about there hisorty in the company, basically, If they were Late, did not show up, cancelled from that day. They share a relationship threw the employee ID. IN the history table, I have DateofAction:, TypeOfAction:, and Description:. In the type of action area I have it set to were you type in what they did, wether it be Late, Did not Show up, Cancelled, etc. I want to be able to get a total about of Late, Cancels, etc. I want to know that John Smith was Late 16 times, cancelled 10 times, and Did not show up 2 times. Is this possible with only one feild.

View 6 Replies View Related

Queries :: Running Total In Calculated Field

Oct 5, 2013

I want to calculate running total and find out the date when that total is greater than a number.

My initial plan was to use Dsum and then use dlookup to find when that Dsum value > [Fixednum].

But when I try Dsum and use Totals in query, access shuts down. maybe because of 15000 rows.

I have attached a sample database that shows what Im working with and what I would like.

View 2 Replies View Related

Queries :: Query To Total Based On Value Of A Field?

Jul 31, 2013

the relevant fields look something like this:

TellerNum - Date - Field1 - Field2 - Field2

I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:

TellerNum 1: 486
TellerNum 2: 300
TellerNum 3: 240
etc..

View 3 Replies View Related

Queries :: How To Make Total Columns For ONE Field

Jul 2, 2015

I am trying to create a query that ONLY shows me the total count of each combo box choice for one field. It sounds very easy and I'm sure it is but everything I try tells me I am using the wrong syntax, or requires me to have rows when I only want column totals.

I have a combo box with a dropdown of 10 common places that people travel to. I want the report to ONLY show the total number of people who chose place 1, place 2, place 3... etc. But I always have to do according to date or time or something. I want it to look like this

Place 1 Place 2 Place 3 Place 4
13............36.........3........77

So essentially there would only be one row. But access will not let me. Is there something I'm doing wrong? Or is this not possible?

View 8 Replies View Related

Queries :: Calculate Value From Previous Day To Sum To Next Day Total Income?

Dec 2, 2014

I have this columns :

Income | Outcome | Transport | Total Income | Date
Total Income (N) = Income (N) + Transport (N-1)
Total income equals income from that day + transport from previous day

Transport (N) = Total Income (N) - Outcome (N)
Transport from previous day equals Total Income minus outcome

How can I calculate the transport from previous day to sum to next day total income?

View 1 Replies View Related

Queries :: Single Total Of Parts Between Range

Jan 8, 2014

I am running a query to some me total parts made between 2 dates. The dates are selected from a form with the query looking to this form to selected the date range.

When I run the query it shows me total parts made for each day. What i would like is a single total of parts between the range.

I assume I could achieve this by a 2nd query...

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved