Is this possible in a Query

[NAHolmes]

I have a database with 3 instances of the same field for 3 different records
('Unit Cost').

I want to run a query to show the final cost but I need to add the field
'Unit Cost' 3 times for each of the 3 components in the database.

Is this possible?

Thanks in advance.

 

[Allen Browne]

So you have a table of components, and you are asking if it's okay to add 3
more fields to cover the 'Unit Cost' for 3 cases (perhaps wholesale, retail,
and discount, or perhaps for quantites of 1, 10+, and 100+.)

If that's the question: no it's not alright to have repeating price fields
like that.

It seems that one product (component) can have many prices. This suggests
you need a related table to handling the pricing. This new table would have
fields like this:
ProductPriceID AutoNumber primary key
ProductID Number which product this price is for.
PriceTypeID Text contains "Wholesale", "Retail", or
....
UnitPrice Currency price each.

Now you can record as many prices as you need for each product.

You would also want a little lookup table of the price types, to use as the
RowSource of your PriceTypeID field.

 

[NAHolmes]

Hey, thanks for the prompt reply, I didn't think so.

It's not that the price is variable for a component, but there are more than
one component, each with a different price, so to return an overall cost per
item (made up from numerous components) I need the 'Unit Cost' field from
each record in the components database.

I'm new to Access & guessing that your solution could be adapted to provide
the necessary structural fix. I'm just trying to see how that might work.

Thanks again.

 

[Allen Browne]

So this is like an order that could contain different components at
different prices?

If so, one order contains many line items. You need a table for the order
header (order number, client, date, ...) and another table for the items in
the Order.

The Northwind sample database installs when you load Access onto your
computer. Open it, and open the Relationships window. This wlll all become
clear.

 

[NAHolmes]

Yep, I've been looking at the Northwind DB to get some pointers, but the
order setup they have is not really suitable for what I need to achieve.

Is there a way to automatically link a cost from a record when the
ComponentCode for the said record is selected?

I have created a table for a product which has a number of fields selected
from 4 sub-tables (components). The ComponentCode fields in the product
table are set up as a combobox so the selection is variable. The issue
arises when there are 2 or more components from the same sub-table. I guess
I'm needing to mix horizontal & vertical data (i.e. fields & records).

Can't see the wood for the trees.

 

[Allen Browne]

In the order details subform, when you select a product in the combo, it's
AfterUpdate event procedure looks up the current price and drops it into the
row for the order. Ultimately that's the kind of thing you need.

I didn't follow the bit about using 4 subtables; not about mixing horizontal
and vertical data.

From a relational database standpoint, repeating fields horizontally is
always wrong, so you need to use many related records, not many repeating
fields.

 

[NAHolmes]

I think the problem lies in the table I have set up. Unlike the one for
'Order Details' in Northwind, I have added all the fields I want for
components as oppose to just one. If I replicate the Northwind set up, how
can I reference the 6 tables which hold the component data?

 

[Allen Browne]

Why 6 tables? Is there any chance of combining those into one table, like
the Products table in Northwind?

 

[NAHolmes]

I want to have a separate table for each of the component suppliers - is this
not possible then?

 

[Allen Browne]

You can create a UNION query to combine the data from multiple tables.

But it would be a much better design to combine them into one table, with an
extra field to tell who the supplier is.

 

[NAHolmes]

I though so. I was hoping to be able to have separate tables for this type
of data to make them more manageable. This becomes more of an issue as there
are 100's of products, each using 10's of components.