If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data from one table and write it to another table
How do I take the Date from one table and use it in another table.
I have a form frmPurchaseOrders that has the following table fields and a subForm subProductsInventory. POID CompanyID PODate The subForm subProductsInventory has the following fields. POID InventoryDate ModelID UnitPrice UnitsOrdered UnitTotal What I want to do is obtain the value of the PODate in the frmPurchaseOrders and place it into the InventoryDate of the subProductsInventory. I can do this in the form but can not get it to write to the underlying table tblProductsPurchased. The subForm subProductsInventory underlying table is tblProductsPurchased. So when I open up the Form frmProductsInventory the subForm subProductsInventory shows the date the item was purchased. Thanks in advance. -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Data from one table and write it to another table
Logical,
If every subform record's InventoryDate ALWAYS has the same date as the main PODate, then there's no need for that subform table field at all. The key POID relationship between the Main table and the Sub table can associate that PODate with it's related subform records... through any subsequent query for another form, or report. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Loggical via AccessMonster.com" u28343@uwe wrote in message news:9f306574c6071@uwe... How do I take the Date from one table and use it in another table. I have a form frmPurchaseOrders that has the following table fields and a subForm subProductsInventory. POID CompanyID PODate The subForm subProductsInventory has the following fields. POID InventoryDate ModelID UnitPrice UnitsOrdered UnitTotal What I want to do is obtain the value of the PODate in the frmPurchaseOrders and place it into the InventoryDate of the subProductsInventory. I can do this in the form but can not get it to write to the underlying table tblProductsPurchased. The subForm subProductsInventory underlying table is tblProductsPurchased. So when I open up the Form frmProductsInventory the subForm subProductsInventory shows the date the item was purchased. Thanks in advance. -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Data from one table and write it to another table
On Mon, 16 Nov 2009 07:12:22 GMT, "Loggical via AccessMonster.com"
u28343@uwe wrote: You seem to have some database design problems. If there is a hard rule that says that tblPP.InventoryDate is always equal to tblPO.PODate, then the field should be removed altogether, per relational database design rules. Same goes for UnitTotal. Since it is always Price*Units, it does not belong in a relational db. If you ever need this value, you can calculate it in an expression or a query. Also it appears you are mixing two concerns in one table: tblPP has both line items for a PO as well as inventory information. More than likely those two should be in separate tables. -Tom. Microsoft Access MVP How do I take the Date from one table and use it in another table. I have a form frmPurchaseOrders that has the following table fields and a subForm subProductsInventory. POID CompanyID PODate The subForm subProductsInventory has the following fields. POID InventoryDate ModelID UnitPrice UnitsOrdered UnitTotal What I want to do is obtain the value of the PODate in the frmPurchaseOrders and place it into the InventoryDate of the subProductsInventory. I can do this in the form but can not get it to write to the underlying table tblProductsPurchased. The subForm subProductsInventory underlying table is tblProductsPurchased. So when I open up the Form frmProductsInventory the subForm subProductsInventory shows the date the item was purchased. Thanks in advance. |
#4
|
|||
|
|||
Data from one table and write it to another table
The UnitTotal is a calculation only, its not written to the table.
Yes the PODate will never change, but the InventoryDate will always change as items are delivered, Used and Disposed. I need to re-think the way i have explained my original quetsion. Tom van Stiphout wrote: You seem to have some database design problems. If there is a hard rule that says that tblPP.InventoryDate is always equal to tblPO.PODate, then the field should be removed altogether, per relational database design rules. Same goes for UnitTotal. Since it is always Price*Units, it does not belong in a relational db. If you ever need this value, you can calculate it in an expression or a query. Also it appears you are mixing two concerns in one table: tblPP has both line items for a PO as well as inventory information. More than likely those two should be in separate tables. -Tom. Microsoft Access MVP How do I take the Date from one table and use it in another table. [quoted text clipped - 25 lines] Thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
Thread Tools | |
Display Modes | |
|
|