Queries :: Calculate Prices For Reservations Using Query
Dec 31, 2014
I have two excel tables that I linked in access. From excel tables I created query that gives me all data I need.
First table has data of periods and prices of apartment per period. For example:
PropertyID 1111
PropertyName Apartment Zagreb Mila
StaringDateOfPeriod 1.1.2015
EndingDateOfPeriod 1.7.2015
PriceForPeriod 100
.
.
PropertyID 1111
PropertyName Apartment Zagreb Mila
StaringDateOfPeriod 1.7.2015
EndingDateOfPeriod 31.12.2015
PriceForPeriod 120
Now as you can see I have apartments with prices that change per periods.
Second excel table gives me information of total paid service that customers need to pay , number of days that he will stay and CheckIn and CheckOut dates of those guest.
In calclutated fields in query I created calculations like TotalyPiad(from 2. table) - OwnerPrice*TotalyNumberofDays . I guest good result IF i have reservations that matches or is it BETWEEN StartDate and EndDate of periods.
BUT If I have reservation that is overlapping , where CheckOut of guest is 1 or more days > that EndDate of selected period I have problem with calculation because my calculation is based on WRONG price in that period.
For now what I manage to do is to create separate field in query where I calculated number of days that are out of range
CHECK_IN - StartingDate of period
CHECK_OUT -EndingDate of period
Check-in date - starting date of reservation of guest
Check-out date - ending date of guests reservation
nabavna cijena - owner price
But still when I calculate those number of days I multiple those number of days with old period.
So How can I MULTIPLE THOSE NUMBER OF DAYS WITH PERIOD +1 , I meen next period not this old one just for those days that are out of range? [CHECK_OUT] +1 doesn't work it just add extra day in my CHECK_OUT filed
My final idea is :
Separately calculate in one field number of days that are out site of range
Calculate number of days of reservation of guest that are in range
= make sum of those two fields
From that I will create report which I will send to our owners of properties (number of reservations, reservations details, price that they need to pay to us ...)
I also thought this could be possible to make using VBA but I am still not skilled anough in it. Also I was thinking of creating calendar where I would that add those two queries but how to create simple calendar in Access. Is there some template for simple calendar in Access?
I am creating a database to room allocation in a small hotel. I want the system to check room availability between two dates. What would be the best way to construct this in Access?
I am trying to write a breakout formula to identify if today's close is greater than the maximum close of the last 20 days. This is what I have so far:
SELECT WZ07.Symbol, WZ07.AlphaDeliveryMonth, DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) AS transdate, WZ07.DClose FROM WZ07 WHERE (((DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2)))) Between Date()-28 And Date()-1) AND ((WZ07.DClose)=(select max([WZ07].DClose) from [WZ07])));
If I replace the = symbol with a > symbol near the end will it compare today's close to the max of the date range selected? I thought I had this figured out but apparently not.
I can do a query that will give me the last 20 days using the "Between date()-28 and date()-1 criteria. I can do a separate query for the maximum close using select max([WZ07].DClose) from [WZ07] but when I try to put them together it screws up. I need to have it first do the last 20 days query and then take the maximum close from it, and then compare to see if today's close is greater than that number. Can this all be put together? Thanks for your help.
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
Is there a way to calculate the mode value in a query?
In a summary query, I need to return the value that shows up the greatest number of times in a certain column of a table. Is there a function built in to Access or an easy way to perform this, or do I have to make my own function to handle this?
Does anyone know if there is a free Access data base for restaurant reservations that has been built? I am in need of a simple Access solution. Nothing fancy
I have a AS400 database table that stores time in the following manner 70535 (hour, minutes, seconds). Utilizing access 2010, I have used the left, mid, and right command to separate hour, minute, and second into their are separate table values 7 05 35. The AS400 database stores 3:15:30pm in the following manner 151530.
At this point, I used the string function and created the following table value 7:05:35.
Overall, I am needing to convert 70535 to time and then calculate lapse time where the employee name is the same. my database has about 2500 unique employee records per day. I am utilizing a query to perform the above.
date name tstime hour minutes second time 2/8/14 NJohn 151530 15 15 30 15:15:30
When I calculate the values of the fields it pastes the values after each other.For example: if you have the values 1 and 2 (which should become 3) then the result is a value of 12.I've tried changing the function to SUM() but that didn't work.The reason I am using NZ() is due to the fact that some values have a NULL value in one or both of the fields and if NZ is not included it will not calculate anything.
To calculate the percentage of the number of passengers in the total number of passengers per single order using query:
Code: SELECT
Code: A.Date, A.Plate, A.Pax, A.Agency, A.Code, IIf ([pax]> 0, Round ([Pax] / (SELECT Sum (Pax) FROM Sheet2 as B WHERE B.Date = A.Date and B.code = A.code), 2), 1) AS KOR FROM Sheet2 AS A ORDER BY A.Date;
Query works fine in all cases except one, and that is when the encounter a same order in one day
01/04/2013. D_1 0pax 01/04/2013. D_1 5pax
in this case the formula gives a coefficient of 1 to 0 passengers and coefficient of 1 to 5 passengers, should be given a score of 0 0 of passengers and 1 to 5 passengers
tell me if it is possible to calculate a value in a column in a datasheet view of a query. i.e. I want to take a value in 'Column A' and subtract the value in 'Column B' to create a value in 'Column C'?
I'm trying to build a query with an iif statement to calculate expiration dates within a query. For some reason what i'm trying to do is not working as expected.
Here's the situation. Every employees with an exception of two employees in the company has an expiration 2 years after the class took place. The two exceptions are the certified instructors who's training certification is good for three years.
I've created a query with the employeeid, training subject, and maxoftraining date.
I'm trying to write an if statement to give me the expiration date based on the above information. Here's what I have so far; however its not calculating based on the criteria.
Expiration: IIf([tblemployee]![EmpID]=1 Or 2,DateAdd("yyyy",3,[maxoftrainingdate]),DateAdd("yyyy",2,[maxoftrainingdate]))
At this point in time, all "expiration" dates are showing + 3 years rather than just the Employee ID's 1 and 2 and the rest +2 years.
Basically I have a report that shows any 'Issues' that wasnt closed within the KPI Target.
I have the report working, but I simply want to do a count of how many days the observations overran the 'Target Date of Closure'.
The report also shows observations that are not closed but passed their Target Date of Closure. These observations will not have a 'Actual Closure Date', but I would like to still have a count of how many days its overrun closure.
This is a formula I tried to piece together but obviously not correct as it's not returning anything;
IIf(IsNull([Issues]![Actual Closure Date]),DateDiff("d",[Issues]![Target Date of Closure], Date()),DateDiff("d",[Issues]![Target Date of Closure],[Issues]![Actual Closure Date]))
I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
ID Up Time Down Time John 18:00 15:00 Kelvin 08:00 08:05 Melisa 23:00 02:00
This is the Table , i need the Query result show IF [Up Time]-[Down Time] is >=3 hrs
Like Below :
ID Up Time Down Time John 18:00 15:00 Melisa 23:00 02:00
IDUp TimeDown TimeJohn18:0015:00Melisa23:0002:00
I tried DateDiff("h",[Up Time],[Down Time]) in Field but POP out with error "Syntax error (comma) in query expression 'Table1.[DateDiff("h",[Up Time],[Down Time])]' " ...
I obtained data for earnings by industry for men and women over time (5 years). I developed crosstab query that showed the average earnings for men and women for the 5 years.
Now I want within this query to calculate the differential between men and womens average earnings and calculate a percentage within this crosstab query. I tried to use Expressionbuilder with little luck.
I found the attached example a while back (can't find the site again though ) and it calculates a moving average. I've hacked out the parts I need for my own work and I can create my moving average query without an issue.
However, I need to extract the MA data into a table so planned on using append. I kept getting type errors so I tried make table to see what type it was creating and it appears to be Short Text rather than a number.
I've added an extra button and Make Table query to the example.
As far as I can tell from the code, the moving average value when calculated is a Single. However, when I write it to the table, its a Short Text.
How do I make the created Table use Number Type for my calculated moving average?
Basically, I have a database table that is maintained every week. It is about e-mail account licenses for Office 365.
Each column represents a week of license data for every mailbox account which is about 10 000 plus users with the date as the field headers( something like "License information as of 06122013").
Basically, I have created 52 queries based on the license type I require to be calculated & I have a form created to control it. However, right now it is all output only as I have yet to create any user parameter to specify on any of the queries about the date I want
As I am currently unsure how to specify all the queries to use one field date header which I want the user to specify via drop down list in order to calculate all the licenses on this specific date.
For example, if I want to see the license information for 06/11/2013, I would choose "License as of 06112013" & it would then run all the queries based on the header specified earlier & output that information on the form.
Right now the queries are all configured as the "Select" type.
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..
example:new odometer reading - previous odometer reading / filled in qty example: 2000 km - 1000 km / 200 liters = 5 liters /km
Objective: How query will automatically calculate the difference of new odometer reading and last odometer reading for a specific truck number and divide it with filled in qty of fuel?
i have a table with fields "[DATE OF BIRTH]", "age", "temp-age"
i have a query that calculate the age: example; the query will get user to input the "temp-age" and upon "temp-age" is input and "ok" on the query, it will calculate a person's age and store the value inside table "age".
however, the calculated value for the age have some data problem. for example, when a person whose age is yy:mm 24:05 the age inside the table will be 1924:05. there will be a additional "19" at the age.
what cause the additional "19" problem? --------------------------------------------------------------------- my query in design view is like this; (see attachment) it uses " [temp-AGE]-[DATE OF BIRTH] " to calculate the age and store into table "age".
ive got a database for tickets for a school play. how can i create a query that adds up prices from multiple records? would the total be displayed in a form?
I am a beginner Access Programmer, so my understanding at this point is pretty basic, so that could be why I have run up against a wall with this one.
I am trying to create a database that creates Estimates. In the most basic setup, I have a customer table, an estimate table and an item table. The item table would have a description and a price per unit. With those tables I can set up a basic form that creates an estimate for someone. The wall I am referring to is when I ask myself, "how am I going to deal with price increases?" With the current setup, when I change the prices in the item table it will change all previous estimates prices.
How have others dealt with this scenario? I would have a hard time believing this is a rare question. In the real world this is going to happen. Yet I have several access 2007 books and none of them seem to address this. The closest thing I have seen that seems like it might be in the right direction would be some sort of update query.
I need to pull 2 fields from 2 different queries, then calculate them. Those 2 queries has the same structure, just one is last year's data, the other is this year's. :rolleyes:
I tried using the 3rd queries to combine them, then calculate from there, but then I had no clue where to go from there. I don't sql...I was wondering is there anything to do with sql? :confused:
