Access MS access, Table id query help

Joined
Jan 23, 2013
Messages
1
Reaction score
0
I am setting up a database in access, for my company. What im trying to setup is a price table.

i have two tables

What I have is one table with product names as the Identifiers with the first column to the right as the average cost. I then have another table (markup table) which has the product cost as the identifiers and the correct markup price in the columns to the right, each column to the right represents a different markup price for x qty.

I would like to be able to pick a shirt, have it pull the cost from the product table, then use that cost as the identifier to find the markup price on the markup table. however I dont have penny increments for my markup table. As it would take 3000 rows to account for the markup of every penny like 3.00 3,01, 3.02..what I have is more like 3.00 3.03..3.07 etc etc...problem is a product may have a cost that is inbetween two identifiers on the markup table..for instance I have a tshirt that is $4.05 but my markup table has an identifier for $4.00 and a indenfier for $4.07 ...not $4.05... yet the markup for 4.07 would work for 4.05...

I want to be able to tell access if you cant find the exact row identifer then round to the nearest one...like wise....I give discounts on my products up to a qty of 1000, oviously I do not have 1000 columns in my markup table. I have a column increments instead such as... 25, 50, 75 ,150, 300 ,etc etc...I would also like it to be able to round for this as well. For example if a customer types in they want 77 tshirts...the database will go to the 75 column as there is no coloum for a qty of 77 ....

im completely new to access and i dont know if this is really easy or really hard to do..

i just was wondering if there was a way to tell access to pull the id from one table then search for it in table two, if its not there then round to the nearest table id in that table and use that one instead....i need to do this with my rows on my markup table, and with my columns as they each are in various increments...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top