|
| 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
January 7th, 2009, 02:35 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Database design Reccomendation
I need to track pieces (2 types) shipped on a daily/monthly/yearly comparable
basis for two groups groupA & GroupB. More specifically, I will be comparing both groups individually on a historical basis, in addition to each groups daily/monthly/yearly relationship to the number of pieces shipped with respect to ProductA & productB Also, the data needs to be input manually, as the system that ships these pieces is a seperate outside company ie Purolator/Fedex, so I cannot "datamine' the info. There are only two "products" which need to be tracked. We need to be able to produce reports on an individual group basis, and combined Group basis: Also, Daily shipping compared over the last "x" years with percentage increase/decrease Monthly "" "" "" Yearly "" "" "" I am thinking the following for Table layouts: tblGroup GroupID (autonumber) GroupA GroupB tblProduct ProductID (autonumber) product1 product2 I am not sure what to do with the date fields? Should I have a Day table, a month table, and a year table? I am thinking I would use a dependent combobox date form to select the date (The reason I am thinking of doing this this way is The year, for example, would determine whether or not Feb has 29 days, in addition to, if I choose December from the first combobox, the second combo box would show 31 days) With respect to the above, I would like the year to default to the current year (we are still updating previous years data) but am unsure how to do this. Also, I would like a warning if there is already data stored for a specific day to make the data input person aware they may be entering informaton in on the wrong date. Any help would be sincerely appreciated! Thanks, Bill 
|
|
#2
January 7th, 2009, 03:30 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database design Reccomendation
I don't understand what your "transactions" will look like. I can only assume
you need a table of Groups with fields like: tblGroups (initially only 2 records) =========== GroupID PK Autonumber GroupName You would have a similar table for Products tblProducts (initially only 2 records) =========== ProductID PK Autonumber ProductName The final table would be the transaction tblShipments ============ ShipID PK Autonumber ProductID GroupID ShipDate PiecesShipped -- Duane Hookom Microsoft Access MVP "Billiam" wrote: I need to track pieces (2 types) shipped on a daily/monthly/yearly comparable basis for two groups groupA & GroupB. More specifically, I will be comparing both groups individually on a historical basis, in addition to each groups daily/monthly/yearly relationship to the number of pieces shipped with respect to ProductA & productB Also, the data needs to be input manually, as the system that ships these pieces is a seperate outside company ie Purolator/Fedex, so I cannot "datamine' the info. There are only two "products" which need to be tracked. We need to be able to produce reports on an individual group basis, and combined Group basis: Also, Daily shipping compared over the last "x" years with percentage increase/decrease Monthly "" "" "" Yearly "" "" "" I am thinking the following for Table layouts: tblGroup GroupID (autonumber) GroupA GroupB tblProduct ProductID (autonumber) product1 product2 I am not sure what to do with the date fields? Should I have a Day table, a month table, and a year table? I am thinking I would use a dependent combobox date form to select the date (The reason I am thinking of doing this this way is The year, for example, would determine whether or not Feb has 29 days, in addition to, if I choose December from the first combobox, the second combo box would show 31 days) With respect to the above, I would like the year to default to the current year (we are still updating previous years data) but am unsure how to do this. Also, I would like a warning if there is already data stored for a specific day to make the data input person aware they may be entering informaton in on the wrong date. Any help would be sincerely appreciated! Thanks, Bill
|
|
#3
January 7th, 2009, 03:49 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database design Reccomendation
Sorry, I should have been more specific about the relationships and setup.
Yes I have three tables like yours, except I did not know what to do about the date. BTW, I have made the necessary many to many relationship table utilizing the PK's of the Group and Product tables. So, Duane, from a form point of view for data entry, I wanted to avoid data entry errors by limiting the date field to established dates i.e February only has 28 days except in a Leap year', and i thought I would have to use dependent comboboxes to do this. Am I overcomplicating this? Also, if I follow what you have prposed here for date, how would I set my form up? Would I use dependent comboboxes for group and product and shipped date? Thanks again for your input and help. Bill "Duane Hookom" wrote: I don't understand what your "transactions" will look like. I can only assume you need a table of Groups with fields like: tblGroups (initially only 2 records) =========== GroupID PK Autonumber GroupName You would have a similar table for Products tblProducts (initially only 2 records) =========== ProductID PK Autonumber ProductName The final table would be the transaction tblShipments ============ ShipID PK Autonumber ProductID GroupID ShipDate PiecesShipped -- Duane Hookom Microsoft Access MVP "Billiam" wrote: I need to track pieces (2 types) shipped on a daily/monthly/yearly comparable basis for two groups groupA & GroupB. More specifically, I will be comparing both groups individually on a historical basis, in addition to each groups daily/monthly/yearly relationship to the number of pieces shipped with respect to ProductA & productB Also, the data needs to be input manually, as the system that ships these pieces is a seperate outside company ie Purolator/Fedex, so I cannot "datamine' the info. There are only two "products" which need to be tracked. We need to be able to produce reports on an individual group basis, and combined Group basis: Also, Daily shipping compared over the last "x" years with percentage increase/decrease Monthly "" "" "" Yearly "" "" "" I am thinking the following for Table layouts: tblGroup GroupID (autonumber) GroupA GroupB tblProduct ProductID (autonumber) product1 product2 I am not sure what to do with the date fields? Should I have a Day table, a month table, and a year table? I am thinking I would use a dependent combobox date form to select the date (The reason I am thinking of doing this this way is The year, for example, would determine whether or not Feb has 29 days, in addition to, if I choose December from the first combobox, the second combo box would show 31 days) With respect to the above, I would like the year to default to the current year (we are still updating previous years data) but am unsure how to do this. Also, I would like a warning if there is already data stored for a specific day to make the data input person aware they may be entering informaton in on the wrong date. Any help would be sincerely appreciated! Thanks, Bill
|
|
#4
January 7th, 2009, 04:25 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database design Reccomendation
Also, just to be clear on the transactions, I need to detail which group sent
which product(s) on which date and what percentage of the total shipments (ie there are shipments which are neither ProductA or ProductB on any given day. In other words, I need to know the percentage of productA to the total shipments for the day. Should i set up a ProductOther group...really it is the leftover amount of the whole, and the data input perosn would have to compute that which I do not want. Hope that clarifies things a little. Bill "Duane Hookom" wrote: I don't understand what your "transactions" will look like. I can only assume you need a table of Groups with fields like: tblGroups (initially only 2 records) =========== GroupID PK Autonumber GroupName You would have a similar table for Products tblProducts (initially only 2 records) =========== ProductID PK Autonumber ProductName The final table would be the transaction tblShipments ============ ShipID PK Autonumber ProductID GroupID ShipDate PiecesShipped -- Duane Hookom Microsoft Access MVP "Billiam" wrote: I need to track pieces (2 types) shipped on a daily/monthly/yearly comparable basis for two groups groupA & GroupB. More specifically, I will be comparing both groups individually on a historical basis, in addition to each groups daily/monthly/yearly relationship to the number of pieces shipped with respect to ProductA & productB Also, the data needs to be input manually, as the system that ships these pieces is a seperate outside company ie Purolator/Fedex, so I cannot "datamine' the info. There are only two "products" which need to be tracked. We need to be able to produce reports on an individual group basis, and combined Group basis: Also, Daily shipping compared over the last "x" years with percentage increase/decrease Monthly "" "" "" Yearly "" "" "" I am thinking the following for Table layouts: tblGroup GroupID (autonumber) GroupA GroupB tblProduct ProductID (autonumber) product1 product2 I am not sure what to do with the date fields? Should I have a Day table, a month table, and a year table? I am thinking I would use a dependent combobox date form to select the date (The reason I am thinking of doing this this way is The year, for example, would determine whether or not Feb has 29 days, in addition to, if I choose December from the first combobox, the second combo box would show 31 days) With respect to the above, I would like the year to default to the current year (we are still updating previous years data) but am unsure how to do this. Also, I would like a warning if there is already data stored for a specific day to make the data input person aware they may be entering informaton in on the wrong date. Any help would be sincerely appreciated! Thanks, Bill
|
|
#6
January 7th, 2009, 05:42 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database design Reccomendation
Steve,
Good reply until your advertising. I don't understand why you can't behave like nearly every other participant in these news groups. I don't recall seeing anyone else try to blantantly sell their services like you do. Billiam, If you want to ensure data entry integrity, use combo boxes for groups and products. I recommend using a pop-up calendar form for dates. If you search the web for "Access calendar form", you should find lots of good ones. I think the one from Stephen Lebans at www.lebans.com is highly recommended. -- Duane Hookom Microsoft Access MVP "Steve" wrote: Hi Bill, Start with these tables: TblGroup GroupID GroupName (GroupA, GroupB) TblProduct ProductID ProductName (ProductA, ProductB, Other Product) TblProductShipment ProductShipmentID GroupID ProductID ShipmentDate Quantity Total shipped on any date = Sum(Quantity on the ShipmentDate) Percentage for a product on any date = Total / Quantity for the product Total shipped on any date by a Group = Sum(Quantity on the ShipmentDate for the group) Percentage for a group on any date = Total shipped by group / Total shipped on any date Bill, if you want to save yourself frustration and save time, I can set this up for you for a modest fee. Contact me at if you want help. Steve "Billiam" wrote in message news  Also, just to be clear on the transactions, I need to detail which group sent which product(s) on which date and what percentage of the total shipments (ie there are shipments which are neither ProductA or ProductB on any given day. In other words, I need to know the percentage of productA to the total shipments for the day. Should i set up a ProductOther group...really it is the leftover amount of the whole, and the data input perosn would have to compute that which I do not want. Hope that clarifies things a little. Bill "Duane Hookom" wrote: I don't understand what your "transactions" will look like. I can only assume you need a table of Groups with fields like: tblGroups (initially only 2 records) =========== GroupID PK Autonumber GroupName You would have a similar table for Products tblProducts (initially only 2 records) =========== ProductID PK Autonumber ProductName The final table would be the transaction tblShipments ============ ShipID PK Autonumber ProductID GroupID ShipDate PiecesShipped -- Duane Hookom Microsoft Access MVP "Billiam" wrote: I need to track pieces (2 types) shipped on a daily/monthly/yearly comparable basis for two groups groupA & GroupB. More specifically, I will be comparing both groups individually on a historical basis, in addition to each groups daily/monthly/yearly relationship to the number of pieces shipped with respect to ProductA & productB Also, the data needs to be input manually, as the system that ships these pieces is a seperate outside company ie Purolator/Fedex, so I cannot "datamine' the info. There are only two "products" which need to be tracked. We need to be able to produce reports on an individual group basis, and combined Group basis: Also, Daily shipping compared over the last "x" years with percentage increase/decrease Monthly "" "" "" Yearly "" "" "" I am thinking the following for Table layouts: tblGroup GroupID (autonumber) GroupA GroupB tblProduct ProductID (autonumber) product1 product2 I am not sure what to do with the date fields? Should I have a Day table, a month table, and a year table? I am thinking I would use a dependent combobox date form to select the date (The reason I am thinking of doing this this way is The year, for example, would determine whether or not Feb has 29 days, in addition to, if I choose December from the first combobox, the second combo box would show 31 days) With respect to the above, I would like the year to default to the current year (we are still updating previous years data) but am unsure how to do this. Also, I would like a warning if there is already data stored for a specific day to make the data input person aware they may be entering informaton in on the wrong date. Any help would be sincerely appreciated! Thanks, Bill
|
|
#7
January 7th, 2009, 05:54 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Stevie the sleaze is soliciting again!
"Steve" wrote in message
m... Hi Bill, Bill, if you want to save yourself frustration and save time, I can set this up for you for a modest fee. Contact me at if you want help. Steve These newsgroups are provided by Microsoft for FREE peer to peer support. Stevie is a known troll who likes to harrass posters for work. If you WANT frustration and waste time, stevie will gladly set you up for disappointment. John... Visio MVP
|
|
#8
January 7th, 2009, 06:51 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database design Reccomendation
"Steve" schreef in bericht m... Bill, if you want to save yourself frustration and save time, I can set this up for you for a modest fee. Contact me at if you want help. Steve Bill, you better don't use Steve's 'services'... he is a liar and a cheat. Read this first.... http://home.tiscali.nl/arracom/whoissteve.html Regards, Arno R
|
|
#9
January 7th, 2009, 06:52 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database design Reccomendation
On Wed, 7 Jan 2009 07:49:01 -0800, Billiam
wrote: I wanted to avoid data entry errors by limiting the date field to established dates i.e February only has 28 days except in a Leap year', and i thought I would have to use dependent comboboxes to do this. Am I overcomplicating this? Yes. Access parses dates very cleverly. If you type 2/30/09 into a date field you'll get a beep and an error message; the error is trappable if you want to give the user an even friendlier (or more informative) message. -- John W. Vinson [MVP]
|
|
#10
January 8th, 2009, 12:05 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database design Reccomendation
I don't know if you are one of them but more than a dozen "regulars" mostly
MVPs are privately emailing posters in the newsgroups offering to help them with their problem for a fee. They have found a way to somehow get the posters' email address when the poster posts his question in the newsgroup. I don't do that! I only selectively offer to help a poster who seems to really need help if he contacts me. Apparently these "regulars" (MVPs) don't want any competition. Steve "Duane Hookom" wrote in message ... Steve, Good reply until your advertising. I don't understand why you can't behave like nearly every other participant in these news groups. I don't recall seeing anyone else try to blantantly sell their services like you do. Billiam, If you want to ensure data entry integrity, use combo boxes for groups and products. I recommend using a pop-up calendar form for dates. If you search the web for "Access calendar form", you should find lots of good ones. I think the one from Stephen Lebans at www.lebans.com is highly recommended. -- Duane Hookom Microsoft Access MVP "Steve" wrote: Hi Bill, Start with these tables: TblGroup GroupID GroupName (GroupA, GroupB) TblProduct ProductID ProductName (ProductA, ProductB, Other Product) TblProductShipment ProductShipmentID GroupID ProductID ShipmentDate Quantity Total shipped on any date = Sum(Quantity on the ShipmentDate) Percentage for a product on any date = Total / Quantity for the product Total shipped on any date by a Group = Sum(Quantity on the ShipmentDate for the group) Percentage for a group on any date = Total shipped by group / Total shipped on any date Bill, if you want to save yourself frustration and save time, I can set this up for you for a modest fee. Contact me at if you want help. Steve "Billiam" wrote in message news Also, just to be clear on the transactions, I need to detail which group sent which product(s) on which date and what percentage of the total shipments (ie there are shipments which are neither ProductA or ProductB on any given day. In other words, I need to know the percentage of productA to the total shipments for the day. Should i set up a ProductOther group...really it is the leftover amount of the whole, and the data input perosn would have to compute that which I do not want. Hope that clarifies things a little. Bill "Duane Hookom" wrote: I don't understand what your "transactions" will look like. I can only assume you need a table of Groups with fields like: tblGroups (initially only 2 records) =========== GroupID PK Autonumber GroupName You would have a similar table for Products tblProducts (initially only 2 records) =========== ProductID PK Autonumber ProductName The final table would be the transaction tblShipments ============ ShipID PK Autonumber ProductID GroupID ShipDate PiecesShipped -- Duane Hookom Microsoft Access MVP "Billiam" wrote: I need to track pieces (2 types) shipped on a daily/monthly/yearly comparable basis for two groups groupA & GroupB. More specifically, I will be comparing both groups individually on a historical basis, in addition to each groups daily/monthly/yearly relationship to the number of pieces shipped with respect to ProductA & productB Also, the data needs to be input manually, as the system that ships these pieces is a seperate outside company ie Purolator/Fedex, so I cannot "datamine' the info. There are only two "products" which need to be tracked. We need to be able to produce reports on an individual group basis, and combined Group basis: Also, Daily shipping compared over the last "x" years with percentage increase/decrease Monthly "" "" "" Yearly "" "" "" I am thinking the following for Table layouts: tblGroup GroupID (autonumber) GroupA GroupB tblProduct ProductID (autonumber) product1 product2 I am not sure what to do with the date fields? Should I have a Day table, a month table, and a year table? I am thinking I would use a dependent combobox date form to select the date (The reason I am thinking of doing this this way is The year, for example, would determine whether or not Feb has 29 days, in addition to, if I choose December from the first combobox, the second combo box would show 31 days) With respect to the above, I would like the year to default to the current year (we are still updating previous years data) but am unsure how to do this. Also, I would like a warning if there is already data stored for a specific day to make the data input person aware they may be entering informaton in on the wrong date. Any help would be sincerely appreciated! Thanks, Bill
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
