Reconstruct the sale price

[Ramesh]

HI,

Need some help in fixing a mess.

I failed to put in an SaleAmt in the Order table. However I have another
Receivable table with Payment Instalment info like OrderNo, Date,
AmountReceivable for each order. Is there a way I could get the total of
Amounts for each order and put it in a SaleAmt field in the Order table?

If this is not possible, can i get this from a price list table, where i
have the fields, DateFrom, Product, Price. This table has records as when
the price changes. Issue here is that i will need to take the SaleAmt from
the price that was valid on that particular day.

I tried doing a sum on the Receivables table and updating that to the Orders
table. But i got a message saying Update can be operated only on updateable
queries.

Thanks for any help please.

Ramesh

 

[Jeff Boyce]

Ramesh

I'm not sure I understand.

It sounds like you have a table ([Receivable]) that contains a total amount
receivable for an order. If so, why would you want to duplicate that data
in your [Order] table? Since I don't know your data structure, I can only
guess that you could create a query that looks up the amount in the
[Receivable] table.

If your data is well-normalized, you'd have an [Order] table and an
[OrderDetails] table, the latter table holding one row per item ordered. If
you ONLY have the total amount for the order (in [Receivable]), how would
you propose to break it apart into "detail" amounts?


--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Ramesh]

Thanks Jeff for your response.

Sorry i was not clear in my last message. In my receivables tables, there
are multiple entries per order. I need the sum of the amounts to be put in
a field in the Orders table. In fact what i am calling Orders table is
indeed the Order Details table.

I am having trouble getting the total of the Receivable for each order and
put them in the Sale amount in the Order Details table. Ideally this should
have been put at the time of the order itself .. but this has been missed
out. Now i have this data only as the receivables in the Receivable table.

Hope i have been clearer this time.

Thanks
Ramesh

Ramesh

I'm not sure I understand.

It sounds like you have a table ([Receivable]) that contains a total
amount
receivable for an order. If so, why would you want to duplicate that data
in your [Order] table? Since I don't know your data structure, I can only
guess that you could create a query that looks up the amount in the
[Receivable] table.

If your data is well-normalized, you'd have an [Order] table and an
[OrderDetails] table, the latter table holding one row per item ordered.
If
you ONLY have the total amount for the order (in [Receivable]), how would
you propose to break it apart into "detail" amounts?


--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

HI,

Need some help in fixing a mess.

I failed to put in an SaleAmt in the Order table. However I have another
Receivable table with Payment Instalment info like OrderNo, Date,
AmountReceivable for each order. Is there a way I could get the total of
Amounts for each order and put it in a SaleAmt field in the Order table?

If this is not possible, can i get this from a price list table, where i
have the fields, DateFrom, Product, Price. This table has records as
when
the price changes. Issue here is that i will need to take the SaleAmt from
the price that was valid on that particular day.

I tried doing a sum on the Receivables table and updating that to the Orders
table. But i got a message saying Update can be operated only on updateable
queries.

Thanks for any help please.

Ramesh