I have a database with a quote number, Vendor 1, Vendor 2, Vendor 3.
The query ask me a quote number (Like "*" & [Quote Number] & "*")
I type in the quote number and get my results.
However, if Quote number 200 only uses Vendor 1 and Vendor 2 fields (Vendor
3 is blank because only 2 vendors where contacted) the query comes back blank.
As soon as I fill in Vendor 3 with something it shows it.
90% of quotes have 3-4 vendors. Is there a way to tell it to show me results
regardless of blank entries?

Thank you!

Jerid

Your data structure, with "repeating fields" for Vendors, is perfectly
suitable ... if you were using a spreadsheet! Since Access is a relational
database, setting up your table this way makes for much more work for both
you and Access.

Take a look at "normalization" and "relational database design". Jeff
Conrad's site:
http://home.bendbroadband.com/conrad...resources.html
has some great resources.

You may ask "what does this have to do with my situation?". Here's a
question/answer...

If you had to add a 5th Vendor, with your design, you'd have to modify the
table structure, modify any queries using the vendor-concept, modify any
forms, reports, macros, and code. That is a LOT of maintenance.

With a well-normalized design, you would use the fact that the relationship
is one-to-many and have a table that records quote number and VendorID. If
a quote had one vendor, you'd have one record. If a quote had 7 vendors,
the table would have 7 records.

So, back to your question about "blank" vendors ... you don't have any! The
new table design only has as many rows as you DO have vendors.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Jerid B" <(E-Mail Removed)> wrote in message
news:2AF3ADD4-91D7-4124-B22C-(E-Mail Removed)...
>I have a database with a quote number, Vendor 1, Vendor 2, Vendor 3.
> The query ask me a quote number (Like "*" & [Quote Number] & "*")
> I type in the quote number and get my results.
> However, if Quote number 200 only uses Vendor 1 and Vendor 2 fields
> (Vendor
> 3 is blank because only 2 vendors where contacted) the query comes back
> blank.
> As soon as I fill in Vendor 3 with something it shows it.
> 90% of quotes have 3-4 vendors. Is there a way to tell it to show me
> results
> regardless of blank entries?
>
> Thank you!

On Tue, 5 Sep 2006 08:23:02 -0700, Jerid B
<(E-Mail Removed)> wrote:

>I have a database with a quote number, Vendor 1, Vendor 2, Vendor 3.

Then you have a misdesiged table.

If you have a Many to Many relationship between quotes and vendors,
you should have THREE tables: Quotes; Vendors; and VendorQuotes, with
one record for each vendor - quote combination.

>The query ask me a quote number (Like "*" & [Quote Number] & "*")
>I type in the quote number and get my results.
>However, if Quote number 200 only uses Vendor 1 and Vendor 2 fields (Vendor
>3 is blank because only 2 vendors where contacted) the query comes back blank.
>As soon as I fill in Vendor 3 with something it shows it.
>90% of quotes have 3-4 vendors. Is there a way to tell it to show me results
>regardless of blank entries?

You might post the SQL view of your query - there's nothing explicit
in what you have described that would cause this; putting criteria on
the Vendor fields might do so.

But you'll have MUCH better luck if you properly normalize your
tables, as above! Someday you'll have a fifth vendor, or one vendor
more than the number of vendor fields - then you'll be in trouble!

John W. Vinson[MVP]

Someone told me that once before and have not had a chance to change that yet.

However similiar project.
Employee Name followed by New Hire, Change, Seperation
Only one of 3 can be checked. When I run a query I do not want the other 2
to show up. If it's empty I don't want access to show me the reults.

So it's kind of an opposite problem here.

"Jeff Boyce" wrote:

> Jerid
>
> Your data structure, with "repeating fields" for Vendors, is perfectly
> suitable ... if you were using a spreadsheet! Since Access is a relational
> database, setting up your table this way makes for much more work for both
> you and Access.
>
> Take a look at "normalization" and "relational database design". Jeff
> Conrad's site:
> http://home.bendbroadband.com/conrad...resources.html
> has some great resources.
>
> You may ask "what does this have to do with my situation?". Here's a
> question/answer...
>
> If you had to add a 5th Vendor, with your design, you'd have to modify the
> table structure, modify any queries using the vendor-concept, modify any
> forms, reports, macros, and code. That is a LOT of maintenance.
>
> With a well-normalized design, you would use the fact that the relationship
> is one-to-many and have a table that records quote number and VendorID. If
> a quote had one vendor, you'd have one record. If a quote had 7 vendors,
> the table would have 7 records.
>
> So, back to your question about "blank" vendors ... you don't have any! The
> new table design only has as many rows as you DO have vendors.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
>
> "Jerid B" <(E-Mail Removed)> wrote in message
> news:2AF3ADD4-91D7-4124-B22C-(E-Mail Removed)...
> >I have a database with a quote number, Vendor 1, Vendor 2, Vendor 3.
> > The query ask me a quote number (Like "*" & [Quote Number] & "*")
> > I type in the quote number and get my results.
> > However, if Quote number 200 only uses Vendor 1 and Vendor 2 fields
> > (Vendor
> > 3 is blank because only 2 vendors where contacted) the query comes back
> > blank.
> > As soon as I fill in Vendor 3 with something it shows it.
> > 90% of quotes have 3-4 vendors. Is there a way to tell it to show me
> > results
> > regardless of blank entries?
> >
> > Thank you!
>
>
>

Jerid

From where I sit, it is exactly the same problem.

Until you normalize your data structure, you might as well be using Excel
instead of Access.

You won't be able to (easily) use Access' features and functions if you
insist on using Excel's data structures.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jerid B" <(E-Mail Removed)> wrote in message
news:79B5F612-A0F4-4BDC-B016-(E-Mail Removed)...
> Someone told me that once before and have not had a chance to change that
> yet.
>
> However similiar project.
> Employee Name followed by New Hire, Change, Seperation
> Only one of 3 can be checked. When I run a query I do not want the other 2
> to show up. If it's empty I don't want access to show me the reults.
>
> So it's kind of an opposite problem here.
>
> "Jeff Boyce" wrote:
>
>> Jerid
>>
>> Your data structure, with "repeating fields" for Vendors, is perfectly
>> suitable ... if you were using a spreadsheet! Since Access is a
>> relational
>> database, setting up your table this way makes for much more work for
>> both
>> you and Access.
>>
>> Take a look at "normalization" and "relational database design". Jeff
>> Conrad's site:
>>
>> http://home.bendbroadband.com/conrad...resources.html
>> has some great resources.
>>
>> You may ask "what does this have to do with my situation?". Here's a
>> question/answer...
>>
>> If you had to add a 5th Vendor, with your design, you'd have to modify
>> the
>> table structure, modify any queries using the vendor-concept, modify any
>> forms, reports, macros, and code. That is a LOT of maintenance.
>>
>> With a well-normalized design, you would use the fact that the
>> relationship
>> is one-to-many and have a table that records quote number and VendorID.
>> If
>> a quote had one vendor, you'd have one record. If a quote had 7 vendors,
>> the table would have 7 records.
>>
>> So, back to your question about "blank" vendors ... you don't have any!
>> The
>> new table design only has as many rows as you DO have vendors.
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>>
>> "Jerid B" <(E-Mail Removed)> wrote in message
>> news:2AF3ADD4-91D7-4124-B22C-(E-Mail Removed)...
>> >I have a database with a quote number, Vendor 1, Vendor 2, Vendor 3.
>> > The query ask me a quote number (Like "*" & [Quote Number] & "*")
>> > I type in the quote number and get my results.
>> > However, if Quote number 200 only uses Vendor 1 and Vendor 2 fields
>> > (Vendor
>> > 3 is blank because only 2 vendors where contacted) the query comes back
>> > blank.
>> > As soon as I fill in Vendor 3 with something it shows it.
>> > 90% of quotes have 3-4 vendors. Is there a way to tell it to show me
>> > results
>> > regardless of blank entries?
>> >
>> > Thank you!
>>
>>
>>

Jerid B wrote:
> Someone told me that once before and have not had a chance to change that yet.
>
> However similiar project.
> Employee Name followed by New Hire, Change, Seperation
> Only one of 3 can be checked. When I run a query I do not want the other 2
> to show up. If it's empty I don't want access to show me the reults.
>
> So it's kind of an opposite problem here.

Yes, in this case each employee has exactly one status. Presumably each
status can pertain to many different employees.

So the 1-many relationship is reversed compared to your other example.
The overarching concept is the same.

Consider the following (bad Bad BAD table design!):
Statuses
========
StatusDesc
Employee1
Employee2
Employee3
Employee4
....

You would never consider that kind of structure, right? How many
employee columns should you create in the table? Imagine the nightmare
of changing an employee's status. You would have to find them in one
row, find an empty column in another row, ...

I second Mr. Boyce's recommendation of studying normalization. It will
change your thinking of database design in a very good way.

--
Smartin

When you say "normalize" from what I read and previous posts I guess I am
having a hard time understanding.
Maybe I don't have enough tables... seperate the data more in more tables.
I'll do more reading and dig deeper into this... thank you for your replies.

"Jeff Boyce" wrote:

> Jerid
>
> From where I sit, it is exactly the same problem.
>
> Until you normalize your data structure, you might as well be using Excel
> instead of Access.
>
> You won't be able to (easily) use Access' features and functions if you
> insist on using Excel's data structures.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Jerid B" <(E-Mail Removed)> wrote in message
> news:79B5F612-A0F4-4BDC-B016-(E-Mail Removed)...
> > Someone told me that once before and have not had a chance to change that
> > yet.
> >
> > However similiar project.
> > Employee Name followed by New Hire, Change, Seperation
> > Only one of 3 can be checked. When I run a query I do not want the other 2
> > to show up. If it's empty I don't want access to show me the reults.
> >
> > So it's kind of an opposite problem here.
> >
> > "Jeff Boyce" wrote:
> >
> >> Jerid
> >>
> >> Your data structure, with "repeating fields" for Vendors, is perfectly
> >> suitable ... if you were using a spreadsheet! Since Access is a
> >> relational
> >> database, setting up your table this way makes for much more work for
> >> both
> >> you and Access.
> >>
> >> Take a look at "normalization" and "relational database design". Jeff
> >> Conrad's site:
> >>
> >> http://home.bendbroadband.com/conrad...resources.html
> >> has some great resources.
> >>
> >> You may ask "what does this have to do with my situation?". Here's a
> >> question/answer...
> >>
> >> If you had to add a 5th Vendor, with your design, you'd have to modify
> >> the
> >> table structure, modify any queries using the vendor-concept, modify any
> >> forms, reports, macros, and code. That is a LOT of maintenance.
> >>
> >> With a well-normalized design, you would use the fact that the
> >> relationship
> >> is one-to-many and have a table that records quote number and VendorID.
> >> If
> >> a quote had one vendor, you'd have one record. If a quote had 7 vendors,
> >> the table would have 7 records.
> >>
> >> So, back to your question about "blank" vendors ... you don't have any!
> >> The
> >> new table design only has as many rows as you DO have vendors.
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >>
> >> "Jerid B" <(E-Mail Removed)> wrote in message
> >> news:2AF3ADD4-91D7-4124-B22C-(E-Mail Removed)...
> >> >I have a database with a quote number, Vendor 1, Vendor 2, Vendor 3.
> >> > The query ask me a quote number (Like "*" & [Quote Number] & "*")
> >> > I type in the quote number and get my results.
> >> > However, if Quote number 200 only uses Vendor 1 and Vendor 2 fields
> >> > (Vendor
> >> > 3 is blank because only 2 vendors where contacted) the query comes back
> >> > blank.
> >> > As soon as I fill in Vendor 3 with something it shows it.
> >> > 90% of quotes have 3-4 vendors. Is there a way to tell it to show me
> >> > results
> >> > regardless of blank entries?
> >> >
> >> > Thank you!
> >>
> >>
> >>
>
>
>

Jerid

"more tables" is still not quite grasping it. I urge you to follow the link
I provided in my first response. If you want/need to use Access, learning
about normalization will greatly help you get your data structure set up
(the critical first step, before any forms, reports, queries, etc.).

I'll try to paraphrase what you can learn from digging into the resources
....

If you were setting up a "student enrollment" database, in which you need to
track information about students, information about classes, and information
about which students enroll in which classes, you would need (at a bare
minimum) three tables:

tblStudent (with data ONLY about persons who happen to be students)

tblClass (with data ONLY about each specific class)

trelEnrollment (with data ONLY about a student and a class - one record
per enrollment)

I hope that sheds a bit more light on the process...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jerid B" <(E-Mail Removed)> wrote in message
news:B5899D50-BE7D-436A-A2A2-(E-Mail Removed)...
> When you say "normalize" from what I read and previous posts I guess I am
> having a hard time understanding.
> Maybe I don't have enough tables... seperate the data more in more tables.
> I'll do more reading and dig deeper into this... thank you for your
> replies.
>
> "Jeff Boyce" wrote:
>
>> Jerid
>>
>> From where I sit, it is exactly the same problem.
>>
>> Until you normalize your data structure, you might as well be using Excel
>> instead of Access.
>>
>> You won't be able to (easily) use Access' features and functions if you
>> insist on using Excel's data structures.
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Jerid B" <(E-Mail Removed)> wrote in message
>> news:79B5F612-A0F4-4BDC-B016-(E-Mail Removed)...
>> > Someone told me that once before and have not had a chance to change
>> > that
>> > yet.
>> >
>> > However similiar project.
>> > Employee Name followed by New Hire, Change, Seperation
>> > Only one of 3 can be checked. When I run a query I do not want the
>> > other 2
>> > to show up. If it's empty I don't want access to show me the reults.
>> >
>> > So it's kind of an opposite problem here.
>> >
>> > "Jeff Boyce" wrote:
>> >
>> >> Jerid
>> >>
>> >> Your data structure, with "repeating fields" for Vendors, is perfectly
>> >> suitable ... if you were using a spreadsheet! Since Access is a
>> >> relational
>> >> database, setting up your table this way makes for much more work for
>> >> both
>> >> you and Access.
>> >>
>> >> Take a look at "normalization" and "relational database design". Jeff
>> >> Conrad's site:
>> >>
>> >> http://home.bendbroadband.com/conrad...resources.html
>> >> has some great resources.
>> >>
>> >> You may ask "what does this have to do with my situation?". Here's a
>> >> question/answer...
>> >>
>> >> If you had to add a 5th Vendor, with your design, you'd have to modify
>> >> the
>> >> table structure, modify any queries using the vendor-concept, modify
>> >> any
>> >> forms, reports, macros, and code. That is a LOT of maintenance.
>> >>
>> >> With a well-normalized design, you would use the fact that the
>> >> relationship
>> >> is one-to-many and have a table that records quote number and
>> >> VendorID.
>> >> If
>> >> a quote had one vendor, you'd have one record. If a quote had 7
>> >> vendors,
>> >> the table would have 7 records.
>> >>
>> >> So, back to your question about "blank" vendors ... you don't have
>> >> any!
>> >> The
>> >> new table design only has as many rows as you DO have vendors.
>> >>
>> >> Regards
>> >>
>> >> Jeff Boyce
>> >> Microsoft Office/Access MVP
>> >>
>> >>
>> >> "Jerid B" <(E-Mail Removed)> wrote in message
>> >> news:2AF3ADD4-91D7-4124-B22C-(E-Mail Removed)...
>> >> >I have a database with a quote number, Vendor 1, Vendor 2, Vendor 3.
>> >> > The query ask me a quote number (Like "*" & [Quote Number] & "*")
>> >> > I type in the quote number and get my results.
>> >> > However, if Quote number 200 only uses Vendor 1 and Vendor 2 fields
>> >> > (Vendor
>> >> > 3 is blank because only 2 vendors where contacted) the query comes
>> >> > back
>> >> > blank.
>> >> > As soon as I fill in Vendor 3 with something it shows it.
>> >> > 90% of quotes have 3-4 vendors. Is there a way to tell it to show me
>> >> > results
>> >> > regardless of blank entries?
>> >> >
>> >> > Thank you!
>> >>
>> >>
>> >>
>>
>>
>>

I just meant that I think I am putting too much into one table.
I am using the link you sent me and looking at some demo databases to get a
better idea... I am sure I'll be back to ask more questions.

Thanks for yourr time

"Jeff Boyce" wrote:

> Jerid
>
> "more tables" is still not quite grasping it. I urge you to follow the link
> I provided in my first response. If you want/need to use Access, learning
> about normalization will greatly help you get your data structure set up
> (the critical first step, before any forms, reports, queries, etc.).
>
> I'll try to paraphrase what you can learn from digging into the resources
> ....
>
> If you were setting up a "student enrollment" database, in which you need to
> track information about students, information about classes, and information
> about which students enroll in which classes, you would need (at a bare
> minimum) three tables:
>
> tblStudent (with data ONLY about persons who happen to be students)
>
> tblClass (with data ONLY about each specific class)
>
> trelEnrollment (with data ONLY about a student and a class - one record
> per enrollment)
>
> I hope that sheds a bit more light on the process...
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Jerid B" <(E-Mail Removed)> wrote in message
> news:B5899D50-BE7D-436A-A2A2-(E-Mail Removed)...
> > When you say "normalize" from what I read and previous posts I guess I am
> > having a hard time understanding.
> > Maybe I don't have enough tables... seperate the data more in more tables.
> > I'll do more reading and dig deeper into this... thank you for your
> > replies.
> >
> > "Jeff Boyce" wrote:
> >
> >> Jerid
> >>
> >> From where I sit, it is exactly the same problem.
> >>
> >> Until you normalize your data structure, you might as well be using Excel
> >> instead of Access.
> >>
> >> You won't be able to (easily) use Access' features and functions if you
> >> insist on using Excel's data structures.
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >> "Jerid B" <(E-Mail Removed)> wrote in message
> >> news:79B5F612-A0F4-4BDC-B016-(E-Mail Removed)...
> >> > Someone told me that once before and have not had a chance to change
> >> > that
> >> > yet.
> >> >
> >> > However similiar project.
> >> > Employee Name followed by New Hire, Change, Seperation
> >> > Only one of 3 can be checked. When I run a query I do not want the
> >> > other 2
> >> > to show up. If it's empty I don't want access to show me the reults.
> >> >
> >> > So it's kind of an opposite problem here.
> >> >
> >> > "Jeff Boyce" wrote:
> >> >
> >> >> Jerid
> >> >>
> >> >> Your data structure, with "repeating fields" for Vendors, is perfectly
> >> >> suitable ... if you were using a spreadsheet! Since Access is a
> >> >> relational
> >> >> database, setting up your table this way makes for much more work for
> >> >> both
> >> >> you and Access.
> >> >>
> >> >> Take a look at "normalization" and "relational database design". Jeff
> >> >> Conrad's site:
> >> >>
> >> >> http://home.bendbroadband.com/conrad...resources.html
> >> >> has some great resources.
> >> >>
> >> >> You may ask "what does this have to do with my situation?". Here's a
> >> >> question/answer...
> >> >>
> >> >> If you had to add a 5th Vendor, with your design, you'd have to modify
> >> >> the
> >> >> table structure, modify any queries using the vendor-concept, modify
> >> >> any
> >> >> forms, reports, macros, and code. That is a LOT of maintenance.
> >> >>
> >> >> With a well-normalized design, you would use the fact that the
> >> >> relationship
> >> >> is one-to-many and have a table that records quote number and
> >> >> VendorID.
> >> >> If
> >> >> a quote had one vendor, you'd have one record. If a quote had 7
> >> >> vendors,
> >> >> the table would have 7 records.
> >> >>
> >> >> So, back to your question about "blank" vendors ... you don't have
> >> >> any!
> >> >> The
> >> >> new table design only has as many rows as you DO have vendors.
> >> >>
> >> >> Regards
> >> >>
> >> >> Jeff Boyce
> >> >> Microsoft Office/Access MVP
> >> >>
> >> >>
> >> >> "Jerid B" <(E-Mail Removed)> wrote in message
> >> >> news:2AF3ADD4-91D7-4124-B22C-(E-Mail Removed)...
> >> >> >I have a database with a quote number, Vendor 1, Vendor 2, Vendor 3.
> >> >> > The query ask me a quote number (Like "*" & [Quote Number] & "*")
> >> >> > I type in the quote number and get my results.
> >> >> > However, if Quote number 200 only uses Vendor 1 and Vendor 2 fields
> >> >> > (Vendor
> >> >> > 3 is blank because only 2 vendors where contacted) the query comes
> >> >> > back
> >> >> > blank.
> >> >> > As soon as I fill in Vendor 3 with something it shows it.
> >> >> > 90% of quotes have 3-4 vendors. Is there a way to tell it to show me
> >> >> > results
> >> >> > regardless of blank entries?
> >> >> >
> >> >> > Thank you!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>