Not sure how to do this part

[Bob H]

I have a shipping control database, which has worked well for the last
few months, and now I need to add some extra features to it.

What I need to do is have a text box in my main form which will give me
a value of the said shipment, but it isn't simple because there are
about 30 parts which can be shipped from any one of 4 different sites.
For example out of the 30 different parts, there can be a shipment of
say 7 of one type and 14 of another, which have different values.
So if PartA x7 has a value of £35.00 and PartB x14 has a value of £45.00
I need to have some means of calculation the total value.

I have created a tblValues, which lists the value of each of the 30
different parts.
I am thinking that a query of some sorts would do this for me, but just
can't see it at the moment.

Thanks

 

[KARL DEWEY]

Try this using your table and field names --
SELECT [tblShipping].[part], [tblShipping].[QTY], [tblValues].[price],
([tblShipping].[QTY] * [tblValues].[price]) AS Total_Cost
FROM [tblShipping] INNER JOIN [tblValues] ON [tblShipping].[part] =
[tblValues].[part]
ORDER BY [tblShipping].[part];

 

[Bob H]

Try this using your table and field names --
SELECT [tblShipping].[part], [tblShipping].[QTY], [tblValues].[price],
([tblShipping].[QTY] * [tblValues].[price]) AS Total_Cost
FROM [tblShipping] INNER JOIN [tblValues] ON [tblShipping].[part] =
[tblValues].[part]
ORDER BY [tblShipping].[part];

Thanks for the SQL statement, but there seems to be a syntax error in
this line:
([Shipping_Control].[CargoQty] x [tblValues].[Value]) AS
CommercialValue

Shipping_Control is the shipping table.
CargoQTY is a feild for the number of items/parts
CommercialValue is a feild for the actual value of the shipment.

Also I am running Access 2007

Thanks

 

[KARL DEWEY]

Take a closer look at what I posted. The multiplication function is an
asterisk and not an 'x' as you used.

([Shipping_Control].[CargoQty] * [tblValues].[Value]) AS CommercialValue

--
Build a little, test a little.

Try this using your table and field names --
SELECT [tblShipping].[part], [tblShipping].[QTY], [tblValues].[price],
([tblShipping].[QTY] * [tblValues].[price]) AS Total_Cost
FROM [tblShipping] INNER JOIN [tblValues] ON [tblShipping].[part] =
[tblValues].[part]
ORDER BY [tblShipping].[part];

Thanks for the SQL statement, but there seems to be a syntax error in
this line:
([Shipping_Control].[CargoQty] x [tblValues].[Value]) AS
CommercialValue

Shipping_Control is the shipping table.
CargoQTY is a feild for the number of items/parts
CommercialValue is a feild for the actual value of the shipment.

Also I am running Access 2007

Thanks
.

 

[Bob H]

Take a closer look at what I posted. The multiplication function is an
asterisk and not an 'x' as you used.

([Shipping_Control].[CargoQty] * [tblValues].[Value]) AS CommercialValue

mmm, yes ok it is an astrisk, doh!
Ok that works a treat now, but my next goal is to get the
CommercialValue from that query into the text box on the main form.

I have tried using an expression builder which points to that
CommercialValue from that query (qryPartValue), but it doesn't do
anything, and when I go back into design view to check it, the
expression has gone from the control source, and is using the
CommercialValue control source from the table instead.

Thanks

 

[KARL DEWEY]

Not need for experssion builder just use the query field as source for the
text box on the form.

--
Build a little, test a little.

Take a closer look at what I posted. The multiplication function is an
asterisk and not an 'x' as you used.

([Shipping_Control].[CargoQty] * [tblValues].[Value]) AS CommercialValue

mmm, yes ok it is an astrisk, doh!
Ok that works a treat now, but my next goal is to get the
CommercialValue from that query into the text box on the main form.

I have tried using an expression builder which points to that
CommercialValue from that query (qryPartValue), but it doesn't do
anything, and when I go back into design view to check it, the
expression has gone from the control source, and is using the
CommercialValue control source from the table instead.

Thanks
.

 

[Bob H]

Not need for experssion builder just use the query field as source for the
text box on the form.

Yes, thats what I thought initially, but the said control source from
the qryPartValue which gives me the Commercial Value, is not showing in
properties. I can't see how to get it there as a source.

The main Shipping_Control form uses the tblShipping_Control as the
source, and the controls from that table are the only ones showing/available

Thanks

 

[KARL DEWEY]

In the main Shipping_Control form source SQL you have to join the
qryPartValue to tblShipping_Control.

If you can not do it then post both and someone can show how to do it.