Query - Table

[Revned]

i have a subform name [OrderSubform] under my main form with a Record
Source: qryOrder
with the following SQL from a query object

SELECT OderTable.OrderRef, OderTable.CustomerID, OderTable.[Item Code],
Item.Description, OderTable.Quantity, Item.Price, [Quantity]*[Price] AS
Amount, OderTable.[Date Order], OderTable.[Date required], OderTable.[Date
Deliver], OderTable.Remarks
FROM OderTable INNER JOIN Item ON OderTable.[Item Code] = Item.[Item Code];

when i enter the Item code in Item code column of my subform under my main
form it populate the item description in Description column and price to
Price column then I manually enters the qty in Qty column and the Amount
column calculates [Qauntity]* [Price] so it works will so far

but the problem is when I check and open the tblOrder where my query is
bound only the Item Code and the Price are populate in table but the
Description and the Amount are empty

i am using access 2003, pls. i just want to know what could be the best way
to handle this?

pls.if there is a need to reconstruct my SQL i will appreciate it.

thanks in advance

 

[Duane Hookom]

If I understand correctly, the best way to handle this is to NOT store the
description anywhere other than your Item table. Also, you should not store
the Quantity * Price since it is a simple calculation that can be performed
in queries or control sources.

 

[Revned]

the Quantity*Price are calculated in my query not in the subform or main form

If I understand correctly, the best way to handle this is to NOT store the
description anywhere other than your Item table. Also, you should not store
the Quantity * Price since it is a simple calculation that can be performed
in queries or control sources.

--
Duane Hookom
Microsoft Access MVP

i have a subform name [OrderSubform] under my main form with a Record
Source: qryOrder
with the following SQL from a query object

SELECT OderTable.OrderRef, OderTable.CustomerID, OderTable.[Item Code],
Item.Description, OderTable.Quantity, Item.Price, [Quantity]*[Price] AS
Amount, OderTable.[Date Order], OderTable.[Date required], OderTable.[Date
Deliver], OderTable.Remarks
FROM OderTable INNER JOIN Item ON OderTable.[Item Code] = Item.[Item Code];

when i enter the Item code in Item code column of my subform under my main
form it populate the item description in Description column and price to
Price column then I manually enters the qty in Qty column and the Amount
column calculates [Qauntity]* [Price] so it works will so far

but the problem is when I check and open the tblOrder where my query is
bound only the Item Code and the Price are populate in table but the
Description and the Amount are empty

i am using access 2003, pls. i just want to know what could be the best way
to handle this?

pls.if there is a need to reconstruct my SQL i will appreciate it.

thanks in advance

 

[Duane Hookom]

It's great they are calculated in the query. I haven't heard any
justification for saving the calculated values in tblOrder.

--
Duane Hookom
Microsoft Access MVP

the Quantity*Price are calculated in my query not in the subform or main form

If I understand correctly, the best way to handle this is to NOT store the
description anywhere other than your Item table. Also, you should not store
the Quantity * Price since it is a simple calculation that can be performed
in queries or control sources.

--
Duane Hookom
Microsoft Access MVP

i have a subform name [OrderSubform] under my main form with a Record
Source: qryOrder
with the following SQL from a query object

SELECT OderTable.OrderRef, OderTable.CustomerID, OderTable.[Item Code],
Item.Description, OderTable.Quantity, Item.Price, [Quantity]*[Price] AS
Amount, OderTable.[Date Order], OderTable.[Date required], OderTable.[Date
Deliver], OderTable.Remarks
FROM OderTable INNER JOIN Item ON OderTable.[Item Code] = Item.[Item Code];

when i enter the Item code in Item code column of my subform under my main
form it populate the item description in Description column and price to
Price column then I manually enters the qty in Qty column and the Amount
column calculates [Qauntity]* [Price] so it works will so far

but the problem is when I check and open the tblOrder where my query is
bound only the Item Code and the Price are populate in table but the
Description and the Amount are empty

i am using access 2003, pls. i just want to know what could be the best way
to handle this?

pls.if there is a need to reconstruct my SQL i will appreciate it.

thanks in advance