I'm stuck

[tc197]

Hey everyone,



So I created my entire database, my main form, my search queries/reports, and had it all working fine this morning. I went to create the new forms which are very similar to the original form but have a few additions here and there which meant a few more fields here and there. Now, none of my search queries will work because I get the "too many fields" error after adding my additional fields. I already compacted and repaired the database and I was wondering if anyone could help tell me what's wrong with my structure/queries.

Overview of my database and how I structured it:

A unit can have up to 48 cells each with its own number. Each cell also has a resistance measurement. The cells are connected in series or parallel and the letters following the number of the cell define how it was connected. if the cells are 1B 2A 3B 4A -> 48B then the unit is in series. If the cells go 1B 2A 3B 4A 5A 6B -> 48 then it is in parallel. There is also a resistance measurement between each cell (cell 1-2, cell 2-3 ... etc)

I created 6 tables that need to be searched through because they contain the final information.

Table 1= cell number. there are 48 fields for each of the cells (1B, 2A, 3B, 4A...) plus an additional 30 fields for the instances where the letters flip and it is in parallel (1B, 2A 3B 4B 5A...)

Table 2 = cell resistance. There are the same number of fields as table 1 and it records the resistance measurement.

Table 3 = general info. 10 or so fields with info like who made it, the date, what type of fuel, etc.

Table 4 = Resistances between cells. 48 fields for cells 1-2 2-3 3-4 etc. plus an additional 5 fields for the parallel connections.

Table 5 = Power lead resistance which is about 20 fields for anode-cathode, anode-ground, cathode-ground and so on.

Table 6 = Voltage tap resistances which is about 2 fields for v1 - ground, v1-v2, v1-v3, etc...

All of this info i recorded for each unit as a whole and there are hundreds of units. How can I avoid the too many fields error while still collecting all of this info? I don't understand how I could re-arrange the tables differently because as you go down in rows you change from unit 1 to unit 2 and so on. I can post the DB if that helps as well, I just don't see a solution. Thank you for any help!

[rpeare]

Could you post your database please (make sure to zip it as well).

Access has a limit of displaying 255 columns of data. I expect that your database structure is not in a normalized format (you can find millions of articles about data normalization on the web) but I would be curious to see the structure to see if there is a better way to arrange your data and still get the results you want.

[John_G]

Hi -

You have probably exceeded the maximum number of fields allowed in a table or query - 255.

Your description of the data structure indicates your data is not properly normalized. You really should have a table with one row per cell -

Unit Number
Cell Number
Properties
...
...


and the same for some of the other tables.


But before offering any suggestions as to how it might be re-structured, more information, please:

When searching the tables - what data are you searching for? In other words, what are the questions that you are trying to answer, in terms of the results, and the criteria used to do the search?

Having this information will help to determine what options you have for structure.

HTH

John

[tc197]

Absolutley. The issue is I had to follow an excel form and recreate a database around this form.


give me one second to get try to organize all the documents I have and present them to you in a way you can follow.


Ok in the database below. If you could open up the front end and take a look at the form 48_cell or 48_cell_parallel. Those are the two forms that contain every field in my database. They have to be set up like that in order to match up with the old excel form that was being used.

Then if you could click the "search by cell" button on my main menu -> type in anything for the cell number -> and you will see exactly what I want my report to look like. This all works just fine until today. Today I had to add more fields for the parrallel form and I hit the max number of fields (255). That is why the "search by bundle" button does not work. I also screwed up both of my update queries within the last hour but I can fix those later.

The main issue - How do I maintain my form exactly how it is, yet store the data more efficiently so i don't have 255 fields?

I attached the old excel form I am mimicking as well. Thank you!

[tc197]

Also here is my database as of yesterday before I hit the field limit -just incase it helps at all.

After this back up was created I split the database, made a main menu, redesigned the cell number to be broken into a cell year and number so that it could be entered easier, and then tried to add in the parallel fields when I hit the limit.


Then to just put everything into words-

We manufacture bundles that contain cells and we need to create a record of each cell with respect to the unit it is in. This database is used to store all of the measurements and numbers associated with each cell. When a cell goes out into the field and fails we want to be able to search back to what bundle it was in and obtain all of the information related to it. Sometimes we only know the cell # and sometimes we only know the bundle #. This is why I have two different search options but regardless, we want all the information for all 48 cells for a particular bundle to open up once the search is conducted. Aside from this, it is just a big storage vault. Any additional analysis will be pulled from the tables and done in excel most likely but the big thing is we want the data entry to remain unchanged so the workers who manufacture the cells don't have to learn a new system. I also have no say with any of this as I am an intern at the company and have no power what-so-ever.

After the 48 cell form/search works and the 48 parallel works the rest is smaller and should work no problem. Theres a 40 parallel, 40 cell, 20 cell, and 10 cell system.

[rpeare]

Ok, here is what I see:

1. None of your tables has a primary key that is NOT user entered. I would really strongly recommend you put one in, autonumber works fine. The risk you run with data entered primary keys (bundle_number for example) is that if someone accidentally changes a value you are going to orphan some data. Each table should also have it's own primary key value along with any foreign key to external tables.

2. Your Year, number, resistance cement and resistance interconnect can all go one one table in a normalized structure along with an additional field to indicate which CELL it is.

for instance:

CellID	BundleID	CellNumber	CellYear	CellNumber	ResistanceCement	ResistanceInterconnect
1	1	1B	XXXX	1111	AAAA	1.0
2	1	2A	XXXX	1112	AAAA	1.0
3	1	3B	XXXX	1113	AAAA	1.2
4	1	4A	XXXX	1114	BBBB	1.3

This is a more normalized structure. In other words you don't have a resistance interconnect value for cell 1, only each cell after that. So in essence the value in resistance Interconnect is the resistance between the cell and the one following it..

3. I am not sure how the resistance voltages and resistance of power leads are related to the cells. If they aren't related to the cells you can set up another normalized table structure similar to the cells to handle the data:

RV_ID	BundleID	VFrom	VTo	Fld1	Fld2
1	1	V1	GRND	blah	blah
2	1	V2	GRND	blah2	blah2
3	1	V3	GRND	blah3	blah3
4	1	V4	GRND	blah3	blah4
5	1	V5	GRND	blah5	blah5

Now a lot of your data entry is going to be repetitive (putting in the cell number for the first table, putting in the from and to values for the second table) but you can mitigate this by setting up the appropriate tables to cycle through 'valid' cell numbers of resistance voltage limits to create a set of values when you enter a new bundle so that you can cut down the data entry to exactly the set of values you have right now.

[tc197]

Thank you very much for taking the time to look at this. I never thought of the cell ID thing. I also don't understand the concept of primary keys. To go ahead and try and implement what you came up with is it best that I start over? Or is there a way to edit what I already have?

[rpeare]

how much data to you currently have stored in your database? I assume you're talking about trying to convert what you have into a more normalized structure?

If this database is still in the design phase or it won't matter a whole lot if you dump the data you have (you can keep it in your existing database) then I'd just write the new tables, vba code etc and go from there.

If you are looking to do a mass conversion of data that is a bit trickier.

[tc197]

All the data is still in excel. I started this database last thursday and I've been working and testing it since. I don't even know if I can start over, I think I'm supposed to be done monday or tuesday and it will take me forever to redo all of this and learn how. Why does Access have a field limit anyways, just to make things harder?

[rpeare]

Access isn't supposed to be a top end database, that's why MS sells SQL itself

If you are attempting to import from excel spreadsheet(s) data you are going to have a dickens of a time. If you are just going to re-enter the data then building conversion code will be a non-issue it's just a matter of re-keying what's already in the spreadsheet(s).

Alternately you could just go with what you have but I wouldn't recommend it. In the long run a non-normalized structure is a real pain to deal with.

If you can share an example of an existing file/spreadsheet there may be an easy way to deal with an import of the data but I wouldn't hold my breath, if you are not comfortable with VBA it will be that much harder because I do not imagine the spreadsheets can be imported without parsing information in specific cells.

The good news though... once you have it done it will be way, way, way easier to handle.

[John_G]

Hi -

Primary Keys. A Primary Key (PK) is a field (or combination of fields) which serve to uniquely identify each record in a table. (Example - the Bundle Number in your General_Info table). The data in PK fields is always required, and it is always indexed. Now, before the purists start throwing rocks at me, I'll explain that PK's using a combination of fields (they're called compound keys) are perfectly valid in database theory, but in practical terms they can be a pain, especially when they are used a lot in relationships and queries. In these cases, it is often easier to create a unique numeric value to use as the PK. You can let Access generate the value (Autonumber) or calculate it yourself, it doesn't really matter. Another case where you might want to use a numeric PK is if the real PK in the data is a relatively long text field.

In your case, you should probably have numeric PK's, as rpeare has noted.

[tc197]

rpeare the first column called cell resistance is no longer needed

[rpeare]

Here's an example using a normalized structure:

Cell_Tracking.zip

A few notes:

Open the form frmMain when you open the database

When you click EDIT CELL INFO you'll get to a screen where you can add a single cell or you can bulk add cells with sequential numbering if you want to. A basic cell record will be created with the year, week and month of the cell. I just have a couple of extra pieces of information in there like scrapped and date field (manufactured date maybe). After the records are bulk created you can do whatever you want to them. I included a 'scrap' yes/no field so you could eliminate bad cells from your lists during data entry.

When you click EDIT BATTERY CONFIGURATIONS you'll go to a screen where you can set up your testing for given setup, I only put in your example spreadsheet (RB48). If you click on the configuration then click any of the three testing functions (EDIT RESISTANCE INTERCONNECT TESTS, EDIT RESISTANCE VOLTAGE TESTS, EDIT POWER LEAD TESTS) a subform will open that allows you to organize the tests in any order you want with some buttons to help you move items around. To create a test you click an item in the first column (FROM) an item in the second column (TO) a SORT ORDER if applicable then just click ADD, if no sort order is entered, the record will automatically be added as the last in the sort. If you enter a number in the SORT ORDER field when adding a test all tests will be shuffled down accordingly.

This is what is going to drive your data entry screen and make it easier to handle.

When you click EDIT TESTING DATA you'll open a form that will allow you to enter testing results. It does not look exactly like your excel spreadsheet but I was trying to make it fit on a screen without having to scroll but it's got the same functionality. If you create an assembly NO DATA ENTRY will be available until you click CREATE TESTING MATRIX. This is to avoid creating a bunch of bad records if an assembly is put in incorrectly.

NOTES:

1. The form will fit everything if you have a high enough resolution (mine is 1680x1050, it's a widescreen monitor)
2. I did not do anything with the search function because I don't really know what you expect to type in when you search (i.e. you know you're looking for an RB48 assembly, do you just put in the part of the identifier that doesn't include the RB48 or do you put in the whole assembly number, similar reasoning for the cell search)
3. I used subforms for all of this though I really don't like them, it makes things more accessible if you don't have a lot of experience dealing with unbound forms. Because this is using subforms you're limited in a few areas, for instance your CELL # column. It is not possible to enter a year/cell number and have the cell numbers limited by the year you enter. If you had two cells available, one from 2013, one from 2014 but they both had the same number (1-1 for example) you would not be able to distinguish which one you were entering without adding columns to the combo box and physically using the drop down every time. That is really no efficient in terms of data entry so I concantenated the year/week/cell number as a single data entry point rather than selecting a year, then a cell number.
4. Everything set up on the EDIT BATTERY CONFIGURATIONS form drives how the items are organized on the four subforms on the form frmAssembly. If you want to alter the order items appear, you have to go to the configuration form and change them.

I may have gone a little overboard with this example, it was a long weekend :P

[tc197]

Thank you for taking the time to do that but I don't know how to open the .accdb file? I have access 2003 if that helps

[rpeare]

Cell_Tracking_2003.zip

2003 version, I didn't test this one before re-zipping it.