Solved Designing a DB (1 Viewer)

[AnilBagga]

The DB basically needs to create monthly price lists for customers. The table and queries planned are as below. These 5 tables and queries are more or less ready

• tblItemDetails = PK – ERPFGCode + various specifications (say TS)
• tblCustomerRFQ – PK – CustCode and ERPFGCode. It will have other details like quantity. Some ERPFGCodes will be common across customers but all ERPFGCodes will be in tblItemDetails
• tblRM – this will have historical data customer wise. PK – CustCode. Other fields : Date and RMPrice. This table will again have all CustCodes in tblCustomerRFQ
• frmMain – This form will have a date which the user will fill in when rates are to be determined and a command button C1 which will initiate the price calculation for the date mentioned on the form
• qryPrice – This query will calculate the rate (CalculatedPrice) using inputs from above 3 tables and the form

The final step, where I need help, is as below.

I want to store in the table all the values determined when the command button – C1 (Sr 4) is clicked. The steps foreseen are

Say there are 30 records in tblCustRFQ. When C1 is clicked, 30 records will populate in Temporary Table called tblTempQuote.

Fields in this tblTempQuote will be CustCode, Date (as in frmMain), ERPFGCode, CalculatedPrice (from the qry – Sr 5 above) and another field Quoteprice which will, by default, have the same values as CalculatedPrice and will be editable.

Using a form – frmTempQuote, I want to edit the values of Quoteprice for all these 30 records as the actual price quote could be different from the Calculated price. After this updation, a command button C2 on frmTempQuote , will save these 30 records in a new table tblCustQuote and simultaneously empty the table - tblTempQuote

Later, on a different date, if tblCustomerRFQ has 40 records, the cycle a) to c) will be repeated and the 40 new records are appended to the table tblCustQuote.

 

[theDBguy]

Hi. It might help if you could ask a specific question or step you need assistance. Otherwise, we can't tell what guidance you might need. Since you only have tables right now, you might also consider posting your db, so we have something to use when making suggestions.

 

[AnilBagga]

Hi. It might help if you could ask a specific question or step you need assistance. Otherwise, we can't tell what guidance you might need. Since you only have tables right now, you might also consider posting your db, so we have something to use when making suggestions.

Let me clean my my DB before sharing

 

[jdraw]

To test your tables and relationships, you may find this approach helpful to make sure your structure supports your requirements. Always good to ensure your table designs before getting too deep into physical programming.

 

[AnilBagga]

To test your tables and relationships, you may find this approach helpful to make sure your structure supports your requirements. Always good to ensure your table designs before getting too deep into physical programming.

I read this post. Interesting. Fortunately we have had brainstorming sessions with users in this case. My initial foray into Access was with lesser preparation and consumed a lot of time in correcting things!

I saw a post on code for emptying and appending data from a TempTable to another table - C2. Still looking for C1!