I have some issues with summarizing in a query. My intention is to summarize income and expenses over periods
This is how it should look like.
Periods_____Total income___Total expenses
01-2013_____234__________435
02-2013_____533__________132
03-2013_____345__________853
etc
I made the following query to get the result I wanted.
Unfortunately, the result seems to be multiplied instead of summarized.
I'm trying to combine/summarize two tables in a query. What I've found so far is that I have to use a union query. I tried that but all I get is punctuation error.
So here is what I'm trying to do:
Code:
tblProdA
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 10
02 | 1/3/2015 | 10
03 | 1/10/2015 | 20
04 | 1/13/2015 | 20
05 | 1/17/2015 | 30
------------------------------------
tblProd2
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 5
02 | 1/5/2015 | 5
03 | 1/10/2015 | 10
04 | 1/13/2015 | 10
05 | 1/15/2015 | 10
06 | 1/17/2015 | 5
------------------------------------
qrySummary
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 15
02 | 1/3/2015 | 10
03 | 1/5/2015 | 5
04 | 1/10/2015 | 30
05 | 1/13/2015 | 10
06 | 1/15/2015 | 10
07 | 1/17/2015 | 35
------------------------------------
And I uploaded the sample as well.
I have a query that is pulling from a single table of expenses the sum of expenses for each quarter. I am using only three fields: the quarter, expense type and amount paid. I am using - Qtr: DatePart("q",[PostDate]) - to obtain the quarters, grouping by quarter and then by expense type. However, the record returned for the 4th quarter shows this result: "3.0026" It should not have four digits after the decimal.
Results for the other three quarters in the same query return correctly with two digits after the decimal point. The query below was suggested to me in order to make the sum returned have only two digits after the decimal:
AmtPaid: Sum((SELECT FORMAT(PymntAMOUNT, 2) FROM tbl_expenses))
The result of this was a curious information box that said: "At most one record can be returned by this subquery." The query returned nothing. I have checked the formatting in both the table and the query itself and the format is "Standard" for both. So, I can't figure out what is producing this 4 digit problem, and why it is only in one record while all the others have two digits after the decimal.
I've got three fields - date_time, # of issues, issue reasons
I want to summarize these by work week.
So,
WW....... # of issues ...............................issue reasons
1 ..........<sum of all issues for the week>..list of all reasons entered
2 ..........<sum of all issues for the week>..list of all reasons entered
3 ..........<sum of all issues for the week>..list of all reasons entered
4 ..........<sum of all issues for the week>..list of all reasons entered
5 ..........<sum of all issues for the week>..list of all reasons entered
I know how to get the WW part - I do the datepart("ww",[Date_Time] for the expression. But how to write the query to do the other 2 parts, I'm lost.
I am trying to find the best way to compare estimated expenses to actual expenses in Access. I am working with vessel and port call information, so each port call has a series of costs associated with it. I have two tables, each with multiple fields (around 100). Each of the fields contains a cost type that goes to a particular cost code. Each row of the table is associated with one particular port call. The call is estimated right after it happens, but the invoices are not all recieved for up to three months. Once all of the invoices for this port call have been recieved, we enter the actual expenses for the call. I am trying to find the best way to analyze the costs through finding the variances for one particular port call for each code, and also for multiple port calls to see which cost item has the largest/smallest variance for further investigation into how to estimate it more accurlately. I am thinking that I may have to move into Excel to do this, but would like to stay in Access if possible.
View 4 Replies View RelatedAccess database to calculate income tax?
I'd like to be able to input the amount I invoice each month into a table and it would calculate Vat and Income tax due.
I'm sure its been done many times before however I cant find any examples.
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?
I am trying to create an access db to monitor my expenses for my new Snack house.
I have created a "Main" table which will be holding all the daily expenses entries one the fields is called "Type" in which i formatted as combo box with the following values (vegetables , butchery , bakery).
Another field is called "Item" which should list the items based on the Type selection.
Knowing i have a second table in which i stored all types and their corresponding items , how can i force the filed "item" in table Main to simply display the ones i need based on the "Type" input.
I have to create a query to calculate the Base Income per room.
Guest 1-4 is the standard room rate say $125/day, guests 5 & 6 pay additional $20 charge per day. Guests staying for seven days or more receive a 10% discount. How to build this expression into a query in access. If it was Excel that would be easier.
Table info I have is:
Room
Arrival Date
Departure Date
No of Guest (per room)
Daily Rate
I am currently using Access 2007 and have created a huge database for our investment managers to calculate the income based on percentages. The percentages are created in Excel. I have uploaded into the database both the June and the July percentages. When I run the query for my report using the date range for June, it works fine. When I request just the July information (entering the date range for July) the June data is doubled on the report.
View 4 Replies View RelatedIf my fiscal year ends 12/31 but the pay period doesn't end until 1/10, how do I get a query to tell me there are 25.3 pay periods remaining (counting from the last pay period 1/11)?
View 3 Replies View RelatedI have a grouped query that returns a list of all periods qryPeriod
Code:
SELECT [tblMaster].Year, [tblMaster].Week, ([Year] & " - " & Right(0 & [Week],2)) AS Period
FROM [tblMaster]
GROUP BY [tblMaster].Year, [tblMaster].Week, ([Year] & " - " & Right(0 & [Week],2));
Year | Week | Period
2013 | 48 | 2013 - 48
2013 | 49 | 2013 - 49
2013 | 50 | 2013 - 50
2013 | 51 | 2013 - 51
2013 | 52 | 2013 - 52
2014 | 1 | 2014 - 01
2014 | 2 | 2014 - 02
2014 | 3 | 2014 - 03
2014 | 4 | 2014 - 04
[code]...
these are then used as the start of a between lookup against [tblMaster] along with the last record in the qryPeriod (2014 - 04)...I will need 4 queries so If someone can sort out last period - 8 I can probably do the others.would DLookUp do this say return the last 4 values, Last 6 Values and Last 8 Values
How to summarize the fields of the table this way? See the attached screen shot that show my question
I wanted to summarize the data according the values of Field1 and to get the values of Field1 and Field2 summarized separating the values with comma. How can i do that?
Hi,
I'm having problems getting my head around the best way to design a table for an employees expenses. This is my current M_Expenses table:
ExpenseID (PK)
PayrateID
ExpenseType
Amount
PayrateID is the foreign key to a table that holds information about their rates, evertime etc.
My idea was that the user would only choose an expense if the employee were entitled to that expense - so if someone was not entitled to flight expenses the record would not be created. However my boss says that if he were to look at the report produced from this he wouldn't know whether the person putting in the data just didn't have the information at the time or it didn't apply to that employee. So I will need to let the users know that they must create a record for each.
Also the report must list all of the expenses:
Per diem expense
Accommodation
Car hire
Flight
Other expenses
They need to be listed in this order and must appear whether that particular employee is entitled to them or not. I don't see how I can do this as it is because my subreport that lists the expenses is a continuous form and they are not listed in alphabetical order. Plus only expenses that the user has chosen will appear.
The only other way I can see to do it is redesign my table as follows:
ExpenseID
PerDiem
Accommodation
CarHire
Flights
OtherExpenseType
OtherExpenseAmount
But that doesn't seem like good design?
So, I've recently learned that Access 2013 took a step backward and got rid of PivotTables. Unfortunately for me, PivotTables were perfect for summary reports I have to create with large data sets (some with millions of records).
What are my alternatives to PivotTables within Access 2013? I was playing around with the report builder, but it seems to retain all line items and doesn't allow me to collapse everything into a one page report.
I am trying to calculate variable income tax but can't seem to get it to work.
I have a query called payslips which calculates gross income based on hrs worked and payrate. I then have a separate tax table called TaxThreshWitheld which states the earnings and the tax witheld. I am trying to create a query that selects the tax witheld when earning from the taxwitheld table equals the gross field from the payslip query.
The two tables aren't linked and I am not sure how to link them. I tried to link "earnings" and "gross" but that causes the query not to run...
:confused: I wish to construct a form that displays a graph showing the monthly sales totals year on year. In this way - I will be able to see quite easily how the present monthly income compares to previous years.
Therefore, each year will have its own data series on the graph... the problem I have is that I wish the graph to automatically recognise the roll-over to the next year thereby creating the next series for that year.
This would mean having the months Jan-Dec on the X axis and the income scale as the Y axis. Each data series representing the year.
Otherwise, the alternative is to manually change the underlying datasource every year to include .... has anyone done anything similar to this that they would be willing to share please?
Thank you.
Guido
HI All
I have a question that I just cant seem to get my head around....so I thought I'd pick your brains!
I have a table that has DriverID, Date, Work, Sleep, Rest. As you can imagine, this records their hours of work, sleep & rest.
Now with new fatigue management laws in Oz, we need to find out if they're working for more than 168 hours in a 14 day period.....
My thoughts are, create a query that runs through every date of the year, then counts forward 2 weeks, sums the WORK hours and only displays anything over 168 hours??? Is this correct? Would that not simply die ? Considering we have about 30 drivers to run through?
I have read more post than i care to think about but I can't stil get my head round the following problem.
I have created a database (with this forums help) but I am stuck on the following query/form/vba etc....
Our staff holidays run from 01/05/05 until 30/04/06 and i have a query that calculates what holiday entitlement staff are due from 01/05/05 on a daily basis up to 23 days worth of hols ending on 30/04/06, but I can't work out how to set next years holiday period 01/05/06 - 30/04/07 without changing the form manually, is there a way for it to auto update??
I am tyrying to get my query to analyse the last three months of data but typing the following in the criteria section under Potting Date
Date()-90
An error is occuring.
Can anybody assist with this problem, I think I am writing the wrong thing
Regards Karen
Hi all!
I want to add a function to a form that filters records from a specific date period.
Each record has a field containing the date the record was added.
Example: I want to show the records added between 15.09.2006 and 10.10.2006.
Furthermore, the “from” and “to date” must be defined in textboxes, and the filter initiated with a button.
I need this functionality on a continuous form and in a report. How do I achieve this?
I have several pieces of equipment that register data every minute, is there a way to do like a 5 minute average
i.e., 00:00 - 00:04, 05:00 - 09:00
NOT 00:00- 05:00, 01:00- 06:00 and so on.
We have a db of salmon survey data where catch data is recorded by date, site, etc.
Historically, we have used ‘sampling periods’ to group our results into blocks of time that can be compared from year to year, and within year.
Now, having frequented this forum I’ve read that we shouldn’t store information in tables that can be calculated from fields and I want to try to follow that advice in this instance but am having trouble working this out.
Here’s how we define a sampling period.
1.The start date of the first sampling period in a sampling year is the last Monday in the most recent November.
2.Each sampling period is 14 days long.
What I want to do is have a query use the date of a set to calculate what sampling period the set belongs to. I have an idea that I could use a datediff function to calculate this if I can figure out the ‘last Monday in the previous November’ part. I’ve searched this forum and googled but am having trouble finding anything that I can adapt to this as a query expression.
Anyone care to help a fish bio out? Much obliged :)
I am new to Access and would be very grateful for some adivce on setting up a query.
The data in the table below comes from a photon counter. Both fields are numbers although TIME actually represents seconds.
I need to be able to group the DATA field by arbitrary TIME periods. e.g. to divide the table up into bins of, say, 8.7 seconds length, showing the total accumulated in the DATA field for each successive period of 8.7 seconds.
The tables are about 300,000 records long.
Any help would be much appreciated.
Thanks,
TIME - DATA
285748175.864557 - 100
285748175.874556 - 100
285748175.884556 - 0
285748175.894557 - 100
285748175.904557 - 0
285748175.914556 - 200
285748175.924556 - 300
285748175.934556 - 0
Hello,
I'm having a hard time trying to figure out how I should go about designing the table structure for the hours tracking for the database I'm working on. Basically, I have a table with projects (tblProj) that I need to track the number of labor hours worked per month. The problem lies in the fact that each project could have varying time periods, anywhere from less than a year to more than a year and can start and end at any time of the year.
I was thinking of creating a second table (tblProjHours) to store the hours for the project but I'm not sure what the most efficient way of doing this would be. My idea was to create a table as follows:
tblProjHours
ProjHoursID (pk)
ProjID (fk)
BeginDate
EndDate
Month1
Month2
Month3, etc....
I would create a large number of fields/columns, something like Month1 to Month36 just to make sure I have enough months to enter in the hours. This is of course inefficient since some projects would be way shorter than the maximum allowed months set by the table structure and there is always the possibility of surpassing the maximum allowed months based on the table structure. I was wondering if someone had any better ideas on how I should pursue the design. Thanks in advance!
TheMach
I'm having trouble finding an entry anywhere about this, although I'm sure I'm not the first person to come across it...
When I am typing my entry into a combobox it begins to autosearch, highlighting the part of the entry that it finds that matches the beginning that I typed in (as a normal combobox does in access).
However, when the entry it finds has multiple periods in it (i.e.: S.T.A.R) it stops searching and does not highlight the remainder of the entry it moved to. I'm not sure that searching is the correct term, which may be why I can't find any information on this, but is there a way to correct this problem?