choseing table as datasource based on combobox entry

[Tuuslar]

Hi,
Want to build products data (price) searching form. I have many different suppliers and don't want to but all prices into one big "pricelist" where will be more then about 1M rows, but keep pricelists in different tables.


So from first combo I want to chose supplier and next combo should search in this particular supplier pricelist/table. I managed to get list of all tables in my database to combobox. But can't figure out how "link" particular supplier pricelist into second combo.

thanks in advance

[orange]

How would you keep such a list current? How long would the price you record be the "effective price"? Prices change for many reasons. Where do get these prices?

May be you could tell us more of your requirement and environment.

[CJ_London]

I have many different suppliers and don't want to but all prices into one big "pricelist" where will be more then about 1M rows, but keep pricelists in different tables.

what do you see as the benefit of using separate tables? you'll still have 1m records

[Gicu]

You might want to look up "Cascading combo boxes":https://www.google.com/search?q=casc...hrome&ie=UTF-8

Basically your second combo box row source needs to reference the first one where you select the supplier; then in the second combo's Enter or GotFocus event you requery it.

Cheers,

[Tuuslar]

How would you keep such a list current? How long would the price you record be the "effective price"? Prices change for many reasons. Where do get these prices?

May be you could tell us more of your requirement and environment.

Thanks for replay.
Right now I have 15+ excel tables. It means endless open-close excels + copy/paste - to set up price quote for customers. I have sophisticated soft for accounting, but it has some cons why I would prefer to use excel type list for price lookup.
Usually these tables will be update once per year. But time to time often. To keep them separate would enable to update them when needed or delete completelly.


Do you believe that 1 "Products" table with supplierID column would work better then preliminary comobox sorting of different tabels for such a amount of rows?
This may be better way for next step as I plan to collect all these search results to another form to generate offer to enduser. I don't know. Hope someone could comment.

[CJ_London]

you haven't provided any reason for using multiple tables except you prefer to use the excel type list for price lookup. It is very difficult for databases to do things the excel way since they are not designed to work that way.

So from first combo I want to chose supplier and next combo should search in this particular supplier pricelist/table

very easy to do with a single table and a technique called 'cascading combos'. Not so easy with separate tables.

Do you believe that 1 "Products" table with supplierID column would work better then preliminary comobox sorting of different tabels for such a amount of rows?

As all responders here have said - No.

From your brief description of your requirement, you need 3 tables

tblProducts
ProductPK
ProductName
SKU

tblSuppliers
SupplierPK
SupplierName

tblPrices
PricePK
SupplierFK
ProductFK
Price
DateFrom
minOrderQty???
BoxQty?????

last 2 fields might go into a separate table if needed

you might need a separate table for Supplier SKU's

tblSupplierSKU
SKUPK
SupplierFK
ProductFK
SupplierSKU

You might need more fields/tables to store information such as delivery times/costs etc

With regards copy/paste you would use a query to import and append or update data instead

[Tuuslar]

you haven't provided any reason for using multiple tables except you prefer to use the excel type list for price lookup. It is very difficult for databases to do things the excel way since they are not designed to work that way.

very easy to do with a single table and a technique called 'cascading combos'. Not so easy with separate tables.

As all responders here have said - No.

From your brief description of your requirement, you need 3 tables

tblProducts
ProductPK
ProductName
SKU

tblSuppliers
SupplierPK
SupplierName

tblPrices
PricePK
SupplierFK
ProductFK
Price
DateFrom
minOrderQty???
BoxQty?????

last 2 fields might go into a separate table if needed

you might need a separate table for Supplier SKU's

tblSupplierSKU
SKUPK
SupplierFK
ProductFK
SupplierSKU

You might need more fields/tables to store information such as delivery times/costs etc

With regards copy/paste you would use a query to import and append or update data instead

Thank you for makeing clrear! Will start to think "database way". I'm wondering why product names and price needs to be kept separate? ProductPK - I suppose you mean product code, but FK?

[CJ_London]

PK is Primary Key - a field that is indexed and has no duplicates and uniquely identifies the record - and is identified as such. For efficiency this is usually a number but can be text. Also usually the autonumber is used so it populates automatically. You might use your product code, but it is generally better not to - you have additional work if it needs to change. The efficiency come from data sizes - a long number is 4 bytes, text is 2 bytes per character plus another 2 bytes. So an 8 character code will be 18 bytes - 4.5 times larger than an 8 digit number.

FK is Foreign Key - used in the child table, this field is indexed, but duplicates are allowed and contains the PK value of the parent record. As with the PK this value is stored in the field and in the index. So say you have 100 parent records and each has 10 child records (i.e. a total of 1000 child records). So your db requires for a long number index 8+8000 bytes. for text it requires 36+36000 bytes, of disk space.

Further when an index is interrogated, the OS can only read 4096 bytes at a time. If the index is numeric, it can pick up 1024 index values, if text it can only pick up 227 index values. Indexing algorithms are pretty smart but you can see you have a built in inefficiency with text.

Why index? performance again. If a field is not indexed, the db has to scan every record looking for a match - which clearly will take a lot longer. See this link about indexing
https://www.access-programmers.co.uk.../#post-1516326

Note when searching for values using Like, try to avoid the initial * - using it prevents the use of indexing. To avoid, as much as possible ensure your fields are set up in a way that you don't need it (e.g. store lastname/firstname in separate fields, if you have codes to indicate more than one thing, break it down to the separate elements). If you don't know what I'm talking about, don't worry, you'll come to understand soon enough

My other tip for you is to google/bing 'database normalisation'

I'm wondering why product names and price needs to be kept separate?

you may not, but prices change over time and you may want to know what the price was a year ago compared with now - so you have multiple prices per product. Also means you can add future prices in now with an effective date in the future which can help balance your workload.

[Tuuslar]

Thank you. This all make sense.