Trying to calculate field

[Phoenix Tech]

Hi everyone im new to this forum and also very rusty and a beginner in access. was wondering if you could help me with my database in Microsoft Access 2010.

I will try my best to give you the rundown of its function.

The database in itself is not that complicated to be honest, I have already begun building the tables and some of the forms.(i think they are correct)

Here is what I need the database for:

We are an online IT company that sells computer hardware and gadgets.
We have been working off an excel worksheet trying to keep it all updated but with about 11,000 products in our catalogue maintenance to keep that in pristine order is quite an ordeal.
So first off is that the database holds all of our catalogue from our various suppliers.

Here are the tables that I have created so far:

Suppliers
>>ID
>>Supplier Name

Categories
>>ID
>>Category Name
>>LOW Range Markup
>>Mid Range Markup
>>High Range Markup
>>LOW Value
>>MID Value
>>High Value
>>Number of LOW end Products
>>Number of MID end Products
>>Number of High end Products
>>Total Category Products
>>Total Category Products ON Site
>>Category Progress
>>Average Cost
>>Average Sale
>>Average Profit
>>Cheapest
>>Most Expensive

Products:
>>Product Code
>>Cost
>>BUYS
>>SELLS
>>Profit
>>Description
>>ON Site
>>Category
>>Supplier

Now I would want it to function so that I could import the data from a general excel template and feed it into the database.

now im trying to calculate the [SELLS]field of a product automatically by running a logical check. my problem is that the logical checks reference a different category so i will try and list what i want it to do here:

calculate the products price:
and these are nestled IF statements
IF([BUYS]< [LOW Value],[BUYS]*[LOW Range Markup] +[BUYS],
IF([BUYS]< [MID Value],[BUYS]*[MID Range Markup]+[BUYS],
IF([BUYS] > [HIGH Value],[BUYS]*[MID Range Markup] +[BUYS],0
)
)
)
so how do i calculate the [BUYS] Field the rest of the fields are in the Categories table and Buys is in the Products table??

please help

 

[andy_25]

You will need to setup a Query to perform that calculation.

Create a Query with all the data in you require from the tables, then you can perform the calculation as a calculated field in the query

 

[Phoenix Tech]

You will need to setup a Query to perform that calculation.

Create a Query with all the data in you require from the tables, then you can perform the calculation as a calculated field in the query

Thanks i have now created the query but where do i insert the calculation in the

criteria field:
or:

??

thanks

 

[andy_25]

Thanks i have now created the query but where do i insert the calculation in the

criteria field:
or:

??

thanks

You put it in the field. i.e. you could calculate a line total in an invoice by adding Qty and item_price to a query then in a calculated field =[Qty]*[item_price]
This creates a new column in your query output.

 

[boblarson]

Your table design is not properly normalized as you should not be storing calculated values like these:

>>Number of LOW end Products
>>Number of MID end Products
>>Number of High end Products
>>Total Category Products
>>Total Category Products ON Site
>>Category Progress
>>Average Cost
>>Average Sale
>>Average Profit
>>Cheapest
>>Most Expensive

Those are things that QUERIES should provide AT RUN TIME and should not be stored.

Did I say they were not to be stored? See here for more about normalization.