Tables and forms

  • Thread starter Thread starter Art
  • Start date Start date
A

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
 
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)?
Click to expand...

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?
Click to expand...

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
 
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.
Click to expand...
<SNIP>

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

Raist.
 
Back
Top