Compiling Info from 2 tables with no Relationship

[dlene]

I have an Access database pulling info from a canned database via ODBC in which there is a Tasks table and an Event table (among others). The Tasks table contains things to do for any given day and are date dependent; the Events table contains appointments and the like which are both date and time dependent. I need to combine this information into one report by date so that all appointments and tasks for each day are displayed together on that day.

 

[dhookom]

Sounds like you might be able to use a union query. However you haven't provided any information regarding your two tables and their structure and what "information" you want to "display together".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dlene]

I have several tables including a Matter table, Contact table, Tasks table and Event table.

Both the Event and Tasks tables contain the date, description, location, staff responsible along with the matter and matter id the task or event is related to. The Event table has a time field; the Task table does not. Currently I have a query that pulls the date, time, description and related matter from the Events table and another query that pulls the date, description and related matter from the Tasks table.

The Matter table contains information regarding a given case such as case name, plaintiff, defendant, etc. and each record is unique by its Matter ID

I used the Matter ID to join the Matter table to the Event table and the Matter table to the Tasks table.

Currently I run my Event query and it shows me all of the hearings, appointments, etc. for April 1st. I run my Tasks query and it shows me all of the tasks for April 1st such as 'file petition', 'answer discovery', 'prepare for trial', etc. I want to create one query that displays everything I have to do on April 1st whether it's a Task or an Event.

 

[lespaul]

What is the SQL for both of your existing queries?


Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[willir]

Great question Leslie.

Big Brother is watching.

 

[dlene]

SELECT CVDate([date]-36161) AS Dates, [times] AS [Begin Time], EVENT.STAFF, EVENT.[DESC$], EVENT.MEMO, [time]/8640000 AS Times, MATTER.MAT_REF, EVENT.STATUS, EVENT.CCODE, EVENT.ASTAFF, EVENT.MAT_ID, EVENT.SYSID, EVENT.DATE
FROM MATTER INNER JOIN EVENT ON MATTER.SYSID = EVENT.MAT_ID
WHERE (((EVENT.DATE)=74604) AND ((EVENT.GRPID) Not Like "E*"))
ORDER BY CVDate([date]-36161);

 

[dlene]

Tasks SQL - the previous post was the Event SQL


SELECT CVDate([date]-36161) AS Dates, TODO.STAFF, TODO.[DESC$], TODO.MEMO, TODO.MAT_REF, TODO.STATUS, TODO.CCODE, TODO.ASTAFF, TODO.MAT_ID, TODO.SYSID, TODO.DATE
FROM MATTER INNER JOIN TODO ON MATTER.SYSID = TODO.MAT_ID
WHERE (((TODO.DATE)=74604) AND ((TODO.GRPID) Not Like "E*"))
ORDER BY CVDate([date]-36161);

 

[willir]

I have to get onto something else, but here is the design for those who want to play...

Table: Event
EVENT.MAT_ID
EVENT.STAFF,
EVENT.[DESC$],
EVENT.MEMO,
EVENT.STATUS,
EVENT.CCODE,
EVENT.ASTAFF,
EVENT.MAT_ID,
EVENT.SYSID,
EVENT.DATE
EVENT.GRPID

Table: Matter
MATTER.SYSID
MATTER.MAT_REF,


Table: ToDo
TODO.MAT_ID
TODO.DATE
TODO.GRPID
TODO.STAFF,
TODO.[DESC$],
TODO.MEMO,
TODO.MAT_REF,
TODO.STATUS,
TODO.CCODE,
TODO.ASTAFF,
TODO.MAT_ID,
TODO.SYSID,
TODO.DATE

Relations:
MATTER.SYSID = EVENT.MAT_ID
MATTER.SYSID = TODO.MAT_ID

I suspect SYSID is the primary key for Matter
...and
Matter : Event is
one-to-many
One Matter can have many Events

Matter : ToDo
one-to-many
One Matter can have many ToDo items

What is ambigous is...
CVDate([date]-36161) AS Dates,
[times] AS [Begin Time],
[time]/8640000 AS Times,

Also, note the use of reserved names -- Date and Time -- used for naming of fields.

I suspect dlene wants to
List Matter and all respective Events and ToDo items by date.

 

[dlene]

The CVDate and Time is used to convert the Clarion dates to Gregorian dates.

 

[willir]

I understand this dlene
...but
ORDER BY CVDate([date]-36161);

[date] here will refer to a table (either ToDo or Matter, I suspect ToDo) but the table is not specified.

I will work on your query later today.

Richard

 

[dlene]

[date] does refer to the date field in the table which is contained in both the ToDo and the Event.

I appreciate your help and expertise. It's been quite some time since I've had to use this part of my brain; so it's a little rusty. I had forgotten how much I love this stuff.

Donna