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
Access 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 want to make a query to figure out profit made by month year and room type.
View 1 Replies View RelatedI 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?
Using Access 2013
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 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 RelatedSo I'm trying to do this database for my ICT coursework and its a full system for dog kennels.
So in actuality the rooms are kennels.
I have a table tblbookings that amongst others has fields:
Kennel No
Date In
Date Out
I need a way of users entering the requested dates for a new booking and getting an output of a list of all kennels that are available to book for that full date range or even better, a way of running this straight from the form for a new booking frmbookings to just leave the first available kennel no. in the field KennelNo?
I have a table that holds information for a type of show and the base cost of the show. I am trying to make a query that gathers information for all the shows in the table and uses the Dlookup function to identify the base cost of that show. This will in turn be used in the same query to create a quote, but I can not get the Dlookup function to work and I am sure that I may be doing it very wrong.
The table is called ShowList. That table contains 3 fields, ID, Show Type, and Cost.
The query is called Venue Cost and will have a series of fields that work to develop some math on all items from another table called Venue Info. Venue Info uses ShowList to select the type of show.
What I want the query to do is look up the Show type from VenueInfo and then look up the base cost of the show type from ShowList. What I have been using is not working but is as follows:
DLookUp("Cost","ShowList","[Cost] =" & "[Venue Cost]![Venue Type]")
I'm trying to copy records from another base into existing table in current base by:
Code:
DoCmd.RunSQL ("INSERT INTO pivot (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) SELECT (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) FROM svod IN 'Z:NPSNPS - Operator - 1.accdb' ")
But it doesn't run. Says insert into syntax error.
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
I want to build a room scheduler. Before I get too far in to my first attempts, does anyone know if there any examples out there I can use to give me ideas?
If not I wonder can anyone advise me as to a difficulty I currently can see with what my attempts so far. I have built a very basic database with fields for each room and time slots through each day. This works ok. The problem I see is finding a way of setting a room booked for say "every Thursday between 14.00 and 15:00". I have considered using a query to do it but cannot see how to formulate it.
Thanks in advance for any help you may offer.
Peter
Hi All,
I am in the process of creating a Room Bookings database using the limited graphical tools of Access 2003.
I intend to break up the day from 0700 to 1900 into 48 X 15 minute chunks (the minimum bookable time) and have these graphically represented on the form as boxes whose colour change depending on whether it is booked and who by.
It would be normal to have up to 8 colours available for this as any one room would be unlikely to be booked by more than 8 people in one day.
I would envisage that each day would be covered by a separate subform.
Has anyone else tried a graphical representation of a booking form?
It would be interesting to hear others views or experiences...
Dave E
Hi
I currently am in the the middle of creating a database to hold details of computer hardware in stock.
I have created a query which simply lists all the items in the main table, then a form pulls the infomation from the query and displays all the items.
On the main page I have some buttons labled Room1,Room2,Room3...etc
What I am wanting to do is to press a button and it show the form but only the items from that room.
I could do this by creating many querys and then forms and having each button link to its correct form however this isn't ideal.
Does anyone know how to get the form to display only items from the query that are set by each button
Thanks
Chris
Can anyone point me in the direction of a room scheduling example or a tutor site. I originally wanted to use Outlook but my boss, after seeing another Acces Project I did, asked for it in the Access. I can build a basic data base on a daily basis showing rooms against hours, but my problem is forward scheduling eg "every Thursday meeting room 1 will be for Group A" etc. Can anyone please get me started - I'm tearing my hair out on this one!
Thanks in advance.
Peter
Is there still a hotel/room booking system sample database available on the forum?
I remember seeing one a while back in the samples forum but can't find the thread now.
If it's not here anymore does anyone know of any samples I can look at?
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
Hi Everyone,
hope someone out there can figure this one out.
I am developing an access database and forms based on a hotel booking system.
There is a tblRoomState table, which holds the roomID, the date and the state of the room, (so for a 3 night stay, this table holds three entries).
I have a query which returns any roomID's from the tblRoomState table which have a state value of 1 at a specified date.
I want to run another query which returns roomID's from the tblRoom table which are not part of the recordset of the first query.
Since there is no records held in the database for rooms which are not booked, the only way to access "availability" is to use a recordset of rooms which are booked and test against that.
Thanks,
John
Hi,
I have been reading throguh various articles and learning a lot from you guys, so thank you very much. This place is a fantastic source of information, by far the best of anything i have found online.
I have a question which i would be grateful if someone could advise upon.
Access 2003. I have 4 tables:
list_accommodation (holding host family address details who offer accommodation to students)
id (autonum)
name (text)
address (text)
etc.....
list_rooms (table of rooms offered by host families)
room_id (autonum)
host_id (number) - joined to list_accommodation.id
room_name (text)
capacity (number)
students (student contact details who want accommodation!)
id (autonumber)
name (text)
dateofbirth (date)
address (text_
etc.....
bookings_accommodation (to hold the accomm bookings)
booking_id (autonum)
room_id (num) - joined to list_rooms.room_id
student_id (num) - joined to students.id
start_date (date)
end_date (date)
This structure therefore enables me to query if rooms are booked between dates:
SELECT bookings_accommodation.room_id, bookings_accommodation.start_date, bookings_accommodation.end_date
FROM list_rooms INNER JOIN bookings_accommodation ON list_rooms.room_id = bookings_accommodation.room_id
WHERE (((bookings_accommodation.start_date) Between DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date]) And DateAdd("d",-1,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date]))) OR ((([end_date]-1) Between DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date]) And DateAdd("d","0",[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date]))) OR (((bookings_accommodation.start_date)<DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date])) AND (([end_date]-1)>DateAdd("d","-1",[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date])));
and therefore, using the 'booked' query i can therefore find out rooms are available.
SELECT list_rooms.room_id, list_rooms.room_name, list_rooms.capacity, list_rooms.description, list_rooms.host_id
FROM list_rooms LEFT JOIN qry_rooms_reserved ON list_rooms.room_id = qry_rooms_reserved.room_id
WHERE (((qry_rooms_reserved.room_id) Is Null));
----
so, now i have found out that there is a necessity to enable students to share a room (the bookings for a room may not happen at the same time). This means that i have to determine the capacity of a room and therefore if
the capacity is not full, then show as available.
I was wondering what the best approach to this may be?
I have added 'capacity' to list_rooms, but i am unsure as to how to go from there....
Any thoughts and advice woudl be really appreciated.
Many thanks
Phil.
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".
I have Table for rooms called Rooms, and the data in the table is roomNumber which is in the format Letter and two Digit number, so A01 would be dorm building A and room building 1, and I then have a seperate row named roomType that is either VIP or Semi Private.
Now I am creating a form where a worker will put in there scheduledCheckin date and scheduledCheckout date and it will be written to the Bookings table. I would like this form however to take the dates they have put in, as well as room type (Semi-Private or VIP, and assign them an available room that isn't booked at all in that range) or list all available rooms for that range of time and they could then just select the room. I would rather it automatically assigns an available room based on room type though because this check in system is going to have about 500 rooms.
I've attached a link for what I have so far. I know how to make a query to list anyone who has booked rooms over that date, but need one for just preventing booking the same room.
[URL] .....
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:
I'm stucked so far, how can I solved it? :mad:
How to calculate the percentage of kilometers per order compared to the total number of kilometers per carrier.
In the attached screenshot send queries where I need formula.
Mileage on the orders of the field called "Korkma" . The formula should look like: # [Korkm] / [total kilometers per carrier] #.
I do not know how to get the total mileage driven by the carrier (the carrier is in the query in the Field Name Plate) for the month.
For example:
date 1.1.2013.,Plate:Pribus Korkm= 37
date 2.1.2013.,Plate: Pribus,Korkm=56
date 10.1.2013.,Plate: Pribus, Korkm= 70
Formula: [37]/[163] = 0,23
[56]/[163]= 0,34
[70]/[163]= 0,43
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?
My problem is as following:
I have post with dd/mm/yyyy
I want access to calculate the date for 25 years later. (Anniversary)