Report on 3 Queries by Date

[naigy via AccessMonster.com]

Hi I have 3 queries which I would like to join into one based on the
servicedate/taskdate so that for any given date I can see the totals of each
type of task we log for work. I would have joined these using a simple query
however my issue is that not everyday I log an event in any one table (Each
query based on only one table).



Query1
ServiceDate
CleanTimeTotal
QATimeTotal
ESTTimeTotal

Query2
ServiceDate
RepairTimeTotal

Query3
TaskDate
TaskTimeTotal

Now I suspect I need to rename TaskDate to ServiceDate in Query3 above which
I am okay to do but have no idea how to set up the union query or infact if
it is the best way to do this. The format of the data I would like to output
is as follows where DateDone is equal to ServiceDate and or TaskDate.

DateDone CleanTimeTotal QATimeTotal ESTTimeTotal RepairTimeTotal
TaskTimeTotal

Any guidence on where I need to start would be appreciated.

 

[Jeff Boyce]

I suspect what you are trying to do could be accomplished if you were able
to start with a list of ALL services dates, and join the three queries you
have to that list, using a "directional" join (LEFT or RIGHT).

If you open the query in design view and join two [ServiceDate] fields
between two tables, you can right-click the join line and change it from
showing where [ServiceDate] matches in both, to showing ALL of one and ANY
matching in the other.

The reason for starting with ALL service dates is because none of your three
existing queries may have all the dates already. You need to start will all
possibilities, then find any matches in the other queries.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[naigy via AccessMonster.com]

Thanks Jeff,

This sounds like a logical step however how can I construct such a list of
service dates.

I suspect what you are trying to do could be accomplished if you were able
to start with a list of ALL services dates, and join the three queries you
have to that list, using a "directional" join (LEFT or RIGHT).

If you open the query in design view and join two [ServiceDate] fields
between two tables, you can right-click the join line and change it from
showing where [ServiceDate] matches in both, to showing ALL of one and ANY
matching in the other.

The reason for starting with ALL service dates is because none of your three
existing queries may have all the dates already. You need to start will all
possibilities, then find any matches in the other queries.

Hi I have 3 queries which I would like to join into one based on the
servicedate/taskdate so that for any given date I can see the totals of each

[quoted text clipped - 25 lines]

Any guidence on where I need to start would be appreciated.

 

[Jeff Boyce]

If this were mine, and if there was NOT an already existing list of service
dates, I'd probably use a union query to derive all possible dates from the
three queries! You could use something like (untested psuedo-SQL follows):

SELECT [ServiceDate] FROM Table1
UNION
SELECT [ServiceDate] FROM Table2
UNION
SELECT [ServiceDate] FROM Table3;

This would give you all/any service dates in the underlying data. (Do you
actually have three separate tables you are storing service dates in? If
so, why?)

Then use the "directional" joins from the results of this union query to
each of your existing queries.

This is not "elegant", but I suspect it will get the job done...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks Jeff,

This sounds like a logical step however how can I construct such a list of
service dates.

I suspect what you are trying to do could be accomplished if you were able
to start with a list of ALL services dates, and join the three queries you
have to that list, using a "directional" join (LEFT or RIGHT).

If you open the query in design view and join two [ServiceDate] fields
between two tables, you can right-click the join line and change it from
showing where [ServiceDate] matches in both, to showing ALL of one and ANY
matching in the other.

The reason for starting with ALL service dates is because none of your three
existing queries may have all the dates already. You need to start will all
possibilities, then find any matches in the other queries.

Hi I have 3 queries which I would like to join into one based on the
servicedate/taskdate so that for any given date I can see the totals of

each
[quoted text clipped - 25 lines]

Any guidence on where I need to start would be appreciated.

 

[naigy via AccessMonster.com]

Thanks for your reply Jeff. I am using 3 tables with the following data and
my understanding was it is best to split data across several tables
particularly when some data is irrelevant to the other information.

Ie. Table 1.
This Table is used to log repair information and logs information such as
servicedate, technician, reportid, device serialnumber, repair location,
fault information & time to do repair.

Table 2.
This logs what units were cleaned, what were QA'd & what were electrically
safety tested and the dates. I work for a medical firm that rents out units
but most units do not come through our repair section and therefore do not
get info stored in Table1. However most records that have data in Table1 will
also have data in table2.

Table 3.
This covers any other work that we do like adhoc duties, stocktake, training
staff, deliveries etc.

So these 3 tables pretty much don't relate to each other except for the
servicedate.

I have a report which contains multiple sub reports which gives totals on all
the above over a specified time period however my manager has recently
decided he wants a very basic report which details daily figures.

Thanks for your assistance with this and I will give it a go when I am next
in the office.

If this were mine, and if there was NOT an already existing list of service
dates, I'd probably use a union query to derive all possible dates from the
three queries! You could use something like (untested psuedo-SQL follows):

SELECT [ServiceDate] FROM Table1
UNION
SELECT [ServiceDate] FROM Table2
UNION
SELECT [ServiceDate] FROM Table3;

This would give you all/any service dates in the underlying data. (Do you
actually have three separate tables you are storing service dates in? If
so, why?)

Then use the "directional" joins from the results of this union query to
each of your existing queries.

This is not "elegant", but I suspect it will get the job done...

Thanks Jeff,

[quoted text clipped - 19 lines]