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.

 

[Pat Hartman]

At this stage given your experience level, it is probably better to keep the data with the other objects and not split. You WILL need to split before you distribute the application to others but for now, you can leave everything in one database.

The process of splitting can be done by a wizard. The wizard takes all the tables from the database and copies them to a separate database. It renames the existing copies as yourtablename_local and then it links the database Front End (FE) which now contains only forms/reports/macros/code/queries/linked tables to the Back End (BE) which contains only tables. At one level, the necessity of doing this is obvious - you can change the FE without holding the data hostage so your users can keep using the app while you make modifications to the FE. Once the mods have been completed and tested, you would distribute a new copy of the FE and link it to the production version of the BE rather than the test BE that you have been working with. But that's a little down the road for you. There are also other reasons for splitting, mostly revolving around preventing corruption.

Access records are limited in size. I don't remember the exact limit but i think it is something between 2,000 and 4,000 characters so to resolve this problem, you can start by:
1. Remove ALL calculated columns. Calculations are done in queries/forms/reports and calculated data is almost always not stored in tables for reasons you will come to understand.
2. Go through the remaining fields and make sure that the column sizes are reasonable. You don't need all text fields to be 255 (the default created by importing from Excel). Adjust them so that they hold only what is reasonable. For example, ZipCode in the US is limited to 10 characters if you store the dash or 9 if you don't. State is abbreviated to 2 characters, etc. Also make sure that numeric fields are defined as numbers and dates are defined as date data types.
3. You have created a number of lookup tables. Most of them will be in the format-
tableID (primary key), valuedescription (what you want to see in your combos).
4. Relationships are ALWAYS Foreign Key (FK) to PrimaryKey (PK) so, you would store RawMatlID in the Ingredients table rather than RawMatlName. Remember, your users will ALWAYS be working with forms and reports. Only YOU will be working with tables and queries so the fact that you don't see the name of the raw material (don't get sucked into using table level lookups) isn't a problem. The combo on the form will show the text rather than the ID and that is what the user will see. If you want to create a query that shows the name as well as the ID, then include the lookup table in the design grid and draw the join line from the FK of the main table to the PK of the lookup table. Of course, if you went as far as going to the Relationship window to create a real relationship, Access will automatically draw the join line since it knows how the tables are related (baby steps - Relationships are critical to a healthy database but not to getting something working. It is just that if you start without them, you will almost certainly have data issues that need to be fixed when you actually implement relationships.

And finally, to eliminate future work and possibly other errors, use good naming standards. Use ONLY letters (upper and lower case), numbers, and the underscore. NEVER use embedded spaces or any special characters such as #. Remember, users NEVER look at tables and queries. So the names are for your use and the use of the query engine and VBA so go with what they like rather than what you may want your form and report labels to say. Also, be careful to avoid reserved words. In particular, watch out for function names such as Date, Month, Year, Split, etc and property names such as Name, Length, etc. You can do this one of three ways -
Memorize the reserved word lists for Access, VBA, and SQL
Bookmark them and always check before creating a new name
Name your objects to avoid conflicts. This is what I do even though after all these years, I could probably list about 50% of the problem words if I had to. Use qualifiers such as SaleDate, BirthDate, ReceivedDate. The names will make much more sense when you are reading the code than Date or Date1, Date2, etc.

My preferred naming style is called CamelCase and it is based on sheer laziness. I don't like shifting when I type. So I shift when I define TheName and forever after, I just type thename and Access caps it for me automatically. Other people prefer the_underscore. I do use underscores but only if I want real separation between the parts of a word. One other technique that will be helpful is to prefix object names. For examples, my tables are prefixed with tbl (tblStudents, tblparts) and sometimes lkp for tables that are only used for lookups. Queries are prefixed simply with q (qLastUpdateDT, qMontlyCount). Forms as frm and sfrm for subforms. Reports as rpt and srpt for subreports. I never use macros (except for 2 that are in all my databases) but if I do, I use m.