Calculation Query Based on Customer Demand

[SamCrow]

Hey guys,

I am new to this forum, and this is my 1st thread regarding a problem that I am facing.

I am a university student currently studying in the field of Management Information Systems, and I am required to create a database from scratch.
The database that I have created is a customer-related database in let's say a supermarket. This database helps customers to find the item, price, and location of anything that they would like to purchase.

I want to create a query that allows the customers to enter the name of the Item that they want, followed by the quantity that they desire.
I currently have a query that only allows them to enter the item that they want - this will automatically show the location of that item, and the price, but not the quantity that they desire.

So my question is: How can I make that possible?

I am sorry if you find it difficult to understand, as I am still a beginner and it is hard for me to explain it.



Regards,

Sam

[rpeare]

What is your goal? To find out if you have enough stock on hand to handle their request?

Frankly it doesn't matter how much they want, if they want more than you have in stock they're not getting the item

Just displaying your on hand quantity should be sufficient (unless this is a specific request of the assignment)

[SamCrow]

No it has nothing to do with the stock on hand.
Let's say I have 100 Kitkats in the supermarket.
The customer wants to purchase 5 kitkats.
So in the query, when he types in 'kitkat' I want it allow him/her to type in the number that he/she desires (being 5 in this case).
So, as a result, when he/she types in 'kitkat' and '5', it will show him/her the price of x5 kitkats.

Is it still a bit Chinese?

[rpeare]

ah ok, in that case put a field in you query

Quantity: [Enter the Number of Units]

you'll be prompted to put in a quantity, then you can figure the price from there

[SamCrow]

Okay that helped a lot. But I want the Number of units to be multiplied by the price of the item.
Let's say the price of the kitkat is $1, and he wants 5 kitkats, so it would be $5.
Don't I need to make a calculation query for that?
I want it to show the total price of the number of items that he/she wants, not the unit price.
My whole query is to save them time to figure out the price cause people are lazy

[rpeare]

so... what do you do when you find the total cost

you do the same thing in the query multiply the unit cost by the number of units (entered by the user)

[SamCrow]

Ok so where and how do I type in the formula for that?

[rpeare]

I'm trying to answer this without doing your work for you since this is an assignment

if you have a table storing cost per unit it should be fairly straight forward to perform the calcuation you want.

You have two variables:

UNITCOST (from your table, just the field with your cost per unit)

and

[Enter the Number of Units] (entered by the user).

it should be straight forward from there

If you don't have a table storing your unit cost... you need one.

[SamCrow]

My assignment is to create a database. That is it.
It has nothing to do with the question that I am asking.
Our teacher wants us to youtube and learn online on how to do different query and forms.
The course that I am taking is a pure SQL course. It is nothing to do with Access; but, my project should be on Access. As a result, I had to learn Access in order to do my assignment.

I have a UnitPrice field in my 'Items' Table
but the number of units will always vary from one customer to the next.
My problem is that I do not know where to type in the formula of: "UnitPrice" * "The Number of Units"

[rpeare]

In your query, that's what your original question was about

FinalCost: <put your formula here>

just remember to use your actual field names and, in this case, the exact same terminology for your user entered information.

[SamCrow]

Got it!
Thank you repeare.
I deeply apologize for wasting your time on such an easy question.

Regards
Sam