How on earth..............??

[Mike]

-- Hi and thanks in advance to anybody who can come up with an answer..

Is there any way when running an MSQuery to have the query import BLANK
values. I have six tables, one containing details of patients whilst
the other five contain various info about the patients......all
patients ARE in the 'patient' file but not all patients have data in
some or all of the other files. I need to pull together a spreadsheet
that lists ALL patients with the data relevant to each patient from the
other tables where data exists, where no relevant data exists I require
a blank cell. Seems the best I can get from the query is a spreadsheet
of patients who only have data in ALL the other files, ie not ALL
patients are included.
I guess I could achieve the result with lookup function but there has
to be an easier way...........help appreciated

M

 

[Conrad Carlberg]

Hi Mike,

I'm assuming that you're pointing MS Query at a true database. If that's
true, then I suggest that you not try to get MS Query to manage the records,
but instead use a select query, or an sproc, in the database to establish
outer joins such that all the records in the 'patient' table are returned
regardless of whether they're matched by records in another table. Depending
on which tables you're looking for, you'll probably want to link them using
either the medical record number or the patient visit number.

An inner join returns records only if they're matched in both tables; an
outer join returns all records in a given table and null values for
unmatched records in other tables.

Once you've established this query in the database, point MS Query at it. MS
Query isn't usually powerful enough to do it all by itself.

 

[Guest]

Mike

Have you tried this?:

•Data.Get External Data.New Database Query
•Data Source: Excel Files
•Select the tables to be used

(Note: when you select more than one table, Excel warns that you will have
to create the joins manually. Accept the warning and continue.)

•Create the joins by dragging the PatientID field from the main fact table
to each dimension table
•Double click each join connector and choose to return ALL records from the
main table (creating an OUTER JOIN)
•Arrange Columns to the correct order
•File.Return data to Microsoft Excel
•Select a destination for the returned results

The result should list every patient and whatever applies to each one.

Does that help?

Regards,
Ron

 

[Mike]

Mike

Have you tried this?:

•Data.Get External Data.New Database Query
•Data Source: Excel Files
•Select the tables to be used

(Note: when you select more than one table, Excel warns that you will
have to create the joins manually. Accept the warning and continue.)

•Create the joins by dragging the PatientID field from the main
fact table to each dimension table
•Double click each join connector and choose to return ALL records
from the main table (creating an OUTER JOIN)
•Arrange Columns to the correct order
•File.Return data to Microsoft Excel
•Select a destination for the returned results

The result should list every patient and whatever applies to each one.

Does that help?

Regards,
Ron

Thanks Ron..........that would appear to give me what I want, except
that the option to select the outer join is 'greyed out'.....only the
option to create an inner join is available.....there doesn't seem to
be a way around this.......but thanks anyhow

--

 

[Mike]

Mike

Have you tried this?:

•Data.Get External Data.New Database Query
•Data Source: Excel Files
•Select the tables to be used

(Note: when you select more than one table, Excel warns that you will
have to create the joins manually. Accept the warning and continue.)

•Create the joins by dragging the PatientID field from the main
fact table to each dimension table
•Double click each join connector and choose to return ALL records
from the main table (creating an OUTER JOIN)
•Arrange Columns to the correct order
•File.Return data to Microsoft Excel
•Select a destination for the returned results

The result should list every patient and whatever applies to each one.

Does that help?

Regards,
Ron

Thanks Ron.........seems you cannot have outer joins if there are more
than two tables in the query...............it can be done in Access !!!

--

 

[Guest]

Mike
You're right...I'd not run across the MS Query 2-table maximum for outer
joins. Most of my database work is with more robust tools and languages.
I've been exploring workarounds, but I believe you'd do best with either VBA,
MS Access queries, or stored procedures (depending on your database), as Mr.
Carlberg mentioned.

I wish I could have helped more.

••••••••••
Regards,
Ron

 

[Mike]

Mike
You're right...I'd not run across the MS Query 2-table maximum for
outer joins. Most of my database work is with more robust tools and
languages. I've been exploring workarounds, but I believe you'd do
best with either VBA, MS Access queries, or stored procedures
(depending on your database), as Mr. Carlberg mentioned.

I wish I could have helped more.

••••••••••
Regards,
Ron

Many thanks for your efforts, really appreciated. Interestingly, we all
have something to learn which makes these forums so useful

Mike

--

 

[Guest]

Not that MS Query is the tool of choice for this kind of query, but . . .

I used 1 fact table and 3 dimension tables

Fact Table: rngPatient
Field1: Patient
Data Sample:
Patient
100
110
120

Dim Table 1: rngCategory1
Field1: Patient
Field2: Category
Data Sample:
Patient Category
100 a
110 b
120 c
130 d

Dim Table 2: rngCategory2
Field1: Patient
Field2: Color
Data Sample:
Patient Color
100 Red
110 Orange
120 Yellow
130 Green

Dim Table 3: rngCategory3
Field1: PatientID
Field2: Amount
Data Sample:
Patient Amount
100 1
110 2
120 3
130 4

The MS Query SQL Code (not pretty but functional):
•••••••••••••••
The basic structure is:
Select Qry1.Field1, Qry1.Field2, Qry2.Field2, Qry3.Field2 FROM
(sql code for 1st query) Qry1,
(sql code for 2nd query) Qry2,
(sql code for 3rd query) Qry3
WHERE
Qry1.Field1 = Qry2.Field1
AND Qry1.Field1 = Qry3.Field1

•••••••••••••••••••
SELECT Cat1Query.Patient, Cat1Query.Category, Cat2Query.Color,
Cat3Query.Amount
From
(SELECT rngPatient.Patient, rngCategory1.Category
FROM {oj `C:\PatientTables`.rngPatient rngPatient LEFT OUTER JOIN
`C:\PatientTables`.rngCategory1 rngCategory1 ON rngPatient.Patient =
rngCategory1.Patient}) Cat1Query,
(SELECT rngPatient.Patient, rngCategory2.Color
FROM {oj `C:\PatientTables`.rngPatient rngPatient LEFT OUTER JOIN
`C:\PatientTables`.rngCategory2 rngCategory2 ON rngPatient.Patient =
rngCategory2.Patient}) Cat2Query,
(SELECT rngPatient.Patient, rngCategory3.Amount
FROM {oj `C:\PatientTables`.rngPatient rngPatient LEFT OUTER JOIN
`C:\PatientTables`.rngCategory3 rngCategory3 ON rngPatient.Patient =
rngCategory3.Patient}) Cat3Query
WHERE
Cat1Query.Patient = Cat2Query.Patient
AND Cat1Query.Patient = Cat3Query.Patient

Sample Data from the query:
Patient Category Color Amount
100 a Red 1
110 b Orange 2
120 c Yellow 3
130 d Green 4
140 e Blue 5

Note: Each data source is actually an independent query constructed in SQL.
Rather than coding each query from scratch, I just built 3 separate
outer-join queries in MS Query and saved the SQL in Notepad. Then I just
copy/pasted/edited to construct the query.

Note: I haven't tested it with GROUP BY or HAVING or any number of other
complications.

Hopefully, somebody will find that approach useful.

••••••••••
Regards,
Ron

 

[Conrad Carlberg]

I've been exploring workarounds, but I believe you'd do best with either
VBA,

MS Access queries, or stored procedures

FWIW, a useful rule of thumb is that you'll make more effective use of
resources if you can use SQL. Things come to pass faster. But if you need to
do record-by-record processing, usually on the basis of selection criteria,
then you're often forced to use VBA in conjunction with either ADO or DAO,
so as to establish and modify recordsets. More resource-intensive than SQL
queries or sprocs.