How can I sort 4 date fields into 1 column/row (1 Viewer)

[sammyk0072003]

Hello all.
I'd really appreciate any help that you could offer I can't seem to find the answer to my question anywhere. My knowledge of Access is quite limited as I have only been using it for about 3 weeks. Unfortunately I do not work in IT so I also know nothing about VB either. However, I am at least partially computer savvy. So please answer in the simplest terms with specific instructions assuming that I know nothing about Access. I will try to explain my problem as completely as possible.

I created a database with 5 different tables.

There is a 1 general table with demographic info (age, sex, etc) and 4 specific tables linked to the general table in a 1 to many relationship based on the patients name.
Each of the specific tables contains data about a certain type of procedure that the child had on a particular date. Here are my 4 tables and date fields:
[OSAQuestionnaire].[OSA_DateSurveyed]
[SleepStudy].[SleepStudyDate]
[SpeechEval].[SpeechEvalDate]
[Surgical_data].[ProcedureDate]

I want to create a query (and eventually a report) with results for each child that is sorted in order by the date regardless of which table that field came from. I don't want to specify the order of those four tables. Said differently each child has multiple entries in each of the tables (the one-many relationship) and I want to know the order of entries in relation to each other regardless of the table the entry came from. What I don't want is a query with 4 sorted date fields in columns next to each other.

For example:
Child#1 OSAQuestionnaire on 1-1-04; Surgical_data 1-1-03; OSAQuestionnaire 1-1-02 etc.
Child#2 SpeechEval 1-2-03; Surgical_data 1-2-02; SpeechEval 1-2-01

The idea is to see how the various surgeries have affected the results of the other items over time. For child #2 I would like to compare his 2 SpeechEvals knowing (from my query) that the first SpeechEval was before surgery and the second SpeechEval was after surgery.
Thank you in advance for any help that you can lend to me,
Sam

 

[WayneRyan]

Sam,

Without going into too much detail. If you just care about the dates and not
what table they're in this should work:

Make a pass through all of your tables with a UNION query:

MainTableStuff, ChildAStuff UNION
MainTableStuff, ChildBStuff UNION
MainTableStuff, ChildCStuff UNION
MainTableStuff, ChildDStuff

Then your report can just sort by date.

Wayne

 

[sammyk0072003]

Thank you for the help Wayne, I didn't know I needed a union query.

this is what I did first:
Select [Name], [OSA_DateSurveyed]
From [OSAQuestionnaire]

Union Select [Name], [SleepStudyDate]
From [SleepStudy]

Union Select [Name], [SpeechEval_Date]
From [SpeechEval]

UNION Select [Name], [DateofProcedure]
From [Surgical_data]
ORDER BY [Name];

That gave me 2 columns--Name and Date, but I still wanted to know what happened on that date. The only way I could figure out how to have that information was by creating a new field in each of the 4 tables with the value filled in as follows:
fromOSAtable
fromSleepStudytable
fromSpeechEvaltable
fromSurgicalDatatable

My query (plus an alias expression) is then:

Select [Name], [OSA_DateSurveyed] AS [DateofIntervention] , [fromOSAtable] AS [TypeofIntervention]
From [fromOSAQuestionnairetable]
Union Select [Name], [SleepStudyDate], [fromSleepStudytable]
From [SleepStudy]

Union Select [Name], [SpeechEval_Date], [fromSpeechEvaltable]
From [SpeechEval]

UNION Select [Name], [DateofProcedure], [fromSurgicalDatatable]
From [Surgical_data]
ORDER BY [Name];

This way I get three columns: Name, Dateofintervention, TypeofIntervention

Is there a better way to do this?
Also is there a way to include fields from the different tables in this query in a NON-Union manner. So that in addition to knowing the Name, DateofIntervention, and TypeofIntervention, I can know the specific details for that type of intervention. That way if the date corresponds to surgery there will be surgical data listed, but if that date corresponds to a speechstudy there will be speech data displayed.
I hope all of that makes sense and thanks in advance for any help

 

[WayneRyan]

Sam,

One of us has been shooting pool and drinking beer (OK, it's me) ...

Pat has VERY good ideas about table structures and so forth.

YOUR initial approach about adding an "alias" column is very good.

As long as you queries return:

SomeName, SomeDate, SomeService, SomeOtherThing

And you can extract "similar" fields from your tables. What is the problem?
It definitely isn't performance.

Aren't you looking for those same types of things from your tables?

Need more info, or "rack 'em and we'll think it over".

Wayne

 

[sammyk0072003]

I thought I would report back my final solution

I thought I would report back my final solution.
Using the above union query with the alias, joined to this one by both name and the date of the procedure I came up with the following which gives me NAME, DATEofProcedure, TYPEofProcedure, and any other field that I want.

SELECT
AboveUnionQuery.Name,
AboveUnionQuery.DateofIntervention,
AboveUnionQuery.TypeofIntervention,
surgical_data.Surgeon,
surgical_data.Procedure,
SleepStudy.BMI,
SleepStudy.TotalSleepTime,
SleepStudy.SleepEfficiency,
SleepStudy.SleepLatency,
SleepStudy.ArousalIndex,
SleepStudy.[A/HI],
SpeechEval.Nasal,
SpeechEval.Speech_Subjective

FROM
((AboveUnionQuery LEFT JOIN surgical_data ON (AboveUnionQuery.Name=surgical_data.name) AND (AboveUnionQuery.DateofIntervention=surgical_data.DateofProcedure))
LEFT JOIN SpeechEval ON (AboveUnionQuery.Name=SpeechEval.Name) AND (AboveUnionQuery.DateofIntervention=SpeechEval.SpeechEval_Date))
LEFT JOIN SleepStudy ON (AboveUnionQuery.Name=SleepStudy.Name) AND (AboveUnionQuery.DateofIntervention=SleepStudy.SleepStudyDate);

Thanks again for the help Wayne and Pat.