New to Access and need guidance

[beckeda]

First, sorry if I have posted this in the wrong area or has been covered. I just joined the forum and after quick review could not find what I was looking for.

I am building my first DB in Access. I am proficient with Excel and have built pricing model for my business in Excel. Main reason for moving to Access is that I want to have a record of each price quote that I can quickly recall and the dropdowns (combobox) feature in Excel is just okay (especially with many items).

I did some Access training on Youtube, etc. Reading Access for Dummies. Apparently, I am too dumb for that!

Anyways, my first DB project went as follows:

I created several tables (raw materials, bottles, closures, capsules, labels, blender machine, capsule machine, etc) most containing item name, unit of measure, unit cost - machines containing capacity info (e.g., capsules per hour, max KG load per blender)

Then, I created a master table to collect all of the information about the product. I created the comboboxes right there at the form level. I populated the other fields (U/M, unit cost, etc) with VBA "me.unitcost1.value=me.rawmtl1.column(3). Most of my product quotes contain less than 10 ingredients, so I created 10 fields in the table (e.g., rawmtl1, rawmtl2....etc). I am guessing this is a no-no.

My mastertable contains about 220 columns (I see it maxes out at 250). Many of these columns are calculations (IIF, QTY>0, QTY * Unit Cost,0) etc.

I created a masterform to input the data and the table does the calcs and feeds that info to the form.

Everything was going well! Then I added a report and combobox to search for records by catalog number, then........things went bad.

At the form, when entering a new record.....it craps out and says "Record too Large".

Then, I go back in and all of my comboboxes no longer auto-fill. I have to click and scroll to find the ingredient I want.....major problem because its a list of about 400 ingredients.

The program did exactly what I wanted it to do.....for a few minutes.

Is this an issue with the way I designed this? too many table columns?

I will be honest and say I do not understand the table relationships and queries at this point.

Appreciate anybody pointing me in the right direction. I don't want to give up on Access yet.

 

[Ranman256]

your table may be designed wrong. Tho I cant see your table fields, you keep expanding data horizontally by adding columns....instead expand it vertically by adding records in a subtable bound by the MasterKey, like:

masterKey, ingredient, Cost, Qty
231, Salt, 0.35, 5
231, Pepper, .10, 2
etc...

now you have inifinte 'columns'.

 

[Minty]

You haven't normalised your data, and in fact have done what many a new user with Excel experience does, which is pretty much moved your spreadsheets into tables.

in Excel it is very common to store data "horizontally", where each column represents a different "thing" , in your case raw materials.

You don't do this in Access if you want to harness it's real power. You should store your data vertically, where you would have a Raw materials table with an RawMatId, MaterialDesc. Possibly some other distinctive attribute fields .

You would then have a table lets call it ProductParts. This would have your Unique product ID as a foreign key and as many RawMatID's as you need per product. Think of it like an Invoice Header, and the separate Invoice detail lines.

Have a read up about moving from Excel to Access - there are many good guides some in my signature.

 

[beckeda]

Thanks, I will need to study up on building subtables. I am guessing that would use only the required number of lines as I would have ingredients for that order. Presumably, I would only need to enter the formula once....instead of typing/copying the same basic formula 10 times.

 

[Gasman]

Also I dare say that before you made any large changes to your Excel workbooks, you saved a copy?
That practice is worthwhile with Access as well.
That way you can just go back to a working copy and not try and remember what changes to undo, especially as you are new to Access.?

 

[beckeda]

Yes. I saved multiple times and did the "compact/repair" many times. And that reminds me. I had a version of the file (call it XYZ2) that I saved and renamed the new file XYZ3. I continued to add/improve on XYZ3. When the issue with the comboboxes losing autofill function came up on XYZ3 file, I went back to XYZ2 file....and to my surprise the combobox autofill did not work on it either.....yet I know for certain it was functioning properly at the time I saved it.

Dave

 

[Gasman]

Well the combos would be affected by any changes to the data, as well as code?
Is your DB just one file at present, not split between a Front End (code) and Backend (data)?
Did you change the table for the combo in anyway and forget?

If the code had not changed, then the data would be at fault, I would have thought?

 

[beckeda]

not entirely sure what you mean by splitting database....when coding I just clicked on event procedures, selected code, and the typed in the code. The code seems like its "all one one sheet"....if that make sense.