Can't Figure Out How to Work With Blank Values

[SusanC]

I'm trying to find out if like fields in similar tables contain the
same values or if some contain a different value. The fields will
contain 1's and O's Some tables do not contain all fields.

Example:


Region 1 Region 2 Region 3

Item _______Flag___Item______Flag ______Item_______Flag
hammer_____0 _____hammer___ 0 ________hammer____1
screwdriver__1 _________________________screwdriver_1
wrench______0____wrench ____0 _________wrench ____0
file_________1____ file________0


The result I would want would be the records of the items that have
differing values in "flagged."

In this case, it would be

Region 1__________Region 2 _____________Region 3
hammer ____0_____hammer______0 _______hammer____1
file________1 _____file__________0

How do I search the rows for one value, a 0 or a 1??

I created queries that would pull out everything with an O and
everything with a 1. I could create some kind of query to compare the
two, but it doesn't pull in the rows that aren't in every table. I
also thought about getting an average of each field. Then I'd see if
they were whole numbers or fractions. The fractions would contain 0's
and 1's.


I could use append tables to create one table and normalize my data,
but when I do, it doesn't include all the fields, even when I created a
master table with all fields to append to. It has to be easy to pull
data into because we are pulling this data out of another master
database that contains databases for about 30 regions, and it would be
easiest to just import or paste the data from each region into its own
table, since we can't just pull in columns, since every region does not
have every product. The data will have to be imported or pasted in
everytime we run this program.

Thanks for any help. I can do simple stuff, but this is a challenge I'd
really like to figure out. Wouldn't hurt my reputation around here
either, since no one else can figure out how to do itl


So . . . how can I deal with those blanks (values) to get the results
I need?

 

[Guest]

First I need to see what your table structure is like and from the
information you gave I can not figure it out.
List the table name and the name of the fields with the datatype beside the
field name.

 

[SusanC]

I have just created a small test database.

I currently have 4 tables (I can't make an Append or Make Table query
pull in all fields to create one table yet)

I have a Master table with all fields:

bolt
hammer
nail
screw
washer

I have a DatabaseOne table with the following enteries:

bolt 2
hammer 1
nail 2
screw 1
screwdriver 2
washer 2

I have a DatabaseTwo table with the following data:

bolt 2
hammer 2
nail 1
screw 1
washer 1

My DatabaseThree has:

bolt 2
nail 1
screw 1
washer 2

The relationship is that all are joined to the Master so that all items
in master should show.

Right now, I am working with two queries and trying to get them to pull
in all the items with ones in one query and all the items withs twos in
the other query. If I could make them work, I'd probably combine all
the tables with an Append or Make Table query, but none my my queries
pull in the hammer or the screwdriver.

My present queries Work with all four tables. I pull in the Product
from the master table and the value from the three other tables and my
criteria are <>1 Or Is Null in all three of the values on different
lines so they will be "or's." The other query does the same for <>2 Or
Is Null.

If I can just get ANY query to pull in the screwdriver and hammer, I
think I can solve this problem. Thanks for your help!!

 

[Michel Walsh]

Hi,


It seems you need to have a table will all possible tools name. If you don't
have such table, you can use a query like:


SELECT tool FROM masterTable
UNION
SELECT tool FROM databaseOne
UNION
SELECT tool FROM databaseTwo
UNION
SELECT tool FROM databaseThree



Assume that table, or query, name is Tools.


SELECT Tools.Tool, databaseOne.Qty, databaseTwo.Qty, databaseThree.Qty
FROM ((tools LEFT JOIN databaseOne ON tools.tool=databaseOne.Tool)
LEFT JOIN databaseTwo ON tools.tool=databaseTwo.tool)
LEFT JOIN databaseThree ON tools.tool = databaseThree.tool



should then produces something like what (I think that) you want.



Hoping it may help,
Vanderghast, Access MVP

 

[John Vinson]

I have a Master table with all fields:

bolt
hammer
nail
screw
washer

Just to clarify - are these FIELDNAMES (a very bad design) or VALUES
IN A FIELD (a field named Tooltype perhaps) (the better design?)

John W. Vinson[MVP]