querys: Getting two collumns of summed totals based off a third

[Guest]

The problem:

What i want to come back out of the query is

Directorate :: Sum of the Absences for this Directorate where length = short
term :: Total records summed :: sum of the Absences for Long term :: Total
records summed

Directorate is in one table, JobSpecs, linked to Job, which is then linked
to the personnell and through to the absences.

What I tried was having a set of linked querys

1: works out whether they are long term or short term
2: Takes the short term ones
3: sums for each person
4: sums for each directorate and gives me the total number of people
(although it is not unique people, which is another problem.

then we have:
5: take the long term ones
6: sums for each person
7: sums for each directorate and has the total number of people

they both have directorate in, but when I try and create a query linking
these two things together it says that there is no link at all. which is
annoying. I can't work out why or how to do it any differently. Tried havign
everything calculate in one query, but it only picks up one directorate
rather than them all.

Any advice?

 

[Guest]

Right, I have managed to sort out one of the problems, by creating a
relationship between the two tables in the query (I didn't know you could do
this) but now, it only shows up the one value where there is something in
both the long term side and the short term, so is there any way of getting
the query just to put a zero or something into where there is no linking
record accross both sub-querys?

thanks

-k

 

[Guest]

Hi Kizzie,

It sounds like you have probably created an inner join between your queries,
which will only produce those people that have records in both queries.

You can double click on the relationship line between the queries to change
it to a left or right join, which will give all records from one query or the
other, and all matches from the second. But, that still won't produce all
records.

If you want to see all records, your best bet will probably be to add your
table containing all of your people to the query. Delete the relationship
between the queries, and instead link each one to the table, using an outer
join. To do this, drag the join line between one of the queries and the
table (I'm assuming that you are using the query design grid). Then,
double-click the resulting relationship line and a dialog box will open.
Change the relationship type to return all records from your people table,
and the matches from the query. Repeat the process for linking the second
query to the table.

For the output fields, use the table for the people names, and the absence
fields from the queries. This will list all names, and either values or
blanks from the queries (depending on whether or not records exist for the
name).

Hope that helps.

-Ted Allen