Not sure how to do this part

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

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];

--
Build a little, test a little.


"Bob H" wrote:

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 wrote:
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

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.


"Bob H" wrote:

KARL DEWEY wrote:
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 wrote:
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

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.


"Bob H" wrote:

KARL DEWEY wrote:
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 wrote:
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

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.

--
Build a little, test a little.


"Bob H" wrote:

KARL DEWEY wrote:
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
.