Too many fields

[connerlowen]

HI,

I have a database that will take lots of data entered by an employee and calculate some additional data and generate quotations. Within each quote there is a possibility for 15 different metals (5 Precious Metals, and 10 Base Metals). There is also 5 fields that need to be filled out about each Metal (What the metal is, the market being used, weight, whether it is included in a different price, and the price). I currently have 75 fields to address each Metal and their 5 fields respectively. Is there a better way to Normalize this data, and accomplish what I need accomplished? I want at the form level the employee to tell the database whether they want to add a Precious Metal, or Base Metal, or Move on to other data entry.

Here is a Screenshot of the design view of one of my tables with too many fields:


Thanks,

Conner

 

[spikepl]

I'm getting a feeling of déjà vu, because I 'm sure this has already been mentioned in one of your previous threads by somebody.

For you (and anyone else reading this) do not ignore what people write. If you do not understand what they mean, then simply ask for clarification of the specific incomprehensible bits.

Now the problem at hand: the moment you begin to ennumerate fields (metal1 ... metalN) then you need a new table. Because the ennumerated data is all similar, and similar data has no justification for existence in separate columns. This is called normalization.

There is no normalization police, but the consequence of skipping it is eternal PITA. More superfluous work in data creation, maintenance and management, an unspeakable number of queries has to be handled, and many special functions have to be written. All that you escape if you normalize properly.

Make a table with all your metals - add a column to discern between one and the other, and that's probably it. Show screenshot of your relations window with all tables expanded fully so with all fields visible.

Update:

Many get confused and try to implement an Excel look-alike scheme in Access. In Access storage of data is entirely separate from visualization (or data entry), so those two are not married like in Excel.

 

[connerlowen]

I have mentioned this problem before, however I did not go into this much detail. I have now realized that this is the root of all of my problems, and I need to address this first and foremost before moving on. I tried creating forms and they fell apart.

You mention a new table, which I understand. However you are implying that I not just cut and paste all 75 fields into another table. That being said, what do I need to do differently within the new table.

Thanks,

Conner

 

[MarkK]

You need to understand a one-to-many relationship, which very simply modeled, looks like this when incorrectly implemented in one table . . .

tblThisBites
parent, child1, child2, child3, child4, ..., childN

. . . and correctly modeled in two tables, looks like this . . .

tblParent
parentID (primary key)
parent

tblChild
childID (pk)
parentID (foreign key)
child

In the second model, note how one parent record may have zero or more related children? See how elegant and flexible that seems? If there are no children, there are no records. If there are two children there are two records.

This ability to accurately model one-to-many relationships means you can model facts about the world way, way, way more accurately that in a spreadsheet.

Hope this helps,

 

[gemma-the-husky]

connor

Sorry to be a PITA myself, but you mentioned at the outset that you had no experience in access databases, although you had experience in coding, and you have been given what appeared to me to be a complex database to build in a short period of time.

I just feel you have little chance of being able to achieve your aims, without serious on-site professional help. Access database development is completely different to excel development, although many people clearly think they are the same.

Asking someone with no access/database experience to build an access solution, is like asking someone with no language-coding experience to build a .net or vb solution. They just wouldn't have a chance.

Developing an database is not something you can "hack" like a spreadsheet. You can get by in excel without ever using a macro. You have absolutely zero chance of achieving anything in access without a substantial amount of code. Indeed a good 50% of code in access is defensive, to prevent or limit ways in which users can use access facilities.

If you can, I would talk to your employers, and explain to them that you need professional help to get started at least.

An access project is bespoke application development. It may not seem it perhaps, but that's what it is. I doubt if your bosses would specify and implement a new critical resource without careful specification and evaluation beforehand. But by asking someone to "throw together" a database, that is exactly what they are doing.

 

[vbaInet]

I wonder if there's more to this database that requires our attention. Conner, perhaps you can expand on the entire structure and not just the ones MarkK has helped with.

You can actually build a decent and very simple database without requiring much code as long as the tables are properly structured. It's usually the customisations/personalisations and performance enhancements that tend to require more complex code. But of course, gemma-the-husky makes some good points.

 

[connerlowen]

After normalizing the tables I have started playing around with the forms, and everything seems to be working smooth so far. I believe I have all of the necessary tables and fields. I also believe I have all of the necessary relationships for things to run smoothly.

Thanks,

Conner

 

[vbaInet]

Just for completeness, you could post a legible screenshot of your Relationships and if there are any improvements we can advise. Up to you.

 

[connerlowen]

Here is a screenshot of my relationships. It looks complicated and clustered, but there are a lot of components.



Thanks,

Conner

 

[vbaInet]

Ok, I can already see some improvements but I'll leave it to someone who has more understanding of an assembly line to comment.

 

[connerlowen]

Thank you for all of your help and understanding. I know it is frustrating sometimes working with new users. I have learned a lot from this forum, and am extremely thankful for everyone who has offered any help at all.

 

[spikepl]

So why are PM and BM in separate tables?

 

[connerlowen]

Because I assumed that Base Metals and Precious Metals needed to be in different tables. Also I am hoping that down the road I will be able to incorporate a data feed that will automatically update the Market price of each metal based on the closing price of the day before. I realize that is way over my head right now, but its something my boss wants to look into.

 

[spikepl]

Do not get confused by Excel, external data sources or - the worst - by bosses.

Normalization rules determine how tables should be constructed and those rules do not give a toss about any of the mentioned factors Similar data should go into similar containers. You don't have a Monday-wallet, Tuesday-wallet etc ... do you?

 

[connerlowen]

So combine the tables and add a field for Precious or Base?

 

[spikepl]

Yaaaaaaaaaaaaaaaaaay . U got it

 

[connerlowen]

Are there any other Problems that you noticed?

 

[connerlowen]

Does it still make sense to have a different table for my Internal and External processes because they have vastly different data that needs to be entered?