RE: Repost - help to merge 2 queries

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi,

Hmmm... I think I am missing something here. Could you provide some
sample data for each of the tables and what you would expect as a result?

Thanks,

Clifford Bass

"uk_firebrand" wrote:

Hi again,

I tried your method, Clifford, and unfortunately it doesn't work. Having
left joins on table b brings back results for the entire data set, not just
for the skill/site criteria combo concerned.

Any further suggestions would be welcomed?

Thanks,
--
Mike Peate


"uk_firebrand" wrote:

Thanks for your reply, Clifford - most appreciated.

It sounds counterintuitive but I will give it a go when I return to work in
an hour or so. Will post again to confirm if worked ok.
--
Mike Peate



"Clifford Bass" wrote:

Hi Mike,

You actually probably need to make them all left joins, even though
the one between a and b is really an inner join. Try the following
(untested):

SELECT c.Date, a.Site, Sum(a.ACD_CALLS)
FROM (c LEFT JOIN a ON c.Date = a.DATE) LEFT JOIN b ON b.Site = a.Site and
b.Skill = a.Skill
GROUP BY c.Date, a.Site;

Note the reversal of the grouping. In theory it allows you to drop the
order by clause when you want the information sorted in the same order as the
grouping.

Clifford Bass

"uk_firebrand" wrote:

Hi all,

I've simplified my original post as very long winded - apologies for that;
it was a busy day at work.

I want to run a single query that has inner joins across 2 tables:2 fields
and a left join on 1 table:1 field. Due to an "ambiguous joins" issue when I
attempt to run as a single query, I have had to split this into 2 queries
i.e. inner joins in 1 query, left join in another.

I can perform this type of query in an SQL+ environment however, I'm having
issues porting the same logic to Access and I was hoping someone could assist
in creating a single, merged query.

There are 3 tables involved

a - raw data
b - criteria list for return values where 2 joined fields must be equal
(a.field=b.field)
c - a list of dates in the current year; used as left join on first query to
return all dates from c and the 1st query's results where they exist


The first query pulls back the data I need: -

SELECT a.Site, a.DATE, Sum(a.ACD_CALLS), etc...
FROM a INNER JOIN b ON (a.Site = b.Site) AND (a.Skill = b.Skill)
GROUP BY a.Site, a.DATE
ORDER BY a.DATE;

The second query references the first with a LEFT join on a table containing
all the dates in the current year and, essentially, will pull back all those
dates and any values from the first query where the dates match: -

SELECT c.Date, 1stQuery.1stField, 1stQuery.etc...
FROM c LEFT JOIN 1stQuery ON c.Date = 1stQuery.DATE
ORDER BY c.Date;


Is there any way, possibly using a subquery in the first, that I can the
merge the two queries into one?

Many thanks in advance,
--
Mike Peate
.

Relevant Pages

  • RE: Repost - help to merge 2 queries
    ... I tried your method, Clifford, and unfortunately it doesn't work. ... I want to run a single query that has inner joins across 2 tables:2 fields ... The first query pulls back the data I need: ...
    (microsoft.public.access.queries)
  • RE: Repost - help to merge 2 queries
    ... Thanks for your reply, Clifford - most appreciated. ... I want to run a single query that has inner joins across 2 tables:2 fields ... The first query pulls back the data I need: ...
    (microsoft.public.access.queries)
  • RE: Repost - help to merge 2 queries
    ... I want to run a single query that has inner joins across 2 tables:2 fields ... The first query pulls back the data I need: ...
    (microsoft.public.access.queries)
  • Re: The Microsoft Jet database engine does not recognize...
    ... In the first query, ... Choose Parameters on the Query menu. ... Access opens a dialog. ... (tblGeoLoc INNER JOIN (tblLocPicker INNER ...
    (microsoft.public.access.queries)
  • Re: Database-Query and AutoFill??????
    ... first query, so it gets data from both source tables. ... rightmost one - leave no gaps) with formula, which will retrieve according ... In data range properties for first query, ... > values of coloumn 1 and 2. ...
    (microsoft.public.excel.misc)