Newbie table design query

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Hello All,

What I have is a need to provide a constantly updated database of clients.
Some clients we know about and have records on and some drop in and use our
services as and when they like. What I am being asked to provide is a means
of recording weekly activity that will note and add any new clients to the
main database for mail shots etc. whilst providing a record of usage for all
clients.

Now, before we go any further is there a template somewhere I could
copy/change to my use or that would give me some clues as to how this is
best achieved?

I presumed I need an append query to enter the new clients into the main
client table but is there a way of doing so without getting the error
messages about being unable to accept duplicates and just make it accept
non-duplicates? What I am basing this whole thing on is a client code. This
is a number that clients are given when they first use our services. On the
main Client table this is my Primary Key and it does not accept duplicates,
in the Activity table it also has a client code field but one that will
accept duplicates therefore I need an easy way of transferring between the
two.

So far I have got the two tables idea going using sub-forms to view activity
for each client and constructed an append query to add the new ones as I go
and it works sort of OK-ish but I feel it isn't the best answer, do you ever
get that feeling....

Many thanks,

Martin
 
(snip)
I presumed I need an append query to enter the new clients into the main
Click to expand...
client table

No, you would not normally enter new records into a "main table" by writing
an append query. You would use a form that is bound to that table. Create
such a form using one of the wizards. The created form will let you view,
add, edit or delete records in the bound-to table.

What I am basing this whole thing on is a client code. This
is a number that clients are given when they first use our services. On the
main Client table this is my Primary Key and it does not accept duplicates,

Correct.

in the Activity table it also has a client code field but one that will
Click to expand...
accept duplicates

Not correct! The Activity table should have a so-called "composite"
(multi-field) primary key. That composite key should have *two* fields. The
first one is, the client code from the main table. The second one is, a
field in the activty table which distinguishes the different records for a
given client. For example, if a client can only perform at most one activity
on any given date, then, that field could be Activity Date. So the activity
table's primary key would be the *two-field* key: client code + activity
date.

So far I have got the two tables idea going using sub-forms to view activity
for each client
Click to expand...

That is the way to go. Use the LinkMasterFields and LinkChildFields
properties of the main form's subform control, to link the subform to the
main form. Check online help for those properties, if you have not set them
yet.
 
Hello TC,

Thanks for the reply.

client table

No, you would not normally enter new records into a "main table" by writing
an append query. You would use a form that is bound to that table. Create
such a form using one of the wizards. The created form will let you view,
add, edit or delete records in the bound-to table.
Click to expand...

Could I combine the two? Could I have a form to enter the activity that
would bring up client details when I entered the client number? This would
be more helpful as we don't normally give out these numbers, they are given
at any number of outlets within the organisation, and this would show me if
the person was an existing or new client. (Note: the client list we are
talking about is possibly 2000 people).
accept duplicates

Not correct! The Activity table should have a so-called "composite"
(multi-field) primary key. That composite key should have *two* fields. The
first one is, the client code from the main table. The second one is, a
field in the activty table which distinguishes the different records for a
given client. For example, if a client can only perform at most one activity
on any given date, then, that field could be Activity Date. So the activity
table's primary key would be the *two-field* key: client code + activity
date.
Click to expand...

Interesting, I haven't come across a two-field key before. The solution I
have at present is just a diary of events logging the client number against
an activity and where the new ones go into the main table they are
highlighted by the lack of personal details. I then go back and look up the
number (held on another database not available to me) and enter the
additional details.

I think I'll go and have a browse around the help files and see if any of
this makes any sense to me!

Thanks for the advice.

Martin
 
What I have is a need to provide a constantly updated database of clients.
Click to expand...

"Constant updating" a database is usually the job of one or more
people.
Some clients we know about and have records on and some drop in and use our
services as and when they like.
Click to expand...

Some clients you don't know about?
What I am being asked to provide is a means
of recording weekly activity that will note and add any new clients to the
main database for mail shots etc. whilst providing a record of usage for all
clients.
Click to expand...

Record all activity instead. Report weekly activity, based on all
activity. New clients should almost certainly be added to the "main
database" as they become a client.
Now, before we go any further is there a template somewhere I could
copy/change to my use or that would give me some clues as to how this is
best achieved?
Click to expand...

There's no shortcut to learning how to design, build, and maintain
either databases or database applications. If you're going to develop
software, you're going to become a software developer.

Best clues are in the best books. Others will post ideas if you don't
have time to read books.
What I am basing this whole thing on is a client code. This
is a number that clients are given when they first use our services. On the
main Client table this is my Primary Key and it does not accept duplicates,
in the Activity table it also has a client code field but one that will
accept duplicates therefore I need an easy way of transferring between the
two.
Click to expand...

Think about this for a minute.

What comes first--the client or the client's activity?
 
(snip)
It's hard to learn about some of these issues just by using the help files.
Try reading this article (if you haven't already):

http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC
Click to expand...


Hi TC,

Thanks for the tip, I've just got it and will digest it tonight.

TBH I have had trouble finding a good book about Access. I have tried
several and got some good stuff out of some of them but I haven't found that
one that really 'opens the door'. What makes it worse is I don't seem to
know anyone who uses it to any depth, unlike say Excel or Word or even
Photoshop or Dreamweaver where I have found plenty of good books and helpful
users. Still with this group seems full of good advice so perhaps things are
on the up...

Martin
 
Hello Mike,
clients.

"Constant updating" a database is usually the job of one or more
people.
Click to expand...

Very true. The main company database that holds the ALL the records of ALL
the clients our organisation comes into contact with has scores of people
entering data into it. The problem I have is disseminating some data about
our little bit of it.
Some clients you don't know about?
Click to expand...

Not until they use our services no. They can enter the organisation from
many points, use other services and only at some later date come into
contact with us.
Record all activity instead. Report weekly activity, based on all
activity. New clients should almost certainly be added to the "main
database" as they become a client.
Click to expand...

For reason I wont go into this isn't feasible. The bit we are particularly
interested in at the moment has been selected so we can examine usage and
frequency of a particular area of our activity.
There's no shortcut to learning how to design, build, and maintain
either databases or database applications. If you're going to develop
software, you're going to become a software developer.
Click to expand...

Couldn't agree more. What struck me was this looks an Access project that
must have been done before and I didn't want to start off wrong and find
months down the road that I was in a cul-de-suc because the design I'd
adopted was poor. What I was looking for was an overview as to what was
possible and to get the basics right. Perhaps then as my knowledge grows so
can the functionality of my database.
Think about this for a minute.

What comes first--the client or the client's activity?
Click to expand...

Interesting. To add a metaphysical twist the client and/or their activities
could all be happening without our knowing about it yet!

Thanks for reply,

Martin
 
Back
Top