Nested Query only returning lowest number

  • Thread starter Thread starter Nine Doors
  • Start date Start date
N

Nine Doors

Hi Everyone,
Quick question here. I have a query that returns a series of numbers.
So in my first query I have

TABLE A
LogID
TransActNum

This returns typically 3 or 4 records, all with different TransActNum's.

Then I use that query, and combined with another table have written a
further query try to find any occurrence of any of the TABLE A.TransActNum
reported in the first query, in a field in the second table.

Problem is, the query only returns instances of the lowest number from the
TransActNum list.
Does anyone have any idea how I could possibly get all occurrences of the 3
or 4 records in the transActNum field?

Many thanks in advance,

Niner
 
Hi,


You probably need an outer join rather than an inner join (the actual
inner join may be implicit through the WHERE clause, or explicit in the FROM
clause).


Hoping it may help,
Vanderghast, Access MVP
 
Hi there Michel,
Thanks for your response. Unfortunately however, I have tried every possible
join there is, and still am returning only the lowest value from the first
query. Would it be of any help if I copied the SQL here for you to take a
look at?
(Even when I select on the join, to show all records from TableA it still
only returns the lowest value)

ARGH! Frustrating...sorry

Niner
 
Hi,


definively, that would help if you copy the SQL statement (or a simplified
version, if the original is somehow confidential).


Vanderghast, Access MVP
 
Hi Michel,
I'll try to explain this as best I can...
SalesSummary_Drop2 is the first query, and customers is the table I use in
the second query.
SalesSummary_Drop2.LogID = Customers.StartpointID
TransActNum field should be populated with several values from the
SalesSummary_Drop2.LogID...but it's only returning the lowest value. I'm in
desperate need for it to return ALL the values that were generated in the
orginal query.
Hope this makes sense...


SELECT Customers.ShiftID, Customers.EmpID, Customers.RoomID,
Customers.StartPointID, Customers.TransActNum
FROM Customers RIGHT JOIN SalesSummary_drop2 ON Customers.StartPointID =
SalesSummary_drop2.LogID
WHERE (((Customers.TransActNum)=[salessummary_drop2].[logID]))
GROUP BY Customers.ShiftID, Customers.EmpID, Customers.RoomID,
Customers.StartPointID, Customers.TransActNum;

Thanks again for any insight Michel,

Nikki
 
Hi,


Your WHERE clause is the problem. It involves (part of if involves) two
tables in a relation. That should be moved in the ON clause:

instead of:

SELECT Customers.ShiftID, Customers.EmpID, Customers.RoomID,
Customers.StartPointID, Customers.TransActNum
FROM Customers RIGHT JOIN SalesSummary_drop2 ON Customers.StartPointID =
SalesSummary_drop2.LogID
WHERE (((Customers.TransActNum)=[salessummary_drop2].[logID]))
GROUP BY Customers.ShiftID, Customers.EmpID, Customers.RoomID,
Customers.StartPointID, Customers.TransActNum;


try:

SELECT Customers.ShiftID, Customers.EmpID, Customers.RoomID,
Customers.StartPointID, Customers.TransActNum
FROM Customers RIGHT JOIN SalesSummary_drop2
ON (Customers.StartPointID => SalesSummary_drop2.LogID)
AND
(Customers.TransActNum=salessummary_drop2.logID)
GROUP BY Customers.ShiftID, Customers.EmpID, Customers.RoomID,
Customers.StartPointID, Customers.TransActNum;



Your initial WHERE clause (completely removed, in this case) was an
"implicit join" was asking for a strict match between customers.transActNum
and a field from your query. Moving it in the ON clause, the RIGHT join make
we are sure that every record from the query will be kept, even if there is
no match. Note that you may prefer to GROUP BY (and SELECT) the fields
taken from the query, NOT those from Customers, since the GROUP BY
definitively define GROUPS, and keep just one record per GROUP (otherwise,
what will be a group if records were ... not grouped ).





Hoping it may help,
Vanderghast, Access MVP
Nine Doors said:
Hi Michel,
I'll try to explain this as best I can...
SalesSummary_Drop2 is the first query, and customers is the table I use in
the second query.
SalesSummary_Drop2.LogID = Customers.StartpointID
TransActNum field should be populated with several values from the
SalesSummary_Drop2.LogID...but it's only returning the lowest value. I'm in
desperate need for it to return ALL the values that were generated in the
orginal query.
Hope this makes sense...


SELECT Customers.ShiftID, Customers.EmpID, Customers.RoomID,
Customers.StartPointID, Customers.TransActNum
FROM Customers RIGHT JOIN SalesSummary_drop2 ON Customers.StartPointID =
SalesSummary_drop2.LogID
WHERE (((Customers.TransActNum)=[salessummary_drop2].[logID]))
GROUP BY Customers.ShiftID, Customers.EmpID, Customers.RoomID,
Customers.StartPointID, Customers.TransActNum;

Thanks again for any insight Michel,

Nikki






Michel Walsh said:
Hi,


definively, that would help if you copy the SQL statement (or a simplified
version, if the original is somehow confidential).


Vanderghast, Access MVP
Click to expand...
take
a
Click to expand...
written
a
Click to expand...
Click to expand...
 
Back
Top