Hope Someone can help, I am trying to do a database for work and have got myself stuck, basically I have two tables with a one to many relationship the fields are as follows.
Main Table
ID
Client
Jobtype
Inserts
Date Recieved
DateCompleted
jobdoneby
This table holds the main job details, however each job may have one or more logos involved so I have a seperate table to store the logos as follows:-
ID
Logo
jobID (this field takes the value of the main table Id and therefore links the logo to the correct job)
I have set it up as a subform for input so that the user can add many logos against one job.
Right I hope this is clear so far... Now for the problem
I want to be able to filter records, on fields from the main table e.g. list all jobs by a particular client, which is OK I can do this, but I also want to filter further by e.g All jobs by a pariticular client that have say "shell" and "esso" as related records in the logo Table, say I have the following data
ID Client Jobtype Insert Datereceived Datecomp
1 TCP Oval None 17/09/01 19/09/01
2 TCP Oval Ship 17/09/01 18/09/01
ID logo Jobid
1 Shell 1
2 Esso 1
3 Shell 2
Now what I would want to do in this case is select all jobs where the client is TCP and the logos are shell AND Esso i.e I would not want the filter to pick up job two as it only has Shell as an associated logo and not "shell" AND "esso".
Hope this is clear, if not I will try and explain further on request.
Anyway is this possible? or if it is not possible under the existing structure is there an alternative design that would allow me to filter as required. the main problem I could see was that I could not see any other way to allow me to enter multi logos agianst one job.
Thanks for helping
Main Table
ID
Client
Jobtype
Inserts
Date Recieved
DateCompleted
jobdoneby
This table holds the main job details, however each job may have one or more logos involved so I have a seperate table to store the logos as follows:-
ID
Logo
jobID (this field takes the value of the main table Id and therefore links the logo to the correct job)
I have set it up as a subform for input so that the user can add many logos against one job.
Right I hope this is clear so far... Now for the problem
I want to be able to filter records, on fields from the main table e.g. list all jobs by a particular client, which is OK I can do this, but I also want to filter further by e.g All jobs by a pariticular client that have say "shell" and "esso" as related records in the logo Table, say I have the following data
ID Client Jobtype Insert Datereceived Datecomp
1 TCP Oval None 17/09/01 19/09/01
2 TCP Oval Ship 17/09/01 18/09/01
ID logo Jobid
1 Shell 1
2 Esso 1
3 Shell 2
Now what I would want to do in this case is select all jobs where the client is TCP and the logos are shell AND Esso i.e I would not want the filter to pick up job two as it only has Shell as an associated logo and not "shell" AND "esso".
Hope this is clear, if not I will try and explain further on request.
Anyway is this possible? or if it is not possible under the existing structure is there an alternative design that would allow me to filter as required. the main problem I could see was that I could not see any other way to allow me to enter multi logos agianst one job.
Thanks for helping
)