Tables and forms

[Art]

Hi
I'm in the process of creating an invoicing database to
keep track of clients and services provided to clients. I
have tables and forms created. I have a 1 to many
relationship established between the client and services
tables, where one client can have many services. In my
services form, how do I set it up for multiple services (I
work on their computer 3 different times)? I presently
have fields for Date, Hours, and Description, plus the
Autonumber field and CustomerID. How do I get it to show
the 3 different service calls all at once, but not other
clients' services, or past services for this client?

I hope that I've explained this well enough. If someone
can help would be great. Or if I could be directed to a
good website explaining this would also be great.

Thanks in advance.

Art

 

[John W. Vinson]

Hi
I'm in the process of creating an invoicing database to
keep track of clients and services provided to clients. I
have tables and forms created. I have a 1 to many
relationship established between the client and services
tables, where one client can have many services. In my
services form, how do I set it up for multiple services (I
work on their computer 3 different times)?

This is almost surely a *many to many*, not a one to many
relationship: if you have a one to many relationship between clients
and services, it implies that each Service can be provided once and
once only, and to a single client.

I presently
have fields for Date, Hours, and Description, plus the
Autonumber field and CustomerID. How do I get it to show
the 3 different service calls all at once, but not other
clients' services, or past services for this client?

You need *another table*: the three tables would be

Clients
CustomerID
LastName
FirstName
<other contact information as needed>

Services
ServiceID
ServiceDescription

ServicesProvided
CustomerID <<< link to Clients, who got the service
ServiceID <<< link to Services, what did you do
ServiceDate <<< don't use Date as a fieldname, it's reserved
Hours
Description

<etc etc.>

This can be conveniently displayed using a Form (for Clients) with a
Subform (based on ServicesProvided). If you wish, you can base the
Subform on a query selecting just the records for a particular
ServiceDate - one easy way to do this is to have an unbound textbox
txtPickDate on the mainform, with a Default property of =Date(); set
the Master Link Field to

CustomerID;txtPickDate

and the Child Link Field to

CustomerID;ServiceDate

 

[Raistlin]

This is almost surely a *many to many*, not a one to many
relationship: if you have a one to many relationship between clients
and services, it implies that each Service can be provided once and
once only, and to a single client.

<SNIP>

Or could it be a *One to Many* relationship because One client can have Many
services?

Raist.