can't get unmatched query to work.

[themastertaylor]

I've got a system to manage various quotes for building materials for
a number of sites. i want a query to produce a report that shows me
who HASN'T quoted for which sites. basically so i can print it off and
ring round and hurry them up a bit. I have my quote table which has
the supplier ID, Site ID and an ID for each quote.(I enter each quote
and specify which supplier and site it relates to. I've tried an
unmatched query but can't get it to output correctly. i think i need
some sort of expression to basically say "if there is a site ID,
return all results where one or more supplier IDs isn't attributed to
that site ID" any help would be great

[davidmwhite]

On Mar 21, 11:50 am, [e-mail] wrote:

I've got a system to manage various quotes for building materials for
a number of sites. i want a query to produce a report that shows me
who HASN'T quoted for which sites. basically so i can print it off and
ring round and hurry them up a bit. I have my quote table which has
the supplier ID, Site ID and an ID for each quote.(I enter each quote
and specify which supplier and site it relates to. I've tried an
unmatched query but can't get it to output correctly. i think i need
some sort of expression to basically say "if there is a site ID,
return all results where one or more supplier IDs isn't attributed to
that site ID" any help would be great

did you try to use the unmatched query wizard? start with that, and
create any additional joins/filters as needed

[jonceramic]

On Mar 21, 10:50 am, [e-mail] wrote:

I've got a system to manage various quotes for building materials for
a number of sites. i want a query to produce a report that shows me
who HASN'T quoted for which sites. basically so i can print it off and
ring round and hurry them up a bit. I have my quote table which has
the supplier ID, Site ID and an ID for each quote.(I enter each quote
and specify which supplier and site it relates to. I've tried an
unmatched query but can't get it to output correctly. i think i need
some sort of expression to basically say "if there is a site ID,
return all results where one or more supplier IDs isn't attributed to
that site ID" any help would be great

Here's how I do this(substitute supplier=quoter and site=job
below)...

If I understand it right, you have 3 tables...
tblQuoter - The query of the of active quoters for a job.
tblJob - Is a list of open jobs to be quoted.
tblQuote - Is the list of quotes

So, you set up 2 queries. The first, qryQuotersAndJobsCombined gives
you all of the possible combination of open Jobs and active Quoters.
SELECT tblJob.*, tblQuoter.*
FROM tblJob, tblQuoter
WHERE (((tblJob.open)=Yes) AND ((tblQuoter.active)=Yes));
(To set this up, you set both tables in the query, without a
relationship. Drag the "*"'s down so you'll have all the fields from
both tables, and then drag the "open" and "active" fields, but uncheck
the "Show" boxes.")
(Note, if only certain Jobs are available to certain quoters, then
this query will also need to incorporate the related
"tblQuoterCapabilities" also.)

The second, qryNullQuotes gives you all of the combinations which have
no quote.
SELECT tblQuote.*, [qryQuotersAndJobsCombined].*
FROM [qryQuotersAndJobsCombined] LEFT JOIN tblQuote ON
([qryQuotersAndJobsCombined].Quoter = tblQuote.QuoterName) AND
([qryQuotersAndJobsCombined].Job = tblQuote.JobName)
WHERE (((tblQuote.QuoteID) Is Null));
(Put tblQuote and qryQuotersAndJobsCombined as the 2 query tables.
Set up the "LEFT JOIN" by dragging and dropping both related keys on
each other. On each relationship line, right click to edit them show
"all" form the query, and only those fields that match from the
table. Again, drag both "*"'s down so you get all fields. Then, drag
down the QuoteID field, uncheck the "Show" box, and type in "Is Null"
to limit the answers to those jobs that are Null.)

Hope that helps/makes sense.

Jon

[themastertaylor]

legend - thanks a million!

[themastertaylor]

Basically I wanted to say thanks for the help and see if you could
point me in the right direction on my next problem - . Basically its
in the same system, once I applied the query you helped me with I
spotted a flaw in my system. Some suppliers provide both drystone and
asphalt, as such if a supplier had only submitted a drystone quote the
query assumes that they have also submitted an asphalt quote (some
companies run the 2 as separate departments as such one may arrive and
not the other etc)

I've added 2 yes/no fields to the quotes file, to indicate what has
been quoted (dry and asphalt) however when I try to display them in
the query I get a grayed out box rather than a tick or an empty box
despite having filled them in on the quotes file which the query draws
from. Also I'm assuming I can modify the query you gave me to look at
the tick box and the quote number in relation to similar tick boxes in
my supplier table that indicate which stone types they provide? i.e
to modify my query to find sites suppliers haven't quoted for to one
which displays sites that haven't got a dry/asphalt part to the quote
where the company can supply such a material.

The supply will always be one or the other or both - i don't need any
future flexibility in terms of categories. in terms of the suppliers
probably over 50% offer both services and the rest just offer dry
stone. however they may be unable to quote for one or the other
depending how far the site is from their plants. as such just because
a supplier can supply in theory, practice they may not quote. as such
it would be beneficial if i can insert a field somewhere to say that
the suplier can't quote due to location etc so that they are ommited
from the main query and i don't keep pestering them for a quote that
they can't provide.

[jonceramic]

On 27 Mar, 02:25, [e-mail] wrote:

Basically I wanted to say thanks for the help and see if you could
point me in the right direction on my next problem - . Basically its
in the same system, once I applied the query you helped me with I
spotted a flaw in my system. Some suppliers provide both drystone and
asphalt, as such if a supplier had only submitted a drystone quote the
query assumes that they have also submitted an asphalt quote (some
companies run the 2 as separate departments as such one may arrive and
not the other etc)

I've added 2 yes/no fields to the quotes file, to indicate what has
been quoted (dry and asphalt) however when I try to display them in
the query I get a grayed out box rather than a tick or an empty box
despite having filled them in on the quotes file which the query draws
from.

Are your other fields showing up in the query from the same table that
has the yes/no fields?

>Also I'm assuming I can modify the query you gave me to look at
the tick box and the quote number in relation to similar tick boxes in
my supplier table that indicate which stone types they provide? i.e
to modify my query to find sites suppliers haven't quoted for to one
which displays sites that haven't got a dry/asphalt part to the quote
where the company can supply such a material.

There's 2 ways to do this. One would be your approach. Which is okay
if that's the only 2 stone types you'll ever deal with. (If there
would be multiple types, you may have wanted to consider putting them
in a related table.)

The supply will always be one or the other or both - i don't need any
future flexibility in terms of categories. in terms of the suppliers
probably over 50% offer both services and the rest just offer dry
stone.

The yes/no column, vs. the related table for supply types is going to
play into how you want your system to act. If you really want to know
and deal with separate quotes on your "didn't quote" page, then you'll
likely need to do a related table anyway. The only way around it is
if you can tolerate showing companies on the "not quoted yet" list
which have one service quoted, but not the other.

>however they may be unable to quote for one or the other
depending how far the site is from their plants. as such just because
a supplier can supply in theory, practice they may not quote. as such
it would be beneficial if i can insert a field somewhere to say that
the suplier can't quote due to location etc so that they are ommited
from the main query and i don't keep pestering them for a quote that
they can't provide.

Yes, what you really want to do is create the ability to log a "no-
quote" in your table "quote" file. You might also want to put a
reason there. And, if you're doing a "yes/no" method, you'll want to
have 2 no quote fields for each type of rock.

This isn't really "perfect" database theory, but in your case, since
you have a known dataset of rules, you'll be fine.

Try setting up multiple queries for each condition first. At
first ,it will be fine for you to have 2 separate queries - one that
shows dry rock not quoted. And one that shows asphault rock not
quoted. You'll learn based on that what your "or"/"and" selections of
"WHERE" statements can look like to do a combined query.

Jon