project database help needed

[mr.bungle]

At work I maintain an enquiry/project listing on excel - it has 12 headings,
and about 900 or so records.

I manage the data using filtering, which is fine, but feel it is time to
convert it to Access.

This is a simple task, but I would like some advise on the design. i.e. one
large table, or multiple related tables?

To help, this is the sequence of entry on Excel:


Enquiry received from client:

1. enter Enquiry_ID see note #1
2. enter Enquiry_Date
3. enter Client_Name
4. enter Site_Location
5. enter Project_Name
6. enter Project_Type

If enquiry is successful (i.e. we receive an order) record updated thus:

7. enter Project_ID see note #2
8. enter Project_Date
9. enter Status
10. enter Engineer
11. enter Value
12. enter Client_Order_No



Note #1
=======
Currently, Enquiry_ID is a unique value in the following format:

yymm.nn

where yy = year
mm = month
nn = sequential number

for example, the first Enquiry_ID allocated on 1st Dec 2004 would be
0412.01, then 0412.02 etc. On 1st Jan the next ID would be 0501.01

Ideally, this number would be automatically generated on the database when a
new record is created.


Note #2
=======
The Project_ID is of the following format:

XX123789

where XX = constant 2 letter combination
123 = 3 digit number which changes annualy
789 = sequential number

Again, can this be generated automatically?


Thanks in advance for any advice

DD

 

[Jeff Boyce]

If you've not worked with Access, or relational databases before, this is
NOT simple. There's a fairly steep learning curve associated with both of
these.

The first step would be to step away from your current process and consider
the underlying data and relationships.

What about your current situation is "forcing" you to make this move?

 

[John Vinson]

At work I maintain an enquiry/project listing on excel - it has 12 headings,
and about 900 or so records.

I manage the data using filtering, which is fine, but feel it is time to
convert it to Access.

I agree with Jeff. Access is indeed probably a better choice for this
application - but Access IS NOT A SPREADSHEET, and you will need to
substantially rethink the design! For one thing, the kind of
"intelligent key" you propose is generally not considered proper;
storing multiple disparate chunks of information in a single field is
NOT a good idea.

If you want to move to Access, I'd suggest starting by turning off
your computer (or at least moving into another room <g>) and getting a
pencil and pad of paper. Write down the Entities - real-life things,
people, events - that are of importance to the application; identify
their Attributes (atomic, discrete 'chunks' of information) which
pertain to each entity; identify how the entities are related to one
another. Each type of Entity will have a table; each attribute will be
a field. Read up on the concepts of "normalization" and "relational
integrity" at some of the links on http://www.mvps.org/access before
you get in too deep. It's very easy to bring spreadsheet thinking into
a database - and it can lead to a severely flawed database design!
Excellent spreadsheet applications can be bad database applications,
and vice versa; so check your preconceptions at the door.

If you'ld like to proceed, by all means post back. As I say, it's a
good idea... but it may not be as simple as you hope.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[mr.bungle]

Although my experience with Access is limited, I wouldn't consider myself a
beginner. Many moons ago I worked quite a bit with Paradox, also a
relational database, so the idea of tables, forms, queries, relationships
etc. I understand no problem - I was just after some specific pointers that
I could apply to my situation, without having to trawl through books.

I am not being forced to change - bearing in mind the number of records, it
was my idea to consider using Access.

Maybe I should stick with Excel, but deep down I know Access is the way
forward.

Thanks for you reply in any case.

DD

 

[mr.bungle]

Thanks for taking the time to reply.
Lots of food for thought their - time to get my thinking cap on!

Thanks again.

DD

 

[Larry Daugherty]

Since issues such as you are discussing with your prospects/projects rarely
occur in isolation I endorse your idea of starting to dive into Access,
particularly where you have some relational experience already. Bear in
mind that there are many differences between the two!

In general, the number of tables isn't an issue. The reason for tables is
to correctly represent the entities in your application and make it possible
to Relate them. That usually means more tables than you first thought would
be required. People not used to using relational databases believe
intuitively that the fewer tables the better; somehow more conservative.

At first blush it looks like you'd have tables for Client, Enquiry, Project,
Engineer and Status. tblClient, tblEngineer and tblStatus are lookup tables
to ease the typing load and to reduce data errors. Of course tblClient will
contain information that isn't necessarily a part of Enquiries or Projects.
tblClient may have child tables for addresses, etc. If there is a high
intersection of information (other than Client information) between an
Enquiry and a subsequent Project you may have one or more other subordinate
tables with that common information so that there is no need to re-enter
data when an Enquiry converts to a Project.

If the ways to solutions don't come quickly to you, post back here or in
microsoft.public.access.tablesdesign

You'll want to know about form/subform construction and probably the
NotInList event handling to put new information (rookie Engineer) into your
table without having to stop your data entry to add the name to tblEngineer
and then get back to data entry.

As you're trying to work these things out, it helps to use a pencil and
paper and graphic design techniques and to keep notes on things you have
considered and things you have yet to resolve.

HTH