What is the best way to design a database from an existing Excel spreadsheet?

[Nippy56]

Hi! I have volunteered to try and turn an Excel spreadsheet into a dadabase for a volunteer organisation whose members rescue and raise native wildlife.
It appears I have bitten off more than I can chew. I am a relative novice, not having used Access for the best part of 20 years.
The current spreadsheet has 14 sheets often repeating the same information (member name, address etc). Just the member sheet has 32 columns of information.
I believe some of the information should be obtained from running queries.

To date I have made a number of tables based on:

• member info
  
  • first name
  • family name
  • address
  • suburb
  • post code
  • mailing list inclusion
  • membership No.
  
  
• member contact
  
  • home phone
  • mobile
  • give out phone number
  • collect animals during day
  • collect animals during night
  
  
• status as a mix of:
  
  • Carer
  • Current member
  • Experienced
  • Inexperienced
  • Release site
  • Support only
  
  
• 6 different animal types
  
  • a separate table for different animals within each type

At the moment I am having trouble with my data entry form unable to access the data from the tables.

Some of the whizz-band this I'd like to do is to base certain field being accessable only if member is identified as a carer.
Then I would like to have the 6 main animal types appear if member is a carer
The as each animal type is ticked (checkbox) the subset of animals will appear. For example:

• animal type = Possum selected will have the subset appear where one or more of the subset can be selected
  
  
  
  • BrushTail
  • RingTail
  • MountainBrushTail
  • Glider

Is there anyone willing to help me with the design of this database? I hope my tags help.

Thanks

[June7]

We are all willing to help. Post questions as you have specific issues and hopefully someone will have a solution.

What do you mean by "having trouble with my data entry form unable to access the data" - exactly what happens: error message, wrong result, nothing?

To limit user access to features, will need a way to identify the user when they open the db. Can implement a login procedure or grab their Windows login USERNAME. This identifier will have to be in the Members table as well as their authorization level. Code pulls USERNAME, looks up member record, and sets visibility or disables controls based on authorization. Login process is common topic.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Micron]

Looks to me like you've made a decent start. However,

• collect animals during day
• collect animals during night

probably should be one field with day/night values since (I presume) the member can be only one or the other, in which case you'll have a lot of Null entries.

I believe some of the information should be obtained from running queries.

Yes and no. Forms and reports are the right way, but they can be based on tables or queries. I usually base everything on a query because they're more flexible.

If you can be a Carer and a member, then that part is not right. I can't imagine how [Release Site] fits into Status table.

Possum selected will have the subset appear where one or more of the subset can be selected

This seems like cascading combos are needed, not checkboxes.

I strongly suggest you research db normalization and get to understand it as much as possible. Then pencil out on paper how you think the tables should be designed and related. Then post your design details. No one can truly advise unless they understand the process that the db will support, so a synopsis of the requirements will help a lot.

None of what looks like table suggestions in your post contains any primary or related foreign keys. If you think you are struggling now, just wait until you try to make a poorly designed db work. Here's my usual links for novices (your "client" needs to give you the time it will take to do this properly).

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[CarlettoFed]

In order to help you you need to see the Excel file, so you should attach it.

[orange]

@Nippy56

In addition to the responses so far, I recommend that you spend ~45 minutes working through this tutorial from RogersAccessLibrary. He leads you through the process of reviewing a business description to normalized database design and explains each step. What you learn/relearn can be used with any database. Good luck.

[mike60smart]

Hi
Your expectation of a whiz bang is a Common Thread here and is referred to as Cascading Combobox's.

Use 1st Combobox to select an Animal then the 2nd Combobox only displays the subset associated with the Animal selected.

You will need 2 tables to setup the initial Animals & Subsets.

tblAnimals
-AnimalID - PK - Autonumber
-Animal - Text

tblAnimalDetails
-AnimalDetailID - PK - Autonumber
-AnimalID - FK - (Linked to PK from tblAnimals)

From these 2 tables you would then create a Main form based on tblAnimals and a Subform based on tblAnimalDetails

[Nippy56]

Just a quick thanks for the replies to date. I am currently reading through Orange's suggestion.
After reading all the replies I now realise just how little I understand about databases.
I am 65 and my learning processes are slowing down. I am struggling with some of the concepts.
When I played with mike60smart's cascading comboboxes my result was zip. Oh well! I'll try again after studying some more.

[CarlettoFed]

As previously mentioned, if you attach the excel file, a few data are enough for each sheet, it is possible to show you how to create the database structure.

[mike60smart]

Hi Nippy(?)

I think my suggestion for Cascading Combobox's has been mis-understood by you.

I only suggested that you need as a First step to create the 2 tables.

You would then populate both the tables using a Main Form / Subform setup.

Then in another unrelated table you would have fields where you would use the Cascading Combobox Method for Data Input.

[Nippy56]

As requested, the excel file. I have removed some names etc due to privacy.
I believe there is a huge amount of duplication of data. This is what I am trying to streamline.
Thanks
Nippy

[CarlettoFed]

SURNAME FIRST
CLARKE Beverley & Ray
LAURENT Melissa, Isabelle, Madeleine
what does it mean ?
in the first case, who are two people working together?
in the second case, who are three people working together?

If you are an Assistant, what are the fields you need to manage?
If you are a caregiver, what are the fields you need to manage?
Do other STATUS have to manage other fields?

it is necessary to describe in detail what you want to manage, for example:
a certain number of people make themselves available to carry out one or more tasks by recording the day in which they are performed.

[Nippy56]

CarlettoFed, this is not my spreadsheet. It was set up by someone in the organisation. As you can see from the spreadsheet the data is not in any way close to what is needed.
Double first names, Laurent etc are siblings, however they are carers for injured or orhaned animals.
I am trying to figure out the best way to organise the information. I don't believe the owner of the spreadsheet even knows how to use filters (queries).
My intention is to develop queries that will produce mailing lists, a count of members who care for ??? animal and other queries I haven't even considered.
Not sure where you got Assistant from.

Each carer is registered with the Government to care for specific animals only. There are no daily tasks. Status is the term used by the owner. It could easily be MemberType, so 1 person could be an Exp, Carer and Release site.
Myself, I am classed as Inexp, (new 2 month) registered carer for birds only and a release site for most animals due to the size of my property and location.

Hope this helps

[orange]

@Nippy56,

You have shown what data exists in Excel and that the data is not organized currently.
Have you tried to mock up a sample mailing list --just on paper, phony names etc? Just to see what data is necessary for the list?
Once you determine what you need, you can identify where that data currently exists and devise means to ferret out the data to meet your needs.

You might start by creating an up to date member list.
You may find this info helpful -- not directly an answer to your design question, but a proven approach as you move forward.