Beginner struggling to set up a database - Problems with reports and forms.

[Karooneet1]

Hi

I briefly touched Access in computer studies at school, which for me, ended 10 years ago. I tried to brush up on my Access skills by watching an online Access tutorial on YouTube recently, but it wasn't of much help, which brought me to the conclusion that I'm very much a beginner when it comes to Access. It's kind of a problem as my boss asked me set up a straightforward database, even though I know nothing about programming.



The company I work for produces cheese. Each cheese has it's own specification sheet which includes all the necessary information required by customers (i.e Nutritional information, Microbial Standards, Chemical Standards, Packaging info etc.). The cheeses can either be bought as whole wheels, prepacked or in cuts. Each one of those options will have very similar specification sheets save for a few changes here and there.

At the moment there is one specification sheet per cheese containing all the information for all those options. These are on spreadsheets in Excel. Now they want it to be specific to the size of the cheese bought. So, for example, when a customer walks in and orders a whole wheel of Cheddar, a 500g prepacked Gouda and a cut of Edam, I want to be able to enter that information into a user-friendly interface upon which Access will give me 3 specification sheets, one for each product.

I work on Access 2010 on Windows 7 64 bit. I have set up a table with all the fields that I need (i.e. Nutritional information, Chemical Standards, Microbial Standards, etc.) I made all my forms OLE objects so that I can copy and paste the tables from the Excel Sheets into the forms. I haven't done any queries yet as I first wanted to see what it will look like as a report. It looked awful! All the tables were really small, compressed next to one another. I have attached a file of how I want it to look.Example.zip

My questions are:
1.What can I do to make my report look more like an Excel spreadsheet?
2. How can I make my form as simple as possible so that there are 2 options, type of cheese and size, to choose from, which will give me the info I need.

Sorry for the long post.

Thanks in advance.

[NTC]

forms OLE objects - I cannot recommend that.

To bring your data from excel into your tables - link to the excel temporarily as if it is a table - and then run an append query to write the rows into your table.

to set up a form that is in the style of an excel; use the Datasheet view style of a form or a continuous style form....if you want to deal with just 1 record at a time - select the single style.

An Access report must have a record set - and that will be either a table or a query. One can do most everything in a form as in a report - but report objects are more adept at being 8x11 for saving as pdf or printing.

[rpeare]

You're going to have to do a lot of setup on this database to get to the end result of selling your cheeses with the appropriate information.

Each cheese has these 'sections'

Nutrition Information
Organoleptic Profile
Shelf Life and Storage
Ingredient Breakdown
Critical Control points (maybe?)
Micro Standards
Chemical Standards
Packaging
Labeling Directions
Dispatch and Temperature Storage (looks like a free-form write in area)
Legislation

Cheeses specifically for vegetarians (this might be changed to a field related to a cheese that doesn't require it's own table)

The other sections appear more to do with an individual sale than information related to the cheese in general:

Outer Appearance of Rind (looks like a free-form write in area)
Shelf Life Determinations (looks like a free-form write in area)
Consumer handling (looks like a free form-form write in area)
Suitability (looks like a free-form write in area)

In addition you have one area on your spreadsheet that seems to be related to the COMPANY not specifically a cheese or a sale

Annual Accreditation

For each one of these sections you will likely want a table to handle the information related to them.

What you really need to figure out (as orange is always pointing out, should he read this), is what is your BUSINESS MODEL. When you sell a cheese, what information do you need to provide the customer. Is this wholesale or retail or both? If it's strictly wholesale are you providing information so the customer can produce nutrition labels or is your business responsible for the nutrition label? You need to be very clear from the start (or have a very good idea) of WHAT information is important to WHOM. For instance I don't think the 'legislation' information is going to be of any value to a customer but that's where your expertise of the company's process comes in.

What it comes down to is that you have three main areas of data

COMPANY - data related to the company (annual accreditation, possibly legislation)

CHEESE - nutrition information, micro standards, chemical standards, packaging etc, possibly some batch related information so you can track a batch from a consumer back to the batch the product came from.

SALE - appearance of rind, consumer handling, suitability

Some of this information will cross over, for instance you would want to record the batch the cheese sold came from etc but think of your database as compartments (read some on the topic of normalization). Each compartment in your business you will likely want a compartment for (table) in your database. Setting up a PrimaryKey in each table (autonumber fields are your best option for this) and a ForeignKey to any other table you want to relate the information to.

For instance

tblCheese
CH_ID  CH_Name  
1      Gouda
2      Cheddar

tblCheeseNutrition
CN_ID  CH_ID  CN_ServingSize  CN_Sodium  CN_Cholesterol  CN_Calories
1      2      1oz              10        100             120
2      1      1oz              20        120             150

Your nutrition information shouldn't change per serving size so you can set this once per cheese, then print the information when you make a sale if it's appropriate to do so, so the customer can print their labels (if they are the one adding labels)

[Karooneet1]

Thank you both NTC and rpeare.

It looks as if I have a lot of work ahead of me.

I just want to make sure I understand correctly. According to rpeare, I need to create a table for each individual cheese, and then also for each individual sub-section in the spec sheet. In order for these tables to be linked to one another, should I put my cheese ID in all of the tables and make it my primary key? I see in the example you have shown, you put a Cheese ID inside the Cheese Nutrition table as well as a Cheese Nutrition ID. For CN ID 1 the Cheese ID is 2. Does that mean that this is the nutrition information for Cheddar, as Cheddar's ID is 2?

I would like to set it up properly without redoing it in a few years time and I'm sure your suggestions would make that possible. I will give it a go and will probably ask for assistance again in the near future.

NTC, thanks for your advice on forms and reports. It is going to come in very handy once I have set up everything.

Thanks

[NTC]

well fundamentally all relational databases are about 1-to-Many table relationships. 1 Company table links to the Many Employees table. The Company table may have 10 entries. The Employees table may have 10,000 - each cross referenced to their company employer. That sort of thing.

the term 'many' of course is somewhat flexible....today people have multiple telephone numbers - it really isn't necessary to have a Many table for that - you can still put 3 to 5 fields in the primary table....it is when it becomes too many, variable and uncertain that you spin data off into the Many table. This can hierarchy to multiple levels.

[Karooneet1]

Hey Guys,

I have a few more questions.

1. I have made a bunch of tables now, containing the necessary info. Is it possible to create a report that includes all those tables?
2. Some of my tables have to be in a in a form where I am able to name the rows and the columns.
For example my Nutrition table I want it to look something like this:

Nutritional Info Per 100g Per 30g
Energy KJ
Kcal
Protein(g)
Carbohydrate
etc.

At the moment I can only name my rows and not my columns.

Thanks

[rpeare]

According to rpeare, I need to create a table for each individual cheese,

No, one table for all cheese types as per my example.

If you are new to Access and databases in general you will likely be using bound forms (forms tied directly to queries/tables for data entry). This means you can select your cheese on the 'main form' and fill in related information on the 'sub' form and the referential integrity will be automatically enforced. You do not need to enter the cheese ID by hand.

[rpeare]

You can make a report look like whatever you want (including a nutrition label), I do not know what you mean about 'naming rows and columns', when you add a field from your query or table it consists of 2 components. The text box (where the data is) and the caption (a label that is automatically generated with the field name as the default value). You can make those captions say whatever you want by typing over them.