insert records into table based on the select criteria from the first table. (1 Viewer)

[bkalimili]

Hi

I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).

Forms involved are frmmain and frmsub.

Frmmain contains the call table information in the main form and parents information in the subform.

When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.

Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”

 

[bkalimili]

I got it to work thanks.

I got it to work thanks.

 

[bertiespiv]

I am trying to insert multiple rows into a table (table1) based on the contents of another table (table2) including a calculated row which = column4 of table2 x = column9 of table 2, based on the using the following construct;
INSERT INTO Table1 (column1, column2, column3, column4, column5)
SELECT column1, column2, < column4 x column9 >, column5
FROM Table2

But it inserts the result of the first calculation in every other row. So for example if row1 in Table2 contains 4 in column4 and 2 in column9 I end up with 8 being inserted in every column 3 of Table1?
Apologies if this is not the right way to ask a question. It is remarkably difficult to find out how you post a new question??

 

[boblarson]

Well, the first thing I would say is that in 90% of the cases you do NOT store calculated values. So, why are you doing it? What is the data (you should use REAL field names and such here as trying to simplify it only makes it worse when trying to troubleshoot these things)?

 

[bertiespiv]

Bob,
thanks for your response. I am aware of normalisation and 1nf, 2nf etc. as I have been working with databases for twenty years so I can assure you there are sound reasons here for attempting to store a calculated field. I am however pretty new to Access and all its joys. What I am trying to do is generate a multi-lined invoice based on parts costs and volumes from a Parts table. At this stage of the business process I only need to be manipulating the List price. So my data looks like this;
Pick list table;

row 1 Part No#1, Part Description#1, Part Cost#1, Quantity#1
row 2 Part No#2, Part Description#2, Part Cost#2, Quantity#2
etc.
I want the following in my Invoice detail;

Row 1 Part No#1, Part Description#1, Part Price#1, Quantity#1
Row 2 Part No#2, Part Description#2, Part Price#2, Quantity#2
Where Part Price = Part Cost * Markup
Hope this is cleare.
btw what is the best was of asking a question in this forum?

BS