Help With Developing A Calculated Field
Nov 3, 2007
It's a Bed and Breakfast. There are two Access Tables in the query. No
spreadsheet. The Reservations table has RoomNumbers (some of which repeat because different customers rent the room during different time periods), CustomerNumbers, Check-inDate and Check-out Dates. The Rooms table has RoomNumber, FloorNumber, PricePerNight fields. The calculated field that I have developed to figure the money generated by each room is Total:([Rooms].[PricePerNight])*([Reservation].[Check-outDate]-[Reservations].[Check-inDate]).
This makes my result look like:
Room Number Total
3 $960.00
7 $540.00
3 $480.00
1 $300.00
4 $280.00
4 $280.00
3 $240.00
2 $225.00
2 $150.00
4 $140.00
1 $ 75.00
6 $ 60.00
How do I use a Summation function to aggregate the amount of money brought in for each room, so that the result looks like
Room Number Total
3 $1,680.00
4 $700.00
7 $540.00
2 $375.00
1 $375.00
6 $60.00
Any help would be appreciated.
Thank you
View Replies
ADVERTISEMENT
Dec 20, 2007
HI,
I've been banging my head on this, and I just can't seem to find a good way to create my tables.
They want a selection with a sub selection, and then allow the user to fill in data depending on that data. I'm not sure if I should make a new table to hold the data for each sub selection? Or should I make 1 larger table with columns for all the sub selection data fields, even tho they won't all get filled in for each record.
Option #1
Option #1 sub choice
Sub Choice 1 Sub choice 1 fieldsDates, field 2, field 3
Sub Choice 2Sub Choice 2 fieldsDates, Sub Choice 2 fields
Option #2
and same structure as before, just different options.
this is so hard to explain, not even sure I should post this.
View 14 Replies
View Related
Aug 18, 2004
Background:
I currently work for a large healthcare system as a Nurse Recruiter. We have had the need, fro quitre some time, to have the ability to track all interviews. I have recently developed a database that will allow us to do this and much more but have run into a few snags. I hope someone will be able to help.
Problem:
I would like to create a "pop up" query on one of my forms. I would like to have the ability to type in a date in the pop up box and and thus generate a report that allows me to see all interviews scheduled for that day/date. I have tried several things but cannot seem to get anything to work. I cannot even get the "pop up" box to work corrctly.....any suggestions?
The other task that I am trying to accomplish is not to allow people to schedule interviews at the same hospital for the same time on the same day/date. Ideally I would like an error message to pop up if this occurs. Any suggestions on how to build this query?
My DataBase:
It is a very simple database utilizing only one table, a few queries, a few macros, and 4 forms. I have attached a picture of my table so that yuo may view all available fields.
As you can tell i am relatively new to this but am very anxious to learn as much as I can so that I may improve! Any help is very much appreciated!
View 4 Replies
View Related
Jun 29, 2006
We are a SME with a 4 year old access database which is in need of updating with new tabs and various other new spec. We are struggling to find an access programmer who is right for this project.
Does anyone know someone who could have a look at this project??
We are in the Greater Manchester area at present but will be rolling out on a regional and then national scale with this in the next 12-18 months
View 1 Replies
View Related
Aug 8, 2007
I am working on developing a time table for my mothers day care to include payments due, total payments for the tax year and a few other items. The issue I am having is developing the table to track when the children are present. I would like to use the built in calander function in which I can click on the days of the week the children are present and store this into a table as a yes no for each day. Once the data is stored in a table, then I can convert this information into a billing cycle and then go from there.
Thanks in advance,
Mike
View 1 Replies
View Related
Apr 12, 2006
My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
:confused:
View 1 Replies
View Related
Oct 8, 2004
Hi -- my first time here...
Has anyone ever had to develop a survey (e.g., customer satisfaction survey) in Access? Does anyone have any advice?
View 1 Replies
View Related
Jun 18, 2014
Table1 contain Two fields (3Months) and (6Months)
Table2 contain Two fields (3Months) and (6Months)
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
View 1 Replies
View Related
Jul 23, 2015
I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:
10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.
View 3 Replies
View Related
Jun 10, 2015
I use access 2010.
I am assuming by the error code, one can not use a calculated field to calculate another field.
View 1 Replies
View Related
Jan 14, 2013
I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
This is a skeleton view of what I'm going for:
Master Table:
tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor
Sub Table:
tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes
Sub table to Sub Table
tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes
Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.
View 1 Replies
View Related
Feb 9, 2006
Hi,
I have a calculated field in a query that concatinates an order number prefix and the part number to give the order number:
OrderNum: [OrderPrefix] & [PartNum]
There are 2 tables:
tblPartsList
PartNum
PartDesc
tblPartsListCategories
OrderPrefix
OrderNumCategory
This works fine except for one of the categories. For the third category there are no entries in the tblPartsList - because they are miscellaneous.
How do I change the calculation so that it works as above where there are corresponding Part numbers in tblPartsList and otherwise gives me the order number and any 2 digits e.g. something like [OrderPrefix]*
Having been trying to do this for ages but having trouble with syntax.
View 10 Replies
View Related
Apr 20, 2006
hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
View 2 Replies
View Related
Aug 11, 2005
I am new to Access so please be gentle.
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
Mant Thanks
View 3 Replies
View Related
Aug 11, 2005
I am new to Access so please be gentle.
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
Many Thanks
View 1 Replies
View Related
Aug 11, 2005
I am new to Access so please be gentle.
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
Many Thanks
View 12 Replies
View Related
Apr 2, 2006
I have a table with customers, one with products and one transaction table. How can i produce a report, based on a query that will add up the total any one particular customer has spent to date from the transaction table?
View 1 Replies
View Related
Apr 20, 2006
hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
View 1 Replies
View Related
Oct 26, 2006
i have opened a field (which was created in a table) in a form as a calculated fieled
the calculations are ok but only in the for
the same field in the table remainded empty
how can it be set so the table's field will get the clcaulation ?
** here is a sample file
View 2 Replies
View Related
Oct 15, 2007
I have this query which selects two fields and calculates it in another field.
But the calculated field has more than 1 expression.Eg
If field A < Field B,Field B + Field C AS X,If field A < Field C,Field A + Field C AS Y.The problem is that when I show this on the Form I want only 1 of the fields to be shown ie either X or Y .if any of the expressions is satisfied then only that field should be shown as a textbox control on the form and not both.How do I do this?? Its really complicated for me..Pls help..
View 14 Replies
View Related
Dec 5, 2006
Hello,
Can someone please help with this expression?
I have a table with text field Cust_ID and a
Number field MCounter.
I would like to put in an expression as source in a form
Text field based on the same table to AGGREGATE
all MCounter values for a Given Cust_ID
Something like
Sum(MCounter) where "Cust_ID"=[Cust_ID]
Is this a correct expression?
Many thanks for any help.
View 3 Replies
View Related
Feb 22, 2007
This seems a daft question but I have searched the forums and can't find an answer so I hope someone can help me.
I have a table: Register(RegID, Pupil, Activity, Duration, Preparation)
I have a query that I want to calculate the total hours the pupil has spent on activities.
The Duration and Preparation fileds are held as number of minutes so for a 1 hour event the number 60 is held.
I have a query with 4 fields:
Pupil
SumofDuration
SumofPreparation
TotalHours = ([SumofDuration]+[SumofPreparation])60
The problem I have is that TotalHours is calculating as an whole number.
e.g. I have a Record
Pupil = 701
SumofDuration = 270
SumofPreparation = 0
TotalHours = 4
But I want TotalHours = 4.5
I have set the Duration and Preparation field sizes to Decimal and Format to General Number.
I don't know what else to try.
View 4 Replies
View Related
Mar 10, 2005
Can anyone tell me why this won't work? It calculates the 2nd argument (reduction_man) but not the first (reduction)
I have tried both of these and still can't get it to work.
=IIf([reduction])>0,([reduction]*[2004volume])/(13-[cutinmonthnmbr]),([reduction_man]*[2004volume])/(13-[cutinmonthnmbr]))
=IIf(IsNull([reduction])=False,([reduction]*[2004volume])/(13-[cutinmonthnmbr]),([reduction_man]*[2004volume])/(13-[cutinmonthnmbr]))
Thanks!
View 1 Replies
View Related
May 24, 2006
Hello Guys,
I am trying to do the following in a form.
1) (Travel hours + work hours) * dollarperhr to get the "Total" bill to a customer.
2) The results will be put in a field "TotalDollars" that is a part of a table.
Please help.
I tried to put an event (on change) on the "dollarperhr" box so that after entering the values in "travel hours" + "Work hours" * "dollarperhr", the calculation will be done to populate "TotalDollars", but it didn't work.
please help
View 7 Replies
View Related
Dec 21, 2004
Hi here is my problem
I have a table named= Commun tasks
which has 3 fields= [Commun tasks],[Quantity] and [Unit Cost]
I created from that table a form who shows these 3 fields. I also created in that form a calculated field (named Total) who shows the Total. I simply wrote
in the control source =[Unit Cost]*[Quantity]
I have another table (Project Information) who has 3 fields=[Prior total],[New Total],[cost addition]
What I need to do is each time the [Quantity] or the [Unit Cost] are changed, I need to show the [New total]. But also i need to show the [Prior total] which is based on the [Quantity] and [Unit Cost] before the change. So i think i need to store the calculated field (Total) in a table, but I don't really know how to do that (I,m a newbie).
Ok let's simplify my question
How can I store in a table a value from a Calculated field? I know that we should not store calculated value but in my case I need to store them.
Thanks for help
View 4 Replies
View Related
Jul 30, 2014
How to Add a calculated field named CustomerName as the first column that is either the company or the concatenation of the last name, a comma a space, and the first name. Sort in ascending order by Customer Name ?
I've came up with CustomerName: IIf([CompanyName] , [LastName] & ", " & [FirstName])
View 8 Replies
View Related