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
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.
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).
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).
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..
I need to generate a report with records generated on a daily basis and I am looking how to write the query expression. For example, my Access database generates record on a daily basis and I an using a query to generate the report. I only want to report the records created on that day, and then each subsequent day. So, in the query, I am looking for the correct expression to grab only those records generated that day. (Hope its not confusing, I'm new at this)
Thanks for any help in advance!
Keith
I have a production application in which i have a table named daily_production with fields as ( prod_id, date, productname, qty ). Now I want a daily production query as
date : xx-xx-xxxx
productname | qty | monthlysum(for thsi product) | daily average |
I want this on a single query so that i can make a report out of this .
I am trying to create a query that will provide a field for each day of a month. However, I want the query to be able to work for any month that I want to run on based on a parameter. Basically I want this:
Day 1: Sum(IIF([ReleaseDate]=#[# of Month]/1/[# of Year]#,[GamesSold],0)
Day 2: Sum(IIF([ReleaseDate]=#[# of Month]/2/[# of Year]#,[GamesSold],0)
and so on for 31 fields.
This is not currently working.
I don't use Access too often but I'm trying to connect a table to some business intelligence software I use.
A. Date B. Price C.Ydayprice
01/01/2015 101.45
02/01/2015 104.70
03/01/2015 103.00
Simple stuff. Once I've connected to the table, I can easily do what I want with Field 'Price' and 'Ydayprice'. However, I can't calculate column C. All I want is row 2 to say 101.45, R3 to say 104.7 etc. I've tried various things but I don't have the knowledge to write the action I require. I don't want to calculate the change in Access either.
I have a query that reports the number of students that are currently enrolled in a given number of schools within an area for that given day that the query is ran. This number can fluctuate greatly day to day, so is there a way to create a table which can hold this information so that I can go back in history and look up what our numbers were on whatever given day (date). (The example below is just a representation of what I actually have.) The School number would be unique, as would the date (I assume), I would like to see all of our school on whatever given date to see what our numbers are or were.
I have an example of the query currently I have set up in access 2007:
School # School Name Qty Date
001 BLUE 300 02/26/2014
002 RED 468 02/26/2014
003 YELLOW 10 02/26/2014
VS
School # School Name Qty Date
001 BLUE 310 02/27/2014
002 RED 400 02/27/2014
003 YELLOW 8 02/27/2014
How to build query to give daily balance across bank accounts? (to then plot in a graph)
Assumptions:
* There is a table TRANSACTIONS which includes columns TRANS_DATE, AMOUNT and BANK_ID. It does NOT include a column for balance. So current balance for a bank account is the sum of the AMOUNTs for that BANK_ID for example. Balance on date XX will be the sum of all AMOUNTS for that BANK_ID for all TRANS_DATE's prior and including the date XX.
* Table BANKS which has BANK_ID and TITLE
Would like a query that gives: Supply StartDate and EndDate for the query:
Date Bank1Balance Bank2Balance Bank3Balance TotalBalance
1/1/15 $100 $200 $100 $400
1/2/15 $200 $200 $100 $500
.
.
.
etc
O.K, I am really trying to figure this out from other postings but my limited query experience is limting my understanding of the other posts.
I only know how to use the query wiz and then a bit in design mode.
I have a Query
[ID]pk [Contest Name], [Score]
There will be many records for the same [Contest Name] in the underlying table. Therefore i want to sub total by [Contest Name] so i can then create a report. I have created the report perfectly using the Report support in another posting submitted. But the report does not allow me to order the results as the sum calc is a function on the report. Therefore I am now exploring the Query Sum [Score] order by [Contest Name].
I just need it in a Qery for dummies format.
Go into design mode and put the following in what portion of the query on what line.
I will keep on searching in the mean time
I have a query which returns charge_cost (based on course cost, whether it went ahead, if hospitals are eligible for charging etc) which is then used in an existing report.
I want to make another report which simply is:
Total training spend for 2004-2005:
£1276.04
And i just want that to be the SUM of charge_cost.
I cant work out how to do this - i did a new query including charge_cost and then created a new field called total_spend: sum([charge_cost]) but i keep getting the message "You tried to execute a query which does not include the specified expression charge_cost as part of an aggregate function"
I have no idea what this means...
hmmm... found another little problem. I've got a list of ingredients with cost and amount from a table, total cost per ingredient is simply amount times cost, how would I go about getting a recipe total by summing the seperate ingredient totals?
View 2 Replies View Relatedis there a way to calculate/store total cost value of qty x unitcost in total cost field of same table. if it requires a query, would appreciate guding me how to write it.;) ;)
View 4 Replies View RelatedHello all,
I again ran into something that I can't figure out.
I have a table:
Date
Time
FirstName
LastName
SSN
InAmount
OutAmount
I need a query to sum up the InAmount and OutAmount into one total based on the SSN. This query is placed into a form that is then placed onto another form. The form is to alert the user if the amount of the Inamount and Outamount of a unique SSN totals above $10,000.01 on the current date.
So for example if on 01/01/07 if SSN=111-11-1111 has an Inamount of $5,000.00 and an OutAmount of $5,000.01 thus totaling $10,000.01, then the person's name will appear on the form list. This will change/clear when the date is 01/02/07.
Any Ideas on how to accomplish this?
Hi,
I am trying to write a query that will total the number of fields that have matching values. For example i need to have IP addresses added into the table via a form, that bit is done but i need to create a query that will count how many times an individual IP address is added to the list.
So that on the report i can show the list of IP address and instead of showing duplicates it will show how many times it has been added to the table.
Any help on how i could do this would be greatly appreciated.
Cheers
Luke
Does anyon ehave any experience of running totals in an access query.
I'm reporting the data through excel not access reports so need a query not a report solution..
I have a table which looks:
RegionCategoryTypeDesc Period_IDPeriod_YTDPeriodTotal
CanadaEventsWSOP Team67Budget15000
CanadaEventsWSOP Team78Budget0
CanadaEventsWSOP Team89Budget0
CanadaEventsWSOP Team910Budget0
CanadaEventsWSOP Team1011Budget0
CanadaEventsWSOP Team1112Budget0
CanadaEventsWSOP Team1213Budget0
CanadaEventsTOTALAll12Budget15000
CanadaEventsTOTALAll23Budget15000
CanadaEventsTOTALAll34Budget15000
CanadaEventsTOTALAll45Budget15000
CanadaEventsTOTALAll56Budget15000
What I would like is to have an additional column which keeps a monthly summary of spend based on running total month 1to 12. All items have months 1 - 12 and are ordered in that fashion.
Any helpo really appreciated.
Simon
Please look at the attached sample db file and the text file explaining the problem.
Thanks
I quess it should be simple. But I couldn't find an answer or example in Access books and online.
I have an access 2000 database with a Accounts Receivable table. I am building a query that returns accounts what are 60 days past due and calculating total ballance of ONLY past 60 days accounts. I managed to write a code to display 60 days past due acounts, but when I use UNION query, it calculates total from entire Accounts Receivable table, but I need only total ballance of past due accounts.
Here is my SQL code
SELECT [Accounts Receivable].InvoiceID, [Accounts Receivable].[Patient Last Name], [Accounts Receivable].[Patient#], [Accounts Receivable].InvoiceDate, [Accounts Receivable].PaymentAmount
FROM [Accounts Receivable]
WHERE ((([Accounts Receivable].InvoiceDate)<Date()-60))
UNION SELECT 'TOTAL', "","","",Sum([Accounts Receivable].PaymentAmount)
FROM [Accounts Receivable];
I need my query to look like this.
InvoiceID Patient Last Name Invoice Date Payment Amount
23 Smith 05/01/2007 $100
25 Doe 04/03/2007 $200
Total - - $300
Please help.
Hi,
Am trying to create a query for a chart where I can total the employees over time but am having real trouble creating a running total from the "Total" field within a query but cannot seem to get it at all.
TotalStartDateLeftDate RunningTotal
126/03/1957
121/03/1971
127/02/1986
115/02/1988
207/03/1988
007/03/198831/05/2007
Here is my current SQL query:
SELECT Sum([CountOfStartDate]-[CountOfLeftDate]) AS Total, Atest1.StartDate, Atest1.LeftDate, Sum([CountOfStartDate]-[CountOfLeftDate]) AS RunningTotal
FROM Atest1
GROUP BY Atest1.StartDate, Atest1.LeftDate;
Can anyone help please?
This should be easy! Right?
I have a series of dates with events that occured on those dates. Some events were extended, others were not how do I get a running total, cumulative total, for all records in the RunTotal column?
Opened DateOpen IssuesCountOfExtendedNotExtRunTotal
5/21/2007 1 10 1
8/6/2007 1 10 2
10/8/2007 1 10 3
11/1/2007 1 10 4
11/8/2007 1 01 5
12/5/2007 1 0 1 6
Thanks for your help.
is it possible to have a query carry out a running total, so that it counts the record above and the current entry.....
example
Record 1 Total 10 Running total 10
Record 2 Total 10 Running total 20
Record 3 Total 30 Running total 50
Record 4 Total 5 Running total 55
as i said i need this done in a query if Possible...
anyone help?
I have a database of song track data with track length as a field.
I want to produce play lists up to specific lengths e.g 15 minutes and just want the query to show me enough songs to fill up this time period.
Any help appreciated - especially simple solutions!
Jim
I am doing an assignment where i am required to
Quote: "Find the total price (SUM) of all stock items in the database (use total query and find the SUM of the [price of stock]*[quantity on hand]"
Ive tried several times to do this, each time unsucessfully because im unsure how to go about it. i can get the sum of those two things, but i cant multiply the two.
Thanks in advance for the help
I have a query that is filtering records from a table, I have used the Totals row in the query to Group By and provide Count of totals. The datasheet view of the query has the total row and is working fine. I would like to display the total row on a report, using the query as the record source.
It does not seem to be available, so I put a text box in the footer of the report with the Control Source set to: =Sum([CountOfPermit_Type]), but that only returns an error.