Is Access the right way to go?

[Airlea]

Hi everyone,



I've been given the task of finding a way to create a massive database with a lot of variables. Unfortunately, the last time I used Access was probably about 14 years ago. I have a few questions as I'm still in planning mode.

1. It will be a massive database. Thousands of names.
2. It will have a lot of dates involved. Currently I'm using excel for one master list, however, my employer is looking to create something that not only has my tracking list but everything in between.
3. We track data such as when someone's car insurance is due. Does Access have queries (or can create one) where I can pull a report saying "during the month of December, these following people will have to renew their car insurance".
4. It will also have a lot of fields attached to one person. For instance:



Some of the fields will be simple yes/no while others are dates.

5. Is it possible to pull multiple information such as "during the month of December, the following people have to renew these items" (example car insurance, first aid, and performance review are coming due)

So, a lot of data with a lot of variables. Sort of like an HR database.

Thoughts?
Greatly appreciated.

Sincerely,
Airlea

[June7]

1. Thousands is really quite small - hundreds of thousands or millions is in the 'large' range

2. okay, managing data is what Access was developed for

3. yes

4. okay

5. not easily with the data structure you show, consider:

tblPeople
ID
Surname
FirstName
SupervisorID

tblDocumentation
PeopleID
DocumentType (checklist, license, insurance, first aid, performance)
DocumentExp (expiration date)

[Perceptus]

I would use access to get the ball Rolling. Especially if you already have data stored in a spreadsheet like Excel.

[ssanfu]

Welcome to the forum......

If you want to brush up on Access, work through these tutorials:
http://www.rogersaccesslibrary.com/forum/forum46.html


Tip:
Use pencil/paper, whiteboard, stickies, etc. to design your table structures BEFORE you start creating in Access.
Will save you lots of time (and hair).

[orange]

I'd go along with the other comments.
Work through some of the tutorials mentioned (RogersAccessLibrary) for a refresher on table design, normalization and relationships.
Get yourself a data model -- it is logical representation and can be implemented physically in any database system.
Make up some test data and some test scenarios to test your model.
see my stump the model info
Document what things are involved and what they mean.
If you have processes you can use data flow diagrams to record info for analysis.

Once you have done some data gathering and analysis, you'll be in a better position with the project and your own ability to make decisions on how to develop the application.

This youtube video (26 minutes) will give you a good overview of the processes involved in creating a database to support a business. It isn't car insurance, but it will give you a quick refresher on analysis and design considerations.

Good luck.

[ssanfu]

@orange,
Been raining so much I ordered lumber to build an ark..... errrrr a boat. Yeaaaaaah, that's it - a boat. But I don't have any tape measures in cubits.

I think its time!

[orange]

Hi Steve,

Interesting my wife and I built a 25' trimaran many years ago. I ordered lumber from BC for ribs etc.
Ours was fiberglass over plywood.

[Airlea]

Thanks everyone for your feedback and advice. It looks like Access is probably the best way to go for what I need database wise. Looks like I'll be brushing up and using those tutorials!

One last question.... can queries/field searches do partial names? Example.... I want to find Sharron but it was entered as Sharon. If I do "Shar" will it pull full names with just "Shar"

[June7]

The username box will do pattern matching to show matches in the dropdown but cannot use wildcard. I do see a Sharron and many Sharon.

EDIT: I thought you were talking about searching the forum. But now I think you mean searching a table in your db.

If you provide "Shar" as search parameter then that is exactly what will be searched for. Use LIKE operator and wildcards to search for approximations, like:

SELECT * FROM table WHERE FirstName LIKE "Shar*";