Call a table base on criteria

[Guest]

I have a database for purchase orders with approximately 40 Manufacturers. I
would like to have a separate pricing table for each manufacturer. Is there a
way to call a table base on the chosen manufacturer? I would also need for
this table to be a lookup from a combo box. I currently have everything
working correctly but only one pricing table for all the Manufactueres.

 

[John Vinson]

I have a database for purchase orders with approximately 40 Manufacturers. I
would like to have a separate pricing table for each manufacturer.

Storing data in tablenames as you propose is simply WRONG. Instead,
you should consider a many to many relationship; your pricing table
would have a ManufacturerID, a ProductID, and a price - the price that
this manufacturer charges for this product. You can then select which
record you want (the cheapest price, the most reliable manufacturer,
whatever).


John W. Vinson[MVP]

 

[Guest]

You should have only one pricing table, not one for each manufacturer.
Having a separate pricing table for each manufacturer will cause all kinds of
grief. It will over complicate everything you try to do. For every query
the uses a pricing table, you would need 40 versions, etc.

All you need is a field in the pricing table record that carries the primary
key value of the manufacturer it belongs to. Then all you have to do is
filter your pricing table based on the current manufacturer.

 

[Guest]

Okay only one Table, but this table will become extremely large over time.
How can I add a filter to my combo box to that will display only items for
the chosen Manufacturer?

 

[Guest]

Both Klatuu and JV are far more knowledgeable than I and have helped me many
times; so it is with the most humble voice that I protest - but I would have
to say that the insistence that 40 manufacturer's price/parts tables must be
one table is not real world. Not if each one is extremely big and changes
frequently. Besides being a huge table - it would hamper updating if
different employees needed to work in different manufacturer's data at the
same time to make updates.

If the products are very static and the list of each is not big - one table
is much simpler. But I've seen huge parts lists that are constantly changing
- and it is a full time job of several people just to keep the multitude of
parts lists updated....so if this is your case one table really may not be
feasible.

The Purchase Order table can be a simple cross reference of PO ID record
with the Manufacturer IDs with Parts ID and Prices of that PO transaction.

I think when you say 'call a table' - you can have 40
Manufacturers/Tables/Forms and launch the Manufacturer Form by selection from
a pulldown.

Assuming the Manufacturer Form is based on their table then what you need is
a method to select a Part ID and have it's essential data copied to your PO
Table record to populate the data for a specific PO.

 

[John Vinson]

Both Klatuu and JV are far more knowledgeable than I and have helped me many
times; so it is with the most humble voice that I protest - but I would have
to say that the insistence that 40 manufacturer's price/parts tables must be
one table is not real world. Not if each one is extremely big and changes
frequently.

What's your definition of "Huge"?

Let's say you have 100 manufacturers, each of whom makes 10000
products. A million rows.

With proper indexing, THIS IS NO PROBLEM.

Besides being a huge table - it would hamper updating if
different employees needed to work in different manufacturer's data at the
same time to make updates.

Why? Access is multiuser, out of the box. If they're updating
different records in the table, this will not cause any problems. Only
if two users were both updating the EXACT SAME RECORD would there be a
clash.

If the products are very static and the list of each is not big - one table
is much simpler. But I've seen huge parts lists that are constantly changing
- and it is a full time job of several people just to keep the multitude of
parts lists updated....so if this is your case one table really may not be
feasible.

It's SIMPLER than updating 100 tables.

The Purchase Order table can be a simple cross reference of PO ID record
with the Manufacturer IDs with Parts ID and Prices of that PO transaction.

With the single table, that's exactly what it would be.

I think when you say 'call a table' - you can have 40
Manufacturers/Tables/Forms and launch the Manufacturer Form by selection from
a pulldown.

Assuming the Manufacturer Form is based on their table then what you need is
a method to select a Part ID and have it's essential data copied to your PO
Table record to populate the data for a specific PO.

Which requires extensive code which would need to be edited every time
you add a new manufacturer.

John W. Vinson[MVP]

 

[John Vinson]

Okay only one Table, but this table will become extremely large over time.

What's your interpretation of "extremely large"? Let's say you have
100 manufacturers, each with 10000 products. That's a million rows.

With proper indexing (and hardware), this is a very comfortable size
for Access. (Yes, Aaron will disagree, but Aaron's got a bee in his
bonnet on the subject). If you're talking 10,000,000 rows, well...
that's getting extremely large, and you may want to go to a SQL/Server
backend. The design would still be best kept normalized, though.

How can I add a filter to my combo box to that will display only items for
the chosen Manufacturer?

Base the Combo on a Query which references the Manufacturer combo box
on the form, using a criterion like

=[Forms]![Orders]![cboManufacturerID]

Requery the price combo in the afterupdate event of the manufacturer
combo.

John W. Vinson[MVP]

 

[Guest]

Create a table Pricing with fields Item, Price, Manufacture. You can also
have a date and flag field for old so you have pricing history. Join the two
tables on the item. Use your forms listbox to select the manufacture. If
keeping history then add the flag field and criteria.

SELECT Sales.Customer, Sales.ItemRevenue, Sales.Item, Pricing.Price,
Pricing.Manufacture
FROM Sales INNER JOIN Pricing ON Sales.Item = Pricing.Item
WHERE (((Pricing.Manufacture)=[Forms]![YourForm]![YourListBox]));

 

[Guest]

To be able to yank a manufacturer/table off line & immediately replace with
another - maybe due to payment, delivery, quality issues....or Manufacturer B
submits a lower bid for the paper products table....or Manufacturer X
releases all new part # and you need to take their table offline and put in a
temp table that says "offline" while the update is being done.... Tough
to do record by record within a table that is active real time...

but it isn't right or wrong - it is pros & cons.... what are the downsides
of multiple table approach? ...plenty - - but it needs to be
managed....particularly the table naming scheme...

 

[John Vinson]

To be able to yank a manufacturer/table off line & immediately replace with
another - maybe due to payment, delivery, quality issues....or Manufacturer B
submits a lower bid for the paper products table....or Manufacturer X
releases all new part # and you need to take their table offline and put in a
temp table that says "offline" while the update is being done.... Tough
to do record by record within a table that is active real time...

Not in my experience. Update queries are reasonably fast; you'll only
get a conflict - again - if you're editing THE SAME RECORD. Even then,
it will just make you try again a few seconds later. You do *not* need
to take the table offline to do this.

but it isn't right or wrong - it is pros & cons.... what are the downsides
of multiple table approach? ...plenty - - but it needs to be
managed....particularly the table naming scheme...

Agreed. I'd still use the single table approach until I had hard and
irrefutable evidence that it was impractical, because the 100-table
approach is guaranteed to be impractical from the get-go!

John W. Vinson[MVP]

 

[Guest]

Thanks everyone I will have to chew on this for awhile