Ordering results with two date fields

[bennybee]

I have two seperate queries base don tables which give the following results.:

Meeting_Query (based on meeting table) -
Meeting_Date Location Type
11/11/03 Darwin Monthly Meeting
17/12/03 Alice Monthly Meeting

and

Hearing_Query (Based on Hearing table) -
Hearing_Date Location Type
11/11/04 Darwin Hearing
16/03/04 Darwin Hearing

Hearings are based on the agenda items of a particular meeting.
so a meeting has an agenda which may go to a hearing

now i want to be able to have another query (so that i can make a report based on it), which will display the information in the following way:
Date Location Type
11/11/03 Darwin Monthly Meeting
17/12/03 Darwin Monthly Meeting
16/03/04 Darwin Hearing
11/11/04 Darwin Hearing

i want the query to sort the data by the date fields. so that they are in chronological order. so that the report generated can be circulated to commission members.

is there a way that i can do this - so that each record is displayed and they are in date order.

when i have tried this, it sometimes associates the meeting with the hearing (as they are linked through the meeting_agenda. that is a meeting has agenda items, and a hearing is then based on an agenda item), but i want each entry to be seperate, so that this report can be formed.

is this possible?

 

[Jon K]

Create a UNION query to combine the two queries:-

SELECT Meeting_Date as [Date], Location, Type
FROM Meeting_Query
UNION
SELECT Hearing_Date, Location, Type
FROM Hearing_Query

Then build a report based on the UNION query, sort the report by the Date field.

 

[bennybee]

Yeah that works well.
but what if i want to add another field to be displayed in the query, that only has a value for hearings.

so i have Date, Type Location for Meeting and
Date, Type, Location, Nature for Hearing.

can you do some sort of union query - or join query so that you can display this?? so that you can have more data from one query displayed than the other??

 

[Jon K]

Yes, you can.

SELECT Meeting_Date as [Date], Location, Type, Null as Nature
FROM Meeting_Query
UNION
SELECT Hearing_Date, Location, Type, Nature
FROM Hearing_Query;

 

[bennybee]

Im still getting an error

when i run the queries seperately, the right information is displayed.

however when i run the queries in a UNION query, pop up boxes appear for those fields in the Hearing Query that have lookup values associated with them..

ie... in the Hearing Table, there are look up values for Premises and Inspector and also Nature. they find these values from drop down boxes in other tables
and when i run the union query, a pop up box appears asking for values for these fields. but the fields where i actuall enter data into them (i dont look up), are fine.

is there a way to get around this? so that i dont have to enter in the data in the pop up boxes. cause the premises name will change from record to record.

 

[bennybee]

infact a pop up box does not appear for all fields which have look up values... only some.

what would be the property of the values that are requesting pop up boxes that are different from the ones that arent requesting pop up boxes??

 

[bennybee]

the fields that are coming up requesting info through a pop up box, are not a direct lookup value from the joined table.

e.g.
the query is on Hearing_Agenda.
the hearing agenda has a meeting agenda, and the meeting agenda has a premises name associated with it.

e.g. Hearing ----- Meeting ----- Premises

im trying to get the premises name to appear in the union query... which has one query based on the hearing information, but i dont know how to make this happen.

 

[Jon K]

I tried looking up Nature from a table "tblNature" in the attached database.

It seemed the lookup property was lost in the union query "QueryUnion".

However, in a new query, I was able to link the union query with "tblNature" to get back the nature descriptions, using a Left Join. No pop-up box appeared. Hope you can do the same.


The database was saved from Access 2000.

 

[bennybee]

when i run the db that you attached, and i run "new query" i get the following
"type mismatch in expression". and it wont display anything.

so i dont see what you see...:'(

is there another way of doing it? or maybe refining this method?

 

[EMP]

I have run the database in both Access 2000 and Access 97.

It runs fine in Access 2000.

If you run it in Access 97, Access mistakenly turns the Nature field in the union query from number to text. You can open the tblNature table in table design and change the NatureID field from number to text so as to match with the union query conversion. The new query will then run fine. This is required only in Access 97.

 

[Pat Hartman]

You can rearrange the Union query to solve the problem. I'm assuming that it is something like what Jon posted:
SELECT Meeting_Date as [Date], Location, Type, Null as Nature
FROM Meeting_Query
UNION
SELECT Hearing_Date, Location, Type, Nature
FROM Hearing_Query;
Change it to:

SELECT Hearing_Date As EventDate, Location, Type, Nature
FROM Hearing_
UNION
QuerySELECT Meeting_Date as EventDate, Location, Type, Null as Nature
FROM Meeting_Query;

Jet is using the first few rows of the query to determine what datatype to use for the Nature field. Apparently, different versions of Access make different decisions. You can control the outcome by swapping the order of the queries.

 

[bennybee]

That still doesnt work.

Can i email someone my database (its about 500kb) and you can work directly on it.

i think that its the fact that im trying to get info from a table joined to a table which is joined to another table which the initial hearing_query is based on... and its all getting a little confusing for me...

so can i please email my database as it is to someone for them to look over please??