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 |
#31
|
|||
|
|||
Please suggest relationships model
So, after reading a bit, I managed to come up with this model
http://xs317.xs.to/xs317/07311/rel.JPG That kind of does the trick... I'm just missing one little detail here, and I'm kind of stuck I ran the wizard to create a form using the tblTransfers table as source, came out like this: http://xs317.xs.to/xs317/07311/fTransfers.JPG The only problem here is that one of my transactions may include several items. How would I accommodate that in this form? Do I need maybe an additional table to hold more information? if so, what kind of information would this contain? Thanks again -- Moe |
#32
|
|||
|
|||
Please suggest relationships model
Moe,
The image of you relationship model seems slightly different then what you origially asked for. Where does tblWire fit in the original design? You can skip past this and get your answer located at the bottom of this response, I am just adding my 2 cents regarding your table design. For table design you might want to consider (I may not have taken into account all the fields you may actually need): tblInventory iInventoryID (Primary Key) iDescription iQuantityOnHand iCost iVendorID (Foreign Key) - Only to be used if Vendors other than yourself tblProjects pProjectID (Primary Key) pDate pDescription pAddress1 pAddress2 pCityID pZipCodeID pPhoneNumber pFaxNumber pMainContact tblTransactions (This set-up is if you using Tranasactions agaist Projects) tTransactionsID (Primary Key) tDate tInventoryID (Foreign Key) tProjectID (Foreign Key) tQuantity (Which is QuantitySold or Purchased) If there is a Vendor then add this table tblVendor vVendorID (Primary Key) vCompanyName vAddress1 vAddress2 vCityID vStateID vZipCodeID vPhoneNumber vFaxNumber vMainContact vWebpage But to answer your question.. You will need a subform that would attach to the form you already created (One-to-Many) so you can see the transactions as they relate to the one project. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... So, after reading a bit, I managed to come up with this model http://xs317.xs.to/xs317/07311/rel.JPG That kind of does the trick... I'm just missing one little detail here, and I'm kind of stuck I ran the wizard to create a form using the tblTransfers table as source, came out like this: http://xs317.xs.to/xs317/07311/fTransfers.JPG The only problem here is that one of my transactions may include several items. How would I accommodate that in this form? Do I need maybe an additional table to hold more information? if so, what kind of information would this contain? Thanks again -- Moe |
#33
|
|||
|
|||
Please suggest relationships model
The tblWire is just like the inventory table... I named it like that because
the inventory is mainly wire. -- Moe "Gina Whipp" wrote: Moe, The image of you relationship model seems slightly different then what you origially asked for. Where does tblWire fit in the original design? You can skip past this and get your answer located at the bottom of this response, I am just adding my 2 cents regarding your table design. For table design you might want to consider (I may not have taken into account all the fields you may actually need): tblInventory iInventoryID (Primary Key) iDescription iQuantityOnHand iCost iVendorID (Foreign Key) - Only to be used if Vendors other than yourself tblProjects pProjectID (Primary Key) pDate pDescription pAddress1 pAddress2 pCityID pZipCodeID pPhoneNumber pFaxNumber pMainContact tblTransactions (This set-up is if you using Tranasactions agaist Projects) tTransactionsID (Primary Key) tDate tInventoryID (Foreign Key) tProjectID (Foreign Key) tQuantity (Which is QuantitySold or Purchased) If there is a Vendor then add this table tblVendor vVendorID (Primary Key) vCompanyName vAddress1 vAddress2 vCityID vStateID vZipCodeID vPhoneNumber vFaxNumber vMainContact vWebpage But to answer your question.. You will need a subform that would attach to the form you already created (One-to-Many) so you can see the transactions as they relate to the one project. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... So, after reading a bit, I managed to come up with this model http://xs317.xs.to/xs317/07311/rel.JPG That kind of does the trick... I'm just missing one little detail here, and I'm kind of stuck I ran the wizard to create a form using the tblTransfers table as source, came out like this: http://xs317.xs.to/xs317/07311/fTransfers.JPG The only problem here is that one of my transactions may include several items. How would I accommodate that in this form? Do I need maybe an additional table to hold more information? if so, what kind of information would this contain? Thanks again -- Moe |
#34
|
|||
|
|||
Please suggest relationships model
Okay then you could make the transfer form you made the subform and the jobs
table the main form. Then you would see the one job to the many transfers. Is that what you had in mind? -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... The tblWire is just like the inventory table... I named it like that because the inventory is mainly wire. -- Moe "Gina Whipp" wrote: Moe, The image of you relationship model seems slightly different then what you origially asked for. Where does tblWire fit in the original design? You can skip past this and get your answer located at the bottom of this response, I am just adding my 2 cents regarding your table design. For table design you might want to consider (I may not have taken into account all the fields you may actually need): tblInventory iInventoryID (Primary Key) iDescription iQuantityOnHand iCost iVendorID (Foreign Key) - Only to be used if Vendors other than yourself tblProjects pProjectID (Primary Key) pDate pDescription pAddress1 pAddress2 pCityID pZipCodeID pPhoneNumber pFaxNumber pMainContact tblTransactions (This set-up is if you using Tranasactions agaist Projects) tTransactionsID (Primary Key) tDate tInventoryID (Foreign Key) tProjectID (Foreign Key) tQuantity (Which is QuantitySold or Purchased) If there is a Vendor then add this table tblVendor vVendorID (Primary Key) vCompanyName vAddress1 vAddress2 vCityID vStateID vZipCodeID vPhoneNumber vFaxNumber vMainContact vWebpage But to answer your question.. You will need a subform that would attach to the form you already created (One-to-Many) so you can see the transactions as they relate to the one project. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... So, after reading a bit, I managed to come up with this model http://xs317.xs.to/xs317/07311/rel.JPG That kind of does the trick... I'm just missing one little detail here, and I'm kind of stuck I ran the wizard to create a form using the tblTransfers table as source, came out like this: http://xs317.xs.to/xs317/07311/fTransfers.JPG The only problem here is that one of my transactions may include several items. How would I accommodate that in this form? Do I need maybe an additional table to hold more information? if so, what kind of information would this contain? Thanks again -- Moe |
#35
|
|||
|
|||
Please suggest relationships model
Yes... that's exactly it...
Thanks for your help I also need to create a report to display the same information. I'll mess with that later I managed to get the form right... allows me to do one transaction, with many lines in it (one item per line)... That works right... My main problem at the momment is that mathematical calculations that need to be done.. For example, in tblInventory, I have a QOH field that shows how many of each item I have in stock. In this form, I can pick from a list box the item that I'm shipping out to the job, and I need to enter a quantity (quantity shipped). So, I need it to subtract the quantity shipped from the QOH field in tblInventory. This may get a little complicated. I'm not sure I know enough make this work. -- Moe "Gina Whipp" wrote: Okay then you could make the transfer form you made the subform and the jobs table the main form. Then you would see the one job to the many transfers. Is that what you had in mind? -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... The tblWire is just like the inventory table... I named it like that because the inventory is mainly wire. -- Moe "Gina Whipp" wrote: Moe, The image of you relationship model seems slightly different then what you origially asked for. Where does tblWire fit in the original design? You can skip past this and get your answer located at the bottom of this response, I am just adding my 2 cents regarding your table design. For table design you might want to consider (I may not have taken into account all the fields you may actually need): tblInventory iInventoryID (Primary Key) iDescription iQuantityOnHand iCost iVendorID (Foreign Key) - Only to be used if Vendors other than yourself tblProjects pProjectID (Primary Key) pDate pDescription pAddress1 pAddress2 pCityID pZipCodeID pPhoneNumber pFaxNumber pMainContact tblTransactions (This set-up is if you using Tranasactions agaist Projects) tTransactionsID (Primary Key) tDate tInventoryID (Foreign Key) tProjectID (Foreign Key) tQuantity (Which is QuantitySold or Purchased) If there is a Vendor then add this table tblVendor vVendorID (Primary Key) vCompanyName vAddress1 vAddress2 vCityID vStateID vZipCodeID vPhoneNumber vFaxNumber vMainContact vWebpage But to answer your question.. You will need a subform that would attach to the form you already created (One-to-Many) so you can see the transactions as they relate to the one project. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... So, after reading a bit, I managed to come up with this model http://xs317.xs.to/xs317/07311/rel.JPG That kind of does the trick... I'm just missing one little detail here, and I'm kind of stuck I ran the wizard to create a form using the tblTransfers table as source, came out like this: http://xs317.xs.to/xs317/07311/fTransfers.JPG The only problem here is that one of my transactions may include several items. How would I accommodate that in this form? Do I need maybe an additional table to hold more information? if so, what kind of information would this contain? Thanks again -- Moe |
#36
|
|||
|
|||
Please suggest relationships model
Moe,
You might want to consider an update query that runs after the transaction is processed. It would run on the form unload event. I do't thik I would would ru after the field is updated incase you change the quantity and the it would be more complicated as you would have retain the QOH each time the Quantity shipped was changed. The first way I suggested only changes it whe you exit the form thereby you could chage the Quantity 50 times ad the QOH is oly updated once. I would also suggest you start a new thread for that when you are ready, some folks here might have a better way. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... Yes... that's exactly it... Thanks for your help I also need to create a report to display the same information. I'll mess with that later I managed to get the form right... allows me to do one transaction, with many lines in it (one item per line)... That works right... My main problem at the momment is that mathematical calculations that need to be done.. For example, in tblInventory, I have a QOH field that shows how many of each item I have in stock. In this form, I can pick from a list box the item that I'm shipping out to the job, and I need to enter a quantity (quantity shipped). So, I need it to subtract the quantity shipped from the QOH field in tblInventory. This may get a little complicated. I'm not sure I know enough make this work. -- Moe "Gina Whipp" wrote: Okay then you could make the transfer form you made the subform and the jobs table the main form. Then you would see the one job to the many transfers. Is that what you had in mind? -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... The tblWire is just like the inventory table... I named it like that because the inventory is mainly wire. -- Moe "Gina Whipp" wrote: Moe, The image of you relationship model seems slightly different then what you origially asked for. Where does tblWire fit in the original design? You can skip past this and get your answer located at the bottom of this response, I am just adding my 2 cents regarding your table design. For table design you might want to consider (I may not have taken into account all the fields you may actually need): tblInventory iInventoryID (Primary Key) iDescription iQuantityOnHand iCost iVendorID (Foreign Key) - Only to be used if Vendors other than yourself tblProjects pProjectID (Primary Key) pDate pDescription pAddress1 pAddress2 pCityID pZipCodeID pPhoneNumber pFaxNumber pMainContact tblTransactions (This set-up is if you using Tranasactions agaist Projects) tTransactionsID (Primary Key) tDate tInventoryID (Foreign Key) tProjectID (Foreign Key) tQuantity (Which is QuantitySold or Purchased) If there is a Vendor then add this table tblVendor vVendorID (Primary Key) vCompanyName vAddress1 vAddress2 vCityID vStateID vZipCodeID vPhoneNumber vFaxNumber vMainContact vWebpage But to answer your question.. You will need a subform that would attach to the form you already created (One-to-Many) so you can see the transactions as they relate to the one project. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... So, after reading a bit, I managed to come up with this model http://xs317.xs.to/xs317/07311/rel.JPG That kind of does the trick... I'm just missing one little detail here, and I'm kind of stuck I ran the wizard to create a form using the tblTransfers table as source, came out like this: http://xs317.xs.to/xs317/07311/fTransfers.JPG The only problem here is that one of my transactions may include several items. How would I accommodate that in this form? Do I need maybe an additional table to hold more information? if so, what kind of information would this contain? Thanks again -- Moe |
#37
|
|||
|
|||
Please suggest relationships model
I'm afraid queries are a whole different ball game for me...
but, I'll give it a shot. I'll post back if I dont give up on it -- Moe "Gina Whipp" wrote: Moe, You might want to consider an update query that runs after the transaction is processed. It would run on the form unload event. I do't thik I would would ru after the field is updated incase you change the quantity and the it would be more complicated as you would have retain the QOH each time the Quantity shipped was changed. The first way I suggested only changes it whe you exit the form thereby you could chage the Quantity 50 times ad the QOH is oly updated once. I would also suggest you start a new thread for that when you are ready, some folks here might have a better way. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... Yes... that's exactly it... Thanks for your help I also need to create a report to display the same information. I'll mess with that later I managed to get the form right... allows me to do one transaction, with many lines in it (one item per line)... That works right... My main problem at the momment is that mathematical calculations that need to be done.. For example, in tblInventory, I have a QOH field that shows how many of each item I have in stock. In this form, I can pick from a list box the item that I'm shipping out to the job, and I need to enter a quantity (quantity shipped). So, I need it to subtract the quantity shipped from the QOH field in tblInventory. This may get a little complicated. I'm not sure I know enough make this work. -- Moe "Gina Whipp" wrote: Okay then you could make the transfer form you made the subform and the jobs table the main form. Then you would see the one job to the many transfers. Is that what you had in mind? -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... The tblWire is just like the inventory table... I named it like that because the inventory is mainly wire. -- Moe "Gina Whipp" wrote: Moe, The image of you relationship model seems slightly different then what you origially asked for. Where does tblWire fit in the original design? You can skip past this and get your answer located at the bottom of this response, I am just adding my 2 cents regarding your table design. For table design you might want to consider (I may not have taken into account all the fields you may actually need): tblInventory iInventoryID (Primary Key) iDescription iQuantityOnHand iCost iVendorID (Foreign Key) - Only to be used if Vendors other than yourself tblProjects pProjectID (Primary Key) pDate pDescription pAddress1 pAddress2 pCityID pZipCodeID pPhoneNumber pFaxNumber pMainContact tblTransactions (This set-up is if you using Tranasactions agaist Projects) tTransactionsID (Primary Key) tDate tInventoryID (Foreign Key) tProjectID (Foreign Key) tQuantity (Which is QuantitySold or Purchased) If there is a Vendor then add this table tblVendor vVendorID (Primary Key) vCompanyName vAddress1 vAddress2 vCityID vStateID vZipCodeID vPhoneNumber vFaxNumber vMainContact vWebpage But to answer your question.. You will need a subform that would attach to the form you already created (One-to-Many) so you can see the transactions as they relate to the one project. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Moe" wrote in message ... So, after reading a bit, I managed to come up with this model http://xs317.xs.to/xs317/07311/rel.JPG That kind of does the trick... I'm just missing one little detail here, and I'm kind of stuck I ran the wizard to create a form using the tblTransfers table as source, came out like this: http://xs317.xs.to/xs317/07311/fTransfers.JPG The only problem here is that one of my transactions may include several items. How would I accommodate that in this form? Do I need maybe an additional table to hold more information? if so, what kind of information would this contain? Thanks again -- Moe |
Thread Tools | |
Display Modes | |
|
|