[jobie79]

I have two tables (Clients / Quotes) and I want to create a report for each client displaying a list of quotes that has been sent to them. The 'Quotes Form' can have up to 6 clients being priced the same project with a 7th client field for the winning client.

So the relationship view looks like this;

Company Name -> Client Q1, Client Q2, Client Q3, Client Q4, Client Q5, Clent Q6, Project Won By.

The problem I have is that access seems to want the company name in each (a logical AND function) however I want access to check the seven coloums in each record and run as a logical OR function. So if Bill's Construction is in Q2 it will still show up under Bill's Construction in the report and under Fred's who was entered into Q1.

Any Company Name can be selected and entered into any of the Client Q1 - Q6 or Project Won By and in any order.

Grateful for any ideas and assistance.

[ScottGem]

It looks like your database is not normalized properly. Having fields like Client1, client2, etc,constitutes a repeating group which violates normalization rules.

What I don't understand is the relationship here. I could understand if you were accepting bids from Client1, 2 etc. But that doesn't appear to be what you are saying. So, if you can explain how your business operates a bit clearer I can suggest how to make this work.

[jobie79]

Scott
Apologies. Hopefully this will clear it up a bit.

We are a subcontractor to a number of builders and the projects we price can be quoted to any number of them.
For example; Clients 1, 2, 6,8 & 10 can all be pricing project 1.
Clients 2,6 & 7 can all be pricing project 2. so on and so forth.
What I need to do is to be able to create a report showing that I have priced Project 1 to client 1. Projects 1 & 2 to client 2, same for client 6, and project 2 to client 7.

In summary we price any one project to multiple clients. Any client can receive quotes for multiple projects.
Need to produce a report per client showing which projects have been quoted to that client.

Thanks for your help.

[ScottGem]

This still isn't making sense. In my world a contractor can send a job out for bid to multiple "clients". But I don't understand quoting the same project to multiple clients.

But I think you have a many to many relationship here. One client can be associated with multiple projects and one project can be associated with multiple clients. So you need three tables. A projects table and a clients table and a junction table to manage the many to many relation.

The junction table will have a structure like:

ProjectID
ClientID

You will have a record for each project offered each client.

[jobie79]

Scott
Hope this helps

[jobie79]

[img=http://img825.imageshack.us/img825/5862/30237950.th.jpg]

[ScottGem]

But that illustrates what I have been saying. You as the subcontractor get a job from a builder. You then go out and ask for bids from other contractors. Some bids may be duplications, others may even be a sole bid. For example, you may ask for bids from 4 drywall installers, 3 electricians, 5 plumbers and one cable installer. So you have a Project table that defines the whole project, a Jobs table that defines each part of the Project (i.e, drywall, electrical, etc.), a Builders table that lists the firms you send out bids to, and a Bids table to list each bid sent; what Job it was for, who it was sent to and the results.

That's fairly standard.

[jobie79]

[IMG][img=http://img825.imageshack.us/img825/5862/30237950.th.jpg][/IMG]

[jobie79]

Scott
Could you have a look at the following and let me know if I have this right? I can't get my head around this. :(

http://img683.imageshack.us/i/relationships.jpg/

[ScottGem]

Have you read any articles on Normalization? Because what you have set up is not normalized. Your Client List is the only thing that is correct, except that has some issues too. It is not good practice to have spaces in object names.

Go back to post #7 and see the structure I suggested there. And read up on normalization.