Names and multiple addresses

[Evi]

For my husband's small gardening business, customers are usually identified
largely by their address. The customer may be known only as Madge (sometimes
called Margaret) or Mr Smith (or his partner, Miss Jones - it doesn't really
matter) or in the case of
a 'just come and cut my grass when you've finished my neighbour's garden'
they may even be totally anonymous. Of course, the address can be filled in
fully. Thus the normal structure of a Name table and an Address table could
be difficult to work because Anon of 19 Baker street will be a different
Anon from the one at 21 Sidney Street.
The combo box in the Jobs form which chooses the client has the address as
its first field, rather than the more usual name fields
Having name and address in one table works for 97% of jobs but a couple of
housing associations sometimes ask my husband to do work on one of several
houses which belong to them. The housing association pays (or forgets to
pay) the bill. So here we have one customer with several addresses.
Have you any recommendations on the best way to work this set up?
If I have the Address table linked to the Jobs table rather than the Name
table being linked to the Jobs table will there be problems with forms not
being updatable because Jobs will be linked to the Many table (Address)
which must then be linked to the One table (Name)?

Evi

 

[Larry Daugherty]

Hi Evi,

It would seem that you're coming at this from an easy going point of
view and running into the reasons why those of us who do this kind of
work are characterized as "anal retentive".

For a database application to serve well it must be designed well.
Most people who are neophytes with Access usually have years of use of
Excel and try to carry that kind of thinking over to Access. It
doesn't work that way...

I think you're still hoping to keep a fairly casual organization and
still get good results. It just won't work out that way. Everything
that you don't think through and do correctly at first you will have
to really think through later so that you can preserve the
functionality you already have and fix the new problem or add new
functionality.

It looks to me like you need several tables but the first three
important ones are People, Organizations, Addresses. You might choose
Customers, Contacts, Addresses.

Addresses was your point of pain in posting here so link Addresses to
Customers by using the structure in tblAddress just below:

tblAddress:
AddressID, CustomerID, Address1, Address2, City, ST, Zip, Status
(active), LastVisit, NextVisit, Notes
Note that you might need addresses for Bill to, Ship to,
Work Site, etc & you might need more than one of
a given type as in the multiple worksites for the
associations.

If you have addresses for everyone then you can do searches and
reports based on the address and come up with some pretty good
results.

For people whose names you don't know, assign them names: Anonxxxx.
count up in sequence so that you don't try to use the same number
again. In the note for that name entry, explain how you come to be
dealing with them... Eventually you'll find the name. When you do,
replace Anonxx with the real name. Enter the phone number, etc.

Your work forecast would simply be to list all of the "NextVisit"
entries of every active work site. Most likely you'll need an easy
means to shift some things from one day to another and to even put
jobs in order of execution for a given run from home base.

When the jobs are posted for billing the "NextVisit" date gets moved
to the "LastVisit" date. The next visit interval can be set by
various factors or they can be set by some standard offset in your
system. There could also be minor variations depending on the rate of
growth currently being experienced. It does vary.

The Jobs table may not need to exist.

The jobs to schedule are those that are active and that have a value
in NextDate.

It goes on and on, the rationale for just one part of your application
is above. Your mileage may vary.

Post back with more questions...

HTH

 

[Evi]

Thank you for your reply Larry but I think you may have missed the point
because you've made certain assumptions both about the database and about
myself.
The database is not used for planning jobs - but for recording completed
jobs (income) and expenditure either for that job or for the business itself
so Jobs table is an integral part of the structure.
You assumed 'I think you're still hoping to keep a fairly casual
organization'. I'm not sure on what you based that assumption either. I
haven't given you the whole details of the database because I wanted to keep
to the point of my question - if you could think of any problems I might
counter if I join the Jobs Table to
the Many table (Addresses) instead of the more usual structure of
joining to the usual One table (Client).

I bit the bullet, tried it out and it is working fine so far. Thank you.

Evi