Eliminating duplication

[Ruth]

Hi
Beginning a Database - new to Access
Table 1 consists of client details which includes several
fields of common info (i.e. same for each different
client - column of exact same info). Table 2 also
contains common info, but different to Table 1. This
common info needs to be used/accessed in quiries, forms
and reports. Is there a way to have it available without
duplicating it with each client?
Does something like Excel's 'Named Range' exist in Access?
Will appreciate your expert advice.
Merry Christmas and Happy New Year
Ruth

 

[John Thow]

Hi
Beginning a Database - new to Access
Table 1 consists of client details which includes several
fields of common info (i.e. same for each different
client - column of exact same info). Table 2 also
contains common info, but different to Table 1. This
common info needs to be used/accessed in quiries, forms
and reports. Is there a way to have it available without
duplicating it with each client?
Does something like Excel's 'Named Range' exist in Access?
Will appreciate your expert advice.
Merry Christmas and Happy New Year
Ruth

Not entirely sure what data you have, but lets assume it's clients and
services. You need 3 Tables:-

1) Client: Primary Key ClientID; Fields specific to clients.
2) Service; Primary Key ServiceID; Fields specific to services.
3) ClientService: Primary Key CSID; Foreign Keys ClientID & ServiceID.

This enables you to link each client to the service or services provided to
them without replicating the service data You can query what services a
particular client has had and which clients have been provided with a
particular service.

Something like that?

Happy Yule!

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Ruth]

Thanks for your help John
To have this duplicated info in a table linked to Client &
Services Tables I 'need to establish a link between fields
that contain common info with data type and length the
same' (my understanding from reference book). In so doing
I am duplicating the info which really only needs a table
of one row, but if it is only one row, it is not the same
length and so cannot be properly linked - or have I got
this all wrong?
Table one has 15 fields, consisting of an ID field, 11 of
varied client info, and 3 containing data that is common
to each client. Table two is the same except that the
common data for each client is different from table 1. It
really doesn't matter if there is this duplication, just
wondered if there was a way around it.
Thanks

 

[John Thow]

Thanks for your help John
To have this duplicated info in a table linked to Client &
Services Tables I 'need to establish a link between fields
that contain common info with data type and length the
same' (my understanding from reference book). In so doing
I am duplicating the info which really only needs a table
of one row, but if it is only one row, it is not the same
length and so cannot be properly linked - or have I got
this all wrong?

In my example, the 'common info' which provides the links are the keys
ClientID and ServiceID. These should be type autonumber and indexed (no
duplicates) in their 'home' tables. In the ClientService table, the key CSID
- also autonumber & indexed no duplicates - is irrelevant. The data you need
there are the ClientID and the ServiceID both as type number. [For clarity,
you might call them CSClientID and CSServiceID.] Both AutoNumber and Number
in reality are the same data type (Long Integer). The ClientService table
provides the means of establishing a Many-to-Many relationship between Clients
and Services. Ie each Client may be the recipient of one or more Services;
each Service may be provided to one or more Clients.

Table one has 15 fields, consisting of an ID field, 11 of
varied client info, and 3 containing data that is common
to each client. Table two is the same except that the
common data for each client is different from table 1. It
really doesn't matter if there is this duplication, just
wondered if there was a way around it.

I don't quite understand what you are trying to achieve there.... If you
have 'common data' for each client which is different for the same client
between the two tables, it strikes me that it isn't actually 'common data'.
Common data for a client would be things like Title, Forename, Middle Name,
Last Name, some address lines, ZIP/Postcode, Phone Number etc. etc. Ie things
that can apply _only_ to a _single_ instance of the entity 'Client'.
I suspect your data are not normalised. Perhaps if I knew the data you have
in each of your tables - and its purpose - I could be more helpful. (No
promises and it might take some time: It's holiday time!)

Thanks

[Chopped old stuff]
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!