I do not know if this is within the scope of this forum, but I would like some design assistance for an inventory subsystem that I am trying to build.
I have eight fields that I have to keep track of and sum for reports and forms. They are mainly for PIT Inventory as well as total toys distributed.
The fields are:
cutouts
who created the cutouts
completed unfinished toys
completed toys that are finished
cutout rejects
completed unfinished rejects
completed finished rejects
distributed unfinished toys
recipient of the toys
client contact for the recipient
distributed finished toys
recipient of the toys
client contact for the recipient
I may have several different entries for a day or only one entry for a day. A no entry day is not a problem. I may have several of the same entry for a day.
The indented items are foreign keys imbedded in the record.
This is a pro-bono effort for a local charity (http://www.HappyFactoryLasVegas.org" onclick="window.open(this.href);return false;).I would appreciate any suggestions of how best to do this in Access 2003 with VBA.
TIA
Request for Design Assistance
-
- 2StarLounger
- Posts: 125
- Joined: 17 Feb 2010, 16:25
Request for Design Assistance
PaulW
Lost Wages, NV USA
(former Cobol Programmer)
Lost Wages, NV USA
(former Cobol Programmer)
-
- 2StarLounger
- Posts: 125
- Joined: 17 Feb 2010, 16:25
Re: Request for Design Assistance
Please let me clarify:
"Finished" means that the completed/assembled toys have a mineral oil coating.
"Unfinished" means that the completed/assembled toys are raw wood without a coating.
"Finished" means that the completed/assembled toys have a mineral oil coating.
"Unfinished" means that the completed/assembled toys are raw wood without a coating.
PaulW
Lost Wages, NV USA
(former Cobol Programmer)
Lost Wages, NV USA
(former Cobol Programmer)
-
- Administrator
- Posts: 78483
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Request for Design Assistance
Your question is a bit vague - "design assistance" is very broad. Could you indicate in some detail what you want help with?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 125
- Joined: 17 Feb 2010, 16:25
Re: Request for Design Assistance
Hi Hans,
Sorry I was so vague. I am looking for suggestions for table structures.
My better half, Carol W., thought of three tables as follows:
tblPreAssemblyPieces
Date
Manufacturer id
NumberofCutouts
tblDistribution
DateDistributed
ClientId
ClientContact
NbrFinishedToysDistributed
NbrUnfinishedToysDistributed
tblAssembledInventory
Date
NbrCompletedUnfinishedToys
NbrCompletedFinishedToys
NbrRejectsUnassembled
NbrRejectsUnfinished
NbrRejectsFinished
The goal is to be able to calculate Nbr of Work In Process Pieces, Nbr Remaining in Inventory, Nbr Toys distributed. This will be displayed on a form when the inventory menu is opened. It will be refreshed when data is entered into any of the tables.
Do you think the above table definitions would allow me to calculate the required numbers easily? If not, your suggestions for alternative table structures would be most appreciated.
Sorry I was so vague. I am looking for suggestions for table structures.
My better half, Carol W., thought of three tables as follows:
tblPreAssemblyPieces
Date
Manufacturer id
NumberofCutouts
tblDistribution
DateDistributed
ClientId
ClientContact
NbrFinishedToysDistributed
NbrUnfinishedToysDistributed
tblAssembledInventory
Date
NbrCompletedUnfinishedToys
NbrCompletedFinishedToys
NbrRejectsUnassembled
NbrRejectsUnfinished
NbrRejectsFinished
The goal is to be able to calculate Nbr of Work In Process Pieces, Nbr Remaining in Inventory, Nbr Toys distributed. This will be displayed on a form when the inventory menu is opened. It will be refreshed when data is entered into any of the tables.
Do you think the above table definitions would allow me to calculate the required numbers easily? If not, your suggestions for alternative table structures would be most appreciated.
PaulW
Lost Wages, NV USA
(former Cobol Programmer)
Lost Wages, NV USA
(former Cobol Programmer)
-
- Administrator
- Posts: 78483
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Request for Design Assistance
This looks like a good start.
I have an additional question. Is there only one type of toy (that can be "finished" or "unfinished")? If not, you'll probably need a fourth table that lists the types of toys, with a primary key ToyID that should also be in the other tables.
BTW, inventory databases are not my strong point, to put it very mildly. I hope that someone who has more experience with them will reply too.
I have an additional question. Is there only one type of toy (that can be "finished" or "unfinished")? If not, you'll probably need a fourth table that lists the types of toys, with a primary key ToyID that should also be in the other tables.
BTW, inventory databases are not my strong point, to put it very mildly. I hope that someone who has more experience with them will reply too.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 125
- Joined: 17 Feb 2010, 16:25
Re: Request for Design Assistance
Thanks for the reply. So far as the inventory system is concerned, a toy is a toy is a toy is a toy. No differentiation.
Carol straightened me out on when an update takes place and a record - errrr - row is inserted . So I will wait to see if anyone has any other ideas and then start coding. Your reply has me going in the correct direction
Carol straightened me out on when an update takes place and a record - errrr - row is inserted . So I will wait to see if anyone has any other ideas and then start coding. Your reply has me going in the correct direction
PaulW
Lost Wages, NV USA
(former Cobol Programmer)
Lost Wages, NV USA
(former Cobol Programmer)
-
- Her Majesty
- Posts: 499
- Joined: 19 Jan 2010, 07:13
Re: Request for Design Assistance
In my experience, that only last as long as it takes for someone to decide they need statistics based on type of toy. It wouldn't hurt to add that refinement now before you need it so you can look like a hero later.PaulW wrote:So far as the inventory system is concerned, a toy is a toy is a toy is a toy.
If you really wanted to be thorough, you'd break the granularity of the data down further so you had one record for each toy in your inventory which would include a boolean field for Finished/unfinished rather than two numeric fields in a summary record. That would allow you to quickly summarize finished and unfinished toys delivered to a particular client on any given date you like.
If you provide a bit more information about how the database is intended to be used, some of us may be able to offer more suggestions.
PS/ you also need a table for Clients, to store address, contact persion, etc.
Charlotte
-
- 2StarLounger
- Posts: 125
- Joined: 17 Feb 2010, 16:25
Re: Request for Design Assistance
Hi Charlotte,
Thanks so much for the detailed thought effort you put into my "little" project.
I have asked the manager of the Happy Factory and he has told me that the toys are delivered by the box (and the counts are really approximate) and they have no intention of drilling down for the type of granularity you mention, this may all change in the future .
The basic use of the data is to keep track of volunteers creating wooden toys from scrap hardwood and spare time. The other purpose is to keep track of the inventory so that when a request for toys comes in, the manager has an approximate count of inventory available. All the labor, including mine, is volunteer. Almost all the supplies are donated by various businesses in and around Las Vegas, NV.
When a potential donor wants to see what has been done and what is being done before making a donation of money or goods or services, now they have to open an Excel spreadsheet. It will be more polished to go to a computer station and bring up the Inventory System and show any any all the detail that the donor may wish to see.
In so far as a client table is concerned, we have a "People Table" to keep all entities entered only once so that third normal form may be maintained with a check box control to indicate which category or categories the entity is a member. This allows an entity to be a volunteer, a donor, a client contact, etc (or many of these). The contact person (a checkbox control)is a foreign key within a client record (within the same table).
Thanks so much for the detailed thought effort you put into my "little" project.
I have asked the manager of the Happy Factory and he has told me that the toys are delivered by the box (and the counts are really approximate) and they have no intention of drilling down for the type of granularity you mention, this may all change in the future .
The basic use of the data is to keep track of volunteers creating wooden toys from scrap hardwood and spare time. The other purpose is to keep track of the inventory so that when a request for toys comes in, the manager has an approximate count of inventory available. All the labor, including mine, is volunteer. Almost all the supplies are donated by various businesses in and around Las Vegas, NV.
When a potential donor wants to see what has been done and what is being done before making a donation of money or goods or services, now they have to open an Excel spreadsheet. It will be more polished to go to a computer station and bring up the Inventory System and show any any all the detail that the donor may wish to see.
In so far as a client table is concerned, we have a "People Table" to keep all entities entered only once so that third normal form may be maintained with a check box control to indicate which category or categories the entity is a member. This allows an entity to be a volunteer, a donor, a client contact, etc (or many of these). The contact person (a checkbox control)is a foreign key within a client record (within the same table).
PaulW
Lost Wages, NV USA
(former Cobol Programmer)
Lost Wages, NV USA
(former Cobol Programmer)