Issue retrieving data from 2 tables

[mkdrep]

I have a distributor table within an existing database that I use to keep track of quotations to each distributor for each project our distributors bid. It was simple to design 5 years ago when we only had to quote one mfg to each distributor. However, I now have to quote distributors multiple manufacturers and tried to develop a second "estimate" table to handle things. The issue arises when I want to view all the quotes I have for one distributor, no matter which manufacturer is involved.

I have set up a relationship between the [Distributors] table and [Special_lite Dist Quotes] and the [Distributors] table and [tbl-Unassoc-Quotes] as shown in attached (.jpg)
My intents is to go into the Distributor main form and click on an “estimate” button that will open a form to show all the estimates I have given a particular distributor. I have been able to do that when I only use the (1) [Special_lite Dist Quotes] table. However, things get pretty screwed up if I try to “add” the second table [[tbl-Unassoc-Quotes] to the underlying query.

Any suggestions as to how I might resolve this issue? Thanks in advance…..

Mark

 

[plog]

I think I speak for the forum when I say, 'Huh?' Distributors, projects, manufacturers, quotes, bids, estimates? I have no idea how many people are going to St. Ives.

You posted in the Table forum, your issue involves opening a form somehow and then a query comes into play at some point.

You need to explain 2 things to us:

1. In simple english (no database jargon allowed), explain the process this database models. Tell us about your business.

2. Write a concise one sentence question that we can help you answer.

And a note--it would be helpful if you didn't use synonyms. We know nothing about your business, when you use the terms 'estimate', 'quote' and 'bid' those are 3 distinct concepts to us, even though they might be different words for the same thing to you. When you use distinct words we assume its a distinct concept. Consolidate your vocabulary if necessary.

 

[mkdrep]

Fair enough. In an attempt to “clarify” what I wanted, I obviously confused things.

1. I am a manufacturer’s representative. Our business is to send a price to a distributor for a product of mine that will be used on a new building being built, for example a University. My goal is to track each quote to each distributor. I was able to track quotes easily when we only quoted on projects that are being built. I related everything to each individual project number. We now quote distributors on items that do not have an individual project number.
2. I want to be able to view each quote I have given a distributor whether the quote is related to a project number of not.
Hopefully, this clears things up a little.

Thank you for your reply.

 

[plog]

I believe the short answer to your initial question is that every quote should be part of a project. If that involves making "fake" projects to attribute them to, that should be fine.

If you'd like help with what your structure should be like we can tackle that too. Based on your last post I hear you have these objects:

Distributors
Products
Projects
Quotes

Hopefully you have a table for each of those. Now you have to build a relationship structure that accurately represents what you want to do. Here's some questions to help us get there:

1. Can a project have more than one Distributor?

2. Can a project have more than one quote?

3. Can a quote have more than one product?

 

[mkdrep]

I think I will just go with creating fakes numbers as you suggested.

Thank you very much for your help