Request for Design Assistance

PaulW
2StarLounger
Posts: 125
Joined: 17 Feb 2010, 16:25

Request for Design Assistance

Post by PaulW »

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 :thankyou:
PaulW
Lost Wages, NV USA
(former Cobol Programmer)

PaulW
2StarLounger
Posts: 125
Joined: 17 Feb 2010, 16:25

Re: Request for Design Assistance

Post by PaulW »

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.
PaulW
Lost Wages, NV USA
(former Cobol Programmer)

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Request for Design Assistance

Post by HansV »

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

PaulW
2StarLounger
Posts: 125
Joined: 17 Feb 2010, 16:25

Re: Request for Design Assistance

Post by PaulW »

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.
PaulW
Lost Wages, NV USA
(former Cobol Programmer)

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Request for Design Assistance

Post by HansV »

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.
Best wishes,
Hans

PaulW
2StarLounger
Posts: 125
Joined: 17 Feb 2010, 16:25

Re: Request for Design Assistance

Post by PaulW »

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
PaulW
Lost Wages, NV USA
(former Cobol Programmer)

User avatar
Charlotte
Her Majesty
Posts: 499
Joined: 19 Jan 2010, 07:13

Re: Request for Design Assistance

Post by Charlotte »

PaulW wrote:So far as the inventory system is concerned, a toy is a toy is a toy is a toy.
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.

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

PaulW
2StarLounger
Posts: 125
Joined: 17 Feb 2010, 16:25

Re: Request for Design Assistance

Post by PaulW »

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 :smile: .

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)