How can I do this...? (DB design query)

[garywood84]

I am building a system to manage the allocation of materials/stock to events. I know how I want this to work from the front end, but am not sure how to configure/develop it, so am hoping someone can help me.

First some background. The system is going to manage a number of different types of event. The events all have standard requirements for materials/stock, based on the number of attendees. The database so far has a table of event types, and a related table (EventMaterials) lists materials and the standard quantity per attendee (with the relationship being EventTypeID).

I want a system that works as follows. The user enters basic event details, such as date, location, type of event (selected from a list), number of attendees, etc, on a form. The system then creates order lines in the stock inventory table for each material item required, basing the ordered quantities on the number of attendees and the standard quantity per attendee (in the EventMaterials table). This information should then appear on a subform, where the user can manually amend quantities (if they want fewer or more of a particular item for some reason).

I’m guessing I’m going to need some kind of macro that calculates the number of each standard material item required and creates the order lines in the inventory. The problem is that I have absolutely no knowledge of VBA coding other than what members of this forum have kindly helped me to produce in the past.

A further issue is that when an event is cancelled, I need to be able to remove the order lines from the inventory when the record is deleted in the Events table (though I think this will work with cascading deletes?)

I hope that someone can point me in the right direction with this, because I’ve successfully figured out how every other aspect of my system will work and am just stuck on this now, before I start building…!

Thanks,

Gary

 

[Scooterbug]

I want a system that works as follows. The user enters basic event details, such as date, location, type of event (selected from a list), number of attendees, etc, on a form. The system then creates order lines in the stock inventory table for each material item required, basing the ordered quantities on the number of attendees and the standard quantity per attendee (in the EventMaterials table). This information should then appear on a subform, where the user can manually amend quantities (if they want fewer or more of a particular item for some reason).

This part is easy. It's a classic Order Entry form. You have a form with the basic event details bound to an tblEvent table. From there, you can have a combo box listing the materials. That data is then put into an EventDetails table. By storing the EventID with each entry, you can then reference the materials to the Event.

I’m guessing I’m going to need some kind of macro that calculates the number of each standard material item required and creates the order lines in the inventory. The problem is that I have absolutely no knowledge of VBA coding other than what members of this forum have kindly helped me to produce in the past.

When you say Inventory, are you talking about tracking the Inventory stored in say a warehouse, or are you just looking to create a list of all the materials that you will need for an Event?
Ether way, a query can be designed to get what you need.

A further issue is that when an event is cancelled, I need to be able to remove the order lines from the inventory when the record is deleted in the Events table (though I think this will work with cascading deletes?)

Do you want to keep a record of Cancelled Events? I would rather use a Yes/No field to denote if an Event has been cancelled...you can then use that value to filter out the cancelled events.

Also, make sure that you have a good table structure. If you are unfamiliar with Normalization, read up on it. Having good table structure will make things a lot easier down the road.

 

[The_Doc_Man]

Look at the "Theory and Design" topic for threads on determining what should and should not be in a table, query, form, etc.

Suppose that you didn't have a computer. How would you do this by hand? (No, not being flippant...) This is the first step - problem analysis. Where would you go to get the information you need. How would you compute it (by hand)? Where would you store the results? What would you do with event cancellations? How do you track event attendees? What do you do about attendee cancellations?

I suspect that you would have some worksheets, reference lists, etc. on paper. You would add names to an attendee list tagged to a proposed event. You would sum up your attendees at some point to place your supply orders. And so on ...

The tables in Access can replace your reference lists, attendee lists, event lists, and detailed order-item lists once you have your mind completely folded around the manual process. As you go though the process, you will find some considerations...

You will need to look up a list of supplies per person - meaning you have a supply table that gives you this information. There's one table.

You will need to track events. Meaning you have an event table.

You will need to track attendees, each tied to an event. If you have a lot of repeat attendees, it would make sense to have an attendee table as a customer list and make a separate JUNCTION table (search this forum for examples) to show which attendees are going to which events.

You will need a place to compute your supplies and place orders. This is not less than two tables, one to track what you need and the other to track what you've ordered. Two more tables, almost certainly.

If you have not studied normalization yet, that is a MUST for a set of tables as complex as you are going to generate. I would say offhand that you have a lot of reading and a lot of web searching in your immediate future. When you study articles on Database Normalization, your target is not less than 3rd normal form, which you would learn about during the web searches on DB Normalization. If you give Access something that is no worse than 3rd-normal, you will be a happy camper.