Prev: jeramie bellmay
Next: Count occurrences
From: Bob H on 21 Jan 2010 07:47
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
From: KARL DEWEY on 21 Jan 2010 21:47
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
> .
>
From: Bob H on 22 Jan 2010 05:20
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
From: KARL DEWEY on 22 Jan 2010 11:18
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
> .
>
From: Bob H on 22 Jan 2010 14:24
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
 |  Next  |  Last
Pages: 1 2
Prev: jeramie bellmay
Next: Count occurrences