Beginner trying to create a database

[Hottest Fudge]

Dear Mr. Access expert/master/yoda,

Please see pictures attached first! I am looking to create a database. I have attempted to normalise the database. I will have 3 references all together. 1 for the substance, one for the supplier and one for the health risk. I am trying to make the database so it is really easy to input information. I am using reference autonumbers as the relationships for everything, although supplier company name is a unique identifier, also so is the substance name and health risk. Would that make my reference auto numbers redundant? Or is it best just to keep them in.

I would like to input a whole substances information (supplier info, supstance name and health risk). They would type a new name, then use a drop down menu to pick a supplier company name (autofills phone number and address), then use a drop down menu to pick the health risk (autofill of new picture). I would also like an option that if the supplier information is not on the drop down list then you can click a button and add a new supplier. Also a button so you amend the supplier information if it is out dated.

I know this is very basic thing so if you know of a online course that deals with it feel free to comment it.

Many thanks for reading!

Cheers,

Hottest Fudge

 

[RainLover]

First of all welcome to the Forum.

You have supplied some good information but have left out the good stuff. For anyone to help we need to know what your problem is.

Have you started to design or are you still in the research phase.

 

[Hottest Fudge]

Hi Rain,

I am still in the research phase. The end result of this will be a much bigger database that will have to hold a lot more information. The purpose of this database is to research on how the end result would work.

The problem
The tables are layed out how I think it is logical (please comment if you see anything strange). I need a form that you can add a new substance to the database. This form would consist of a text box (to input the new substance name), 2 drop down boxes (to input the supplier company name and health risk) and 2 buttons to add a new supplier and to amend a supplier. How would I use a drop down menu on the supplier company name to then go on to create a new record in the registry that has that supplier reference? It is a very basic problem I think or I may be going about it all wrong.

Hope that clears things up, sorry for the confusion.

Thanks,

Hottest Fudge

 

[RainLover]

The answer to your question will be fairly simple.

It would be good if you could post a copy of what you have so I can have a proper look.

There are lots of tricks that you will learn and use as time goes on. They are simple to install yet a bit difficult to explain here without more information.

 

[Mile-O]

How would I use a drop down menu on the supplier company name to then go on to create a new record in the registry that has that supplier reference?

You would need to use VBA on the combobox's OnNotInList event.

Effectively, when the event is triggered, you would open up your Supplier admin/data entry form, and get the user to enter the details. Then you return a value to the event of Response = acDataErrAdded to indicate that the value is now in the database.

There will be plenty of examples in this forum's archives, if you search.

 

[RainLover]

O

Thanks for jumping in.

 

[Mile-O]

One other thing I would suggest, looking at your relationships, is the possibility of extending your table design...

What I mean is that you are assigning only one health risk to a substance. Is it feasible that health risks to substances is actually a many-to-many relationship, in that one substance can have many health risks, and one health risk can apply to many subtances?

Since this may be the case, then you don't want to be repeating Health Risk symbols either, so a new table may be required her.

(I've also renamed Register to Substances, since a Substance seems to be the entity that the table actually represents.)

 

[Hottest Fudge]

Thank you very much Mile-O and Rain.

I have uploaded a version of the database with sensitive information removed, please see attached.

With regards to multiple health risks for a substance. I am aware of this and in the final version I was going to use quite a clunky solution. I would have multiple columns for the Health risks. For example, 4 columns health risk reference 1,2,3 and 4, in the registry table. The end result for this data base would actually be an easily accessible (update and input substances easily) and report for each substance (create a COSHH assessment that you can print off in a standardised format). In the end report I would have a section for health risk symbols and there would be a maximum of 4. I know this is a clunky solution, but it seems to be relatively robust (I have never seen a substance with for than 4 health risk symbols associated with it. I am open to suggestions!

Many thanks for your help so far!

Cheers,

Hottest Fudge

P.S when you open the forms on the attached document it may not like it as there are links to health risk symbols that are on my computer that it will get confused with.

 

[Mile-O]

I am aware of this and in the final version I was going to use quite a clunky solution. I would have multiple columns for the Health risks. For example, 4 columns health risk reference 1,2,3 and 4, in the registry table.

Yeah, that's NOT the way to go. The reason? If there's only 1 health risk, that's three columns blank and wasting space. If there's a need for a 5th health risk, then you have to add a new field, amend all queries referencing this table, amend all forms referencing the table, amend all reports referencing this table, and amend all VBA referencing the controls on these forms and/or reports. Or you just have a table, as proposed by myself, where you can store as many or as little health risks against each substance, and in a table that you can easily count across without having to add the total of each of columns 1, 2, 3, 4....5 (!) ......n (!!!!!!) to get an accurate count.

In the end report I would have a section for health risk symbols and there would be a maximum of 4. I know this is a clunky solution, but it seems to be relatively robust (I have never seen a substance with for than 4 health risk symbols associated with it. I am open to suggestions!

My suggestion covers that. To ensure a maximum of four, you would just do a check in the form (via VBA) to check if there are four risks in the RisksToSubstances table and, politely, inform them that the maximum has been reached.


The key word here - as it is to many fledgling designers - is normalization. Get to grips with this, you'll begin to see database design a bit easier and in the eyes of a designer, rather than, as many do, a transfer of spreadsheet thinking to Access because, well, one of those tables when opened kind of looks like a spreadsheet...

 

[RainLover]

Mr Fudge

You need to look at naming conventions. There are some in our archives for you to look at. Choose what suits you.

But most importantly only use alpha numeric for names.

No Spaces.

No special Signs like !@#$%^&*()_+

This requires urgent attention

 

[Mile-O]

Looking at your sample database, I'll point out the following....


1. Get in the habit of using prefixes for your objects. So, rather than Supplier, use tblSuppliers; a) because adding tbl lets you know at a glance that are referencing a table; and b) it's a damn good practice. Then, if you want to do a query on Supplier, you can have qrySuppliers. A form? frmSuppliers.

2. Do not use special characters or spaces in your object or field names. Just because Access lets you, doesn't mean it's a good idea. While it may look good just now, it can cause issues in queries, code, etc. later as you need to remember to use square brackets around anything named this way. So a field called SupplierReference could be referred this, while [Supplier Reference] would be that way, because of the space. Each field does have a Caption property, so you can name that how you want things to display. Under the bonnet, keep things readable, but compressed.

3. Still going to need a junction between health risks and substances.

4. Why are you even calling that table Register, when it's Substances that it holds?

 

[Hottest Fudge]

Hi Rain,

I am not sure how I go about changing my username, also I can't find a thread about naming convention.

Hi Mile-O,

1. Yes I noticed that too, I will go back and change it.

2. Thanks for that information it is useful to know that I haven't done too much coding in the past so wasn't aware of it.

3. Have you got anything specific in VBA I could go and learn or is it just everything?

4. Hey man, it's a register of substances and all the specific reference numbers!

Thank you both for helping!

Cheers,

Hottest Fudge

 

[Frothingslosh]

I am not sure how I go about changing my username, also I can't find a thread about naming convention.

RainLover meant names in your database, not your username. Your username is fine.

Here's a good post from this forum about naming conventions:

http://www.access-programmers.co.uk/forums/showthread.php?t=225837

Also, please note the author. He knows what he's talking about.

 

[RainLover]

Hi Rain,

I am not sure how I go about changing my username, also I can't find a thread about naming convention.
Hottest Fudge

Remind me. What did I say about User Names,


Attached is a paper on Naming. Hope it helps.

 

[RainLover]

Frothingslosh

You beat me to the punch by that much.

Thanks for the complimentary words.

 

[Hottest Fudge]

Ah ha, thank you very much for clarifying that! When you say naming convention and my mind automatically goes to my usernames shows how un-ms access orientated my mind is!

Apart from the naming convention. Have you got any other pieces of knowledge you would like to disperse, links to other threads or even phrases (e.g VBA related to combo boxes) would be nice anything you think would be applicable to my project is great?

Thanks,

Hottest Fudge

 

[Mile-O]

3. Have you got anything specific in VBA I could go and learn or is it just everything?

My point #3 was table design, not VBA. Best to get table design locked in before moving on to queries and forms, never mind VBA.

4. Hey man, it's a register of substances and all the specific reference numbers!

What is a register of substances? Your database or the table? I think the database is the register and I think the table contains substances and information pertinent to them.

 

[Hottest Fudge]

Yes, Mile-O but could I please get help with the forms side also because I am really struggling on how Access would be able to interpret this one column (of health risk references) and then going on to use it in the inputting of a new substance. I would like if the operator of this database would just choose a health risk from a drop down menu then if another one is needed then there is a button or something? How would I go about doing that?

With regards to the register thing. I really don't mind. Personally I would say that the database is a COSHH register and the table is the substance register, but I will go with you suggestion of just calling it substances. Also writing register so much I am starting to get jamais vu so I'm placing a ban on the word in my database from now on.

 

[RainLover]

Yes, Mile-O but could I please get help with the forms side also because I am really struggling on how Access would be able to interpret this one column (of health risk references) and then going on to use it in the inputting of a new substance.

A Form has more that one setting. You choose the one to suit the current situation.

As Mile-o said, there is more to do at the design level before moving on.

 

[Mile-O]

Multiple health risks form in action....see attached.