Merge Query Result into one (1 Viewer)

[jepoysaipan]

Hi,

Can someone help me how to reconcile the query results into one to create somewhat of a pop-up reminder.

Attached is the DB of what I have so far, I am able to extract the records as well as filtered the data, but I can't figure out on how to fit the results into one column.

What I am projecting is to get the following format (can be another table or a query:

Employee Reminder Date of Completion Status
99 - Jane D. Doe Contract Renewal 01/15/2008 Pending
89 - John W. Doe Entry Permit Expired 12/15/2008 Pending
and so on...

Pls. see attached DB for my sample data.

Hope someone can help me on this one.

Jeff

 

[WayneRyan]

Jeff,

Just add a new column to your query:

NewColumn: [Employee] & " " & [Reminder] & " " & [Date of Completion] & " " & [Status]

Then just add the new column to your report.

hth,
Wayne

 

[jepoysaipan]

Hi Wayne,

Thanks for the time, As you can see on my sample DB, I have multiple column results which actually gets into my neck everyday when I tried to figure out how to collect those info. and place it in a neat recordsource which I can present to the user in a friendly way.

Any other ideas?

Jeff

 

[WayneRyan]

Jeff,

Yes, I have plenty of ideas ... what's your question?

Wayne

 

[jepoysaipan]

Hi Guys,

After a sleepless night, I found an SQL that suits the format what I wanted, attached is what I have so far.

As you run the union query (Query6) it will display 3 columns "Employee", "Reminder" "Date_Renewal", but on the Reminder Column it shows the actual date what I want to put messages such as "Contract Renewal", "Passport Expiration", "Entry Permit Expiration" and "Police Clearance Expiration".

I am not inclined with SQL so I don't have any idea what went wrong.

Appreciate all your help.

 

[jepoysaipan]

SOLVED Merging Multiple Columns in a query!

Below is the SQL statement I used to get the result:

select Employee
, "Renewal" as Reminder
, Renewal as [Date Renewal]
from Query5 order by Employee
union all
select Employee
, "PassportExpiration"
, PassportExpiration
from Query5 order by Employee
union all
select Employee
, "Permit Expiration"
, Permit_Expiration
from Query5 order by Employee
UNION ALL select Employee
, "Police Clearance Expiration"
, Police_Clearance
from Query5
ORDER BY Employee;

Hope someone out there have the same problem as mine.

Thanks a lot for your all help guys!

 

[WayneRyan]

J,

Glad to see that you have it working.

Do you really get the Sort Order that you want, or is the Query feeding a report?

2nd, if you have the , "Renewal", "PassportExpiration" and "Permit Expiration"
in a "child" table, then you don't need the Unions.

I'm still only a lowly A2000 person here and I couldn't open your attachment.

Wayne

 

[jepoysaipan]

Wayne,

Attached is the 2K version, I know the SQL still need a lot of working, I am just resting for a while to sort out the needed lift.

Please let me know what are the changes I need to do, or can you get it done without using SQL?

Thanks.