I am getting blank results due to unfilled field

[Guest]

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!

 

[Jeff Boyce]

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/conradsystems/accessjunkie/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

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!

 

[John Vinson]

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]

 

[Guest]

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.

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/conradsystems/accessjunkie/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

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!

 

[Jeff Boyce]

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

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.

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/conradsystems/accessjunkie/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

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!

 

[Smartin]

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.

 

[Guest]

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.

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

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.

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/conradsystems/accessjunkie/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


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!

 

[Jeff Boyce]

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

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.

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

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.

:

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/conradsystems/accessjunkie/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


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!

 

[Guest]

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

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

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.

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

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.

:

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/conradsystems/accessjunkie/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


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!