Check form (1 Viewer)

[Joeman2020]

Hi Access World,

I was just wondering if you can check out my data base, specifically the form. When I enter the new record, it's not showing up on the table parts. Also, the buttons I put in doesn't seem to working either. I would appreciate any advice that you can give me. Thank you...Joe from Michigan

 

[moke123]

unfortunately you have numerous lookup fields in your tables. They are evil.
please take a look at http://access.mvps.org/access/lookupfields.htm

 

[plog]

I know you asked about the form (its what everyone thinks is the most important), but I always look at the tables and when there are issues there I go no further. You have issues with your tables.

The big issue is that you are not letting the database work for you when it comes to keeping track of inventory. [QuantityInStock]/[StockLevel] shouldn't be a fields in tables. Values you can calculate should be calculated and not stored--those fields and the action queries you have supporting them shoudl go. When you need inventory levels you simply add up all your credits and debits to your inventory in a SELECT query.

Also, you have too many tables with data about parts and you are not storing the right information in them.

tblParts - By its name this table should just list data about parts (name, description, type), but you've crammed in data about Vendor, order and customer. This table is doing more than its name and would be better named tblTransactions

tblInfo - this seems like what tblParts should be--just a listing data related to parts

tblinventory - this table should go away and instead be replaced with a query based on tblTransactions. [Minimum Stock Level] should go into your parts table.


You have additional issues that I need more information about to help you with. Location and Vendor data might require additional tables to accomodate. Can multiple parts be in multiple locations? Can multiple vendors provide multiple parts?

You need to address your table issues before proceeding with forms.

 

[Joeman2020]

Hi Plog,
Thanks for the advice. After I make the adjustments, can you look at it again. In terms of multiple parts be in multiple locations: I'm thinking that one multiple parts can be found in multiple locations and multiple vendors can provide multiple parts. Joe.

 

[plog]

Yes I will look at it.

Because your parts/locations and parts/vendors are many-many each will require a junction table. Check this link out:

Video: Create many-to-many relationships - Microsoft Support

Training: A many-to-many relationship is when one or more items in one table has a relationship to one or more items in another table. Watch this Access online video for more.

support.microsoft.com

Additionally, you must answer another question--how grainularly do you want to track parts? Let's use a car metaphor. Theres a stretch of road you want to monitor traffic on. You could do it super fine capturing license plates and detailing every point of entry and every point of exit for each specific car. Or, you could just care about how many cars are on the road without regard of where they came from or where they went. With your data do you really need to track every part from vendor to location to order? Or do you just car that X many parts came in and Y many went out?

Review my link, think about the grainularity question then give it a shot on your database. When you think your done, update the Relationship Tool, expand the tables so we can see each field then post a screenshot back here and we can work through it.

 

[Joeman2020]

Thank you for the advice. It may take me a little while to complete are you on here, or can i shoot you an email?

 

[plog]

Post it in this thread and we can work thru it here

 

[Joeman2020]

Hi Plog,
Thanks so much for helping me on my project. I want to understand how I am doing this. I looked at your link and tried to mimic the tables that were in the video. I've added some fields that were in the video. I placed the junction table as order details and made the relationships based on the video. Can you take a look at my progress so far? Feel free to make any changes you feel necessary.

 

[plog]

I'm looking at it now and I need some more information. Can you give me a few sentences on these:

1. What's your organization do? Give me a high level view, no database jargon. Pretend its career day at an elementary school and your explaining what it is you guys do.

2. What does this database help you accomplish? Limit the database jargon and just tell me what you want out of the database, what it will help you do and how you expect data to get into the database (<5 users/ many users/mass data upload/etc.).

3. Explain how grainular you want to track stock. See my prior post explaining how you can track super specifically or generally.

 

[moke123]

Hi Joe,
I'll leave the nitty gritty of your design issues in Plogs very capable hands but I want to emphasize some issues that may cause you troubles down the road.

You still have lookup fields in your table. Lookups on forms are okay but using them in tables will cause you tons of problems as your database and skills develop. It may seem easier now to see the product name in your transactions table when you look at it but bear in mind your users will not and should not be looking at your tables. While it may appear that the product name is stored in that table it is actually the PKey of products table. Writing code and queries can get complicated with lookup fields in tables. There are a number of issues that will arise over time.

For the most part, your naming conventions look okay and you obviously made an effort to not have spaces in field names but you missed a couple.

You are also formatting primary keys. Primary keys should be meaningless to the end user and usually they are not seen. Their prime function is to uniquely identify a particular record. I would also caution against using a special character in a primary key.

Lastly, when uploading a sample you should really anonymize any names, addresses, phone numbers,etc.