Running Quary

[laura808]

Hi everyone,

I having trouble understanding how to do quaries using Microsoft Access. I have a table for a hotel and I want to do query on:

1) what is the average length of stay per room type?
2) What is the average number of visitors per room type?
3) What is the base income per room (ie lenght of vist muliplied by daily rate) during a specified period of time?

4) what is the strongest customer base?

Can someone please help me out. Like what field i need to put and also what formula I should use to fiqure the calculation.

I have attached a file but is in excel.
Please help someone. |Thanks

 

[Trevor G]

Welcome to the Forum,

Attached is an example from your data, I copied and pasted into Access, then created 2 queries 1 for the Average by Room and the other to calculate the amount by number of guests. In the amount by number of guests you can set the criteira to be greater then start date and less than the end date

This should get you started.

If you need more help let us know, you can always watch tutorials about queries on YouTube.

 

[laura808]

Hi Trevor,

I can't seem to open that file. What version of access are u using?


Thanks

PS thanks for getting back to me on my questions

 

[laura808]

Hi Trevor,

Nevermind I got it open. Now can you please update
me what am I use to do next. Like what field to calculate to answer those four questions.

Thanks

 

[Trevor G]

If you look at the design of the 2 queries you will find your answer. The Average should give you the means to do the other Average question.

As for strongest customer base, you would create a query on the names and do a count of number of visits.

 

[laura808]

Thanks Trevor. Would you be able to assist me with this other one if you can. That would be awesome.

Using the following information found in the table (here is the link: build a simple relational database for sylvester;s. Once you have built the database perform.
1) prepare a report that identifies the five most expenisve bicycles, the quanitly on hand for each, and the markup percentage for each.

2) prepare a report that lists each suppliers, suppliers products, the quantities on hand

3) prepare a report listing only bicycles that are low in stockand need to reordered.

If you could help that would be great thanks so much once again.

 

[Trevor G]

Can I ask if this is for a school project or is this real business?

How do you think you should approach this?

 

[laura808]

hi for school. Are you able to help me ?

 

[Trevor G]

Yes Laura I will help you and try and give you some useful directions. Lets look at this part by part. Are you able to attach the database that you have to use?

1) prepare a report that identifies the five most expenisve bicycles, the quanitly on hand for each, and the markup percentage for each.

You would need to create a query for this, so find the table that holds the relevenat data and then look to create a new query. Select the relevent fields such as bicycle make and cost, then from the field that relates to the cost just above the field name use the right mouse button and select properties you can then set this to show the top 5 and that will give you your first answer, so then once you can see the top 5 save the query with a name such as qryQ1.

The next part would require you to again create a new query and then select the same fields and also the quantity then use a Grouping and group by the Bicycle names and set the quantity field to use Count then view the query and this gives you the second part to the questions so save the query with a name such as qryQ2.

The next part you can create a new query where you use the same fields but also you need to know the cost price and also the selling price then you need to create an expression to find out the percentage. Save the query with a name such as qryQ3.

That should complete question 1.

Next

2) prepare a report that lists each suppliers, suppliers products, the quantities on hand

Again you need to create a query that uses the suppliers table and the required fields, then you save the query with a name like qryQSuppliers2. From this you can then select to create a Report and use the query as the basis and then save the report with a name such as rptQSuppliers2.

Next question

3) prepare a report listing only bicycles that are low in stockand need to reordered.

Here again you need to create a query from the stock table, from the question sheet have the indicated what the reording level is? You need some direction as to decide how low you can go as this becomes the critiera in the field that deals with Ordering Level.

I hope this helps you.

 

[laura808]

Thanks !

where can i give you the file.

 

[laura808]

Trevor,

I try posting a link of my data but not allow on here. Is there a way i can send you the file?

Thanks

 

[accessiscool]

Hi Laura,

I just realized I have the same assignment as you!!!

I was wondering if you could perhaps give me some guidance on it.

Please could you contact me (attached my email......it wont let me type it in here)

Thanks!

Natasha

 

[laura808]

hi, whats is your email.

 

[accessiscool]

HI Laura,

They won't let me post my email because I have less than 10 posts.

I attached my email in the previous post. Please can you give me your email and I will email you.


I attached my email agian in the text file in this post . Just open it and you will my yahoo email

Thanks again ,

Natasha.

hi, whats is your email.

 

[accessiscool]

Laura??????????????????????????????????

 

[Trevor G]

Trevor,

I try posting a link of my data but not allow on here. Is there a way i can send you the file?

Thanks

Laura I have been working off site for a few days now, but around again, how have you been getting on and have you mastered the reports?