Help with Form & Inventory Set up (1 Viewer)

[Fisher970]

Hi everyone,

I've just started back on MS Access after not using it for a couple years since learning the program in University. I'm definitely feeling some growing pains so came here to see if anyone could help.

I'm taking over the management of a remote property in the Bahamas that is going through some modernization and as such, we are trying to move all paper forms to a digital format. I've really been spear heading the whole thing since I have more experience that anyone else on property with Access (doesn't really say much). I've manged to set up most of our forms to record data into new sheets (the easy stuff). But, now I'm working on our maintenance forms and logs.

We rely on generators for power and as such, have to do a lot of maintenance on them. We have 2 generators and each is a different brand and size that requires different filters and oil and coolant quantities (one is a 4 cylinder John Deere, the other a 3 cylinder Yanmar). I'm trying to set up a form that will allow me to just put check boxes for the various items changed/replaced that will automatically deduct them from the inventory. How can i set it up so that it does this process and selects the correct filter/ deducts the right oil level out of the inventory? Is there also a way that I can set up the hours entered into the form to update the the "Generator Master" with the total hour information?

Thank you in advance for any assistance.

 

[theDBguy]

Hi. The more you want to make things automated, the more you'll probably have to use code to do most of the stuff for you. Do you know if you have the right table structure? That would be the first step. After that, you can design any UI/Form you want and either Access will do the work for you or you have to "bend" it a little to do the extra things peculiar to your needs. If you can post a sample copy of your db, we can take a look.

 

[Fisher970]

Hi.
Thank you for the response. I've set up a sample copy of the DB for you to take a look. I think I have the right structure but would love to feedback / criticism on what to improve and things to change.

 

[theDBguy]

Hi.
Thank you for the response. I've set up a sample copy of the DB for you to take a look. I think I have the right structure but would love to feedback / criticism on what to improve and things to change.

Hi. Sorry, I got busy today. I'll try to look at it tomorrow.

 

[plog]

Tables, then Reports, then Forms. That's the development order for a database. No point making amazing beautiful forms if you are just throwing data into an improper structure from which you can't retrieve your data. So, here's what I see with your tables:

1. You need a new table for services. Instead of having Yes/No for 5 services you should have a table that lists which services were performed:

tblServices
serv_ID, autonumber, primary key
ID_Log, number, foreign key to [Generator Log].ID
serv_Type, text, this will hold the service values (Oil Filter Replaced, Fuel Filter Replaced, etc.)

So those 5 Yes/No fields come out of [Generator Log] and the user would select which services they performed and that would go into tblServices as a record. If 4 services were performed, 4 records would go into tblServices. If a service wasn't performed, no record goes into tblServices.

2. Calculated values shouldn't be stored. Instead you use a query and you do the calculation there. That means [Cost of inventory] comes out of [Part Inventory] and you do the math in a query and reference that when you need that value.

In that same manner, [Number in inventory] will probably come out as well. I'm guessing you want the system to track inventory, not have someone update this table each time a piece gets used or more come into inventory. You do this with an tblInventoryTransaction table. It will hold records where you add to inventory and all the records where you use inventory, then to get inventory on hand you run a simple Totals query and it let's you know what the balance of every part is.

3. Change your names. Only use alphanumeric characters and underscores in table and field names. No spaces, no parenthesis, no hash marks; just letters, numbers and underscores. This makes coding and querying easier.

Also, prefix super-generic field names with something to make it more meaningful. Don't use just [ID], put a short description of the table its the id of(Log_ID). Same with [Hours], what type of hours? What do the hours represent?