Narrowing Down Information

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

I have lots of different databases that my department has
been working on that I was told we want to be able to
cross reference all that information against our clients.
I made a new database where I have linked in the necessary
information tables from all the other database and created
a master table of all the clients.
My problem is that we have lots of clients and have
information on about 8-10% of the clients in the database
we are pulling information from(and none have data in all
five database).
My problem is that I have not been able to narrow down my
query to show just those clients who have information in
at least one of those exiting database. I would appreciate
any suggestions on how to make a query like this work.

Thanks
 
Queries are extremely dependent upon the data structures of the Tables that
are used. So, to be able to tell you exactly how you need to make it is not
going to be possible.

When you used the word 'Database', are you referring to an .mdb file, or
just one table within the .mdb file?

If it's just the tables, can you list the structures of them?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
I hope this might make it easier to understand what I need
help with. Sometimes it is hard to explain when you work
on something for a while.
Right now I have four table (containing survey and
evaluations) containing scores (number:bytes), notes
(memos)based around a clients specific id number
(number:long interger). The master table of clients has
the client id as the primary key and goes on to list name,
address, contact info...
The query I have been trying to make would focus around
looking at all four of the survey/evaluation tables
referenced against the master client list and only showing
those clients who have a survey/evaulation in at least one
of the survey/evaluation tables.
The query that I tried so far was to pull all the master
client table information into the query and the scores
from each survey/evaluation table. I then made a IIf
statement for each table's score (like "IIf([Table 1 Score]
0,1,0)") and tried to sum the IIf statements, with a
Click to expand...
criteria of the sum of the IIf statements >1. However,
once I tried to limit the criteria to >1 I get a popup
window asking for "Parameter value" of the IIF statements
and there are no entries resulting from the query. ANY
suggestions about what I am doing wrong or other ideas on
how to make this work would be appreciated.

Thanks
 
I can't say I understand the need for four different tables for
survey/evaluation, but will still wager a guess.
Also, I'm going to assume that you don't want to use any VBA code.

Because there are 4 child tables, I am thinking that you will need at least
4 queries to determine if someone has at least one "thing" inside any 1 of
the 4.

Make 4 count queries:
Select ClientID, Count(ThingID) as Thing[1-4]Cnt From Table[1-4] Group By
ClientID

[1-4] being the table name for each respective table.

Create a new query, linking the Clients table to each of the 4 saved queries
above.
(All in the same query now).
The trick being that you make an Outer Join from Client to each of the 4.

Then, create a calculated field that adds all of the Thing[1-4]Cnt's
together.

TotalThingCount : nz(Thing1Cnt,0) + nz(Thing1Cnt,0) + nz(Thing1Cnt,0) +
nz(Thing1Cnt,0)

Add criteria to TotalThingCount that is > 0.

So, something like:

Select ClientID, {formula above} from {Client table outer joined to 4
queries} Where TotalThingCount > 0

I think that you really have a data normalization problem, because you
typically don't have to do this kind of stuff with a properly structured
database.

I hope this might make it easier to understand what I need
help with. Sometimes it is hard to explain when you work
on something for a while.
Right now I have four table (containing survey and
evaluations) containing scores (number:bytes), notes
(memos)based around a clients specific id number
(number:long interger). The master table of clients has
the client id as the primary key and goes on to list name,
address, contact info...
The query I have been trying to make would focus around
looking at all four of the survey/evaluation tables
referenced against the master client list and only showing
those clients who have a survey/evaulation in at least one
of the survey/evaluation tables.
The query that I tried so far was to pull all the master
client table information into the query and the scores
from each survey/evaluation table. I then made a IIf
statement for each table's score (like "IIf([Table 1 Score]
0,1,0)") and tried to sum the IIf statements, with a
Click to expand...
criteria of the sum of the IIf statements >1. However,
once I tried to limit the criteria to >1 I get a popup
window asking for "Parameter value" of the IIF statements
and there are no entries resulting from the query. ANY
suggestions about what I am doing wrong or other ideas on
how to make this work would be appreciated.

Thanks
-----Original Message-----
Queries are extremely dependent upon the data structures of the Tables that
are used. So, to be able to tell you exactly how you need to make it is not
going to be possible.

When you used the word 'Database', are you referring to an .mdb file, or
just one table within the .mdb file?

If it's just the tables, can you list the structures of them?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-




.
Click to expand...
Click to expand...
 
Back
Top