Joining Queries

[lemo]

just a quick question -
is it possible, in the statement below, instead of "query1" to put actual SQL syntax for that query?
if not, is there a workaround?

FROM table1 INNER JOIN query1 ON table1.var1 = query1.var1

 

[MSAccessRookie]

No reason that should not work as written

just a quick question -
is it possible, in the statement below, instead of "query1" to put actual SQL syntax for that query?
if not, is there a workaround?

FROM table1 INNER JOIN query1 ON table1.var1 = query1.var1

We use this exact syntax all of the time. Many of the queries here are used like programming objects and called by other queries (sometimes several levels deep). What is not working for you?

 

[lemo]

if i change "query1" to actual query syntax, it's not working..
e.g.

FROM table1 INNER JOIN (SELECT Ratings.[Cust ID], First([Cust ID] & [Type]) AS CustType
FROM Ratings
GROUP BY Ratings.[Cust ID])
ON table1.var1 = (SELECT Ratings.[Cust ID], First([Cust ID] & [Type]) AS CustType
FROM Ratings
GROUP BY Ratings.[Cust ID]).var1

 

[MSAccessRookie]

Now I understand

if i change "query1" to actual query syntax, it's not working..
e.g.

FROM table1 INNER JOIN (SELECT Ratings.[Cust ID], First([Cust ID] & [Type]) AS CustType
FROM Ratings
GROUP BY Ratings.[Cust ID])
ON table1.var1 = (SELECT Ratings.[Cust ID], First([Cust ID] & [Type]) AS CustType
FROM Ratings
GROUP BY Ratings.[Cust ID]).var1

I think that We may need to separate the actual from the hypothetical here. The query cannot work as written for at least the following three reasons:

1. There is no field var1 defined in the actual query. var1 represents the actual common column that is used to link the table to the query ([Cust ID] perhaps?)

2. The On Clause needs an assigned alias in the FROM statement.

The rewritten query would look somnething like this.:

FROM table1 INNER JOIN
(SELECT Ratings.[Cust ID], First([Cust ID] & [Type]) AS CustType
FROM Ratings
GROUP BY Ratings.[Cust ID]) AS Qry1
ON table1.var1 = Qry1.var1


I think this should get you going

 

[jal]

I have a similar question. A while back I was trying to avoid using a temptable, meaning i was trying to change this:

Select From Table1, tempTable
Where Table1.ID = tempTable.ID

into this:

Select From Table1, (Select whatever From whatever) as tempTable
Where Table1.ID = tempTable.ID

and I never got it working. Does Access support this kind of substitution?

 

[MSAccessRookie]

Below is a simple example of what I am talking about. (Earlier today I was creating a query to show the parts of a date from the Now() function).

SELECT CurrentTime, MonthName, MonthID, DayNumber, Year FROM
(SELECT distinct now() AS CurrentTime,
monthname(month(now())) AS MonthName,
month(now()) AS MonthID,
day(now()) AS DayNumber,
year(now()) AS Year
FROM Institutions)

{ Note that Institutions is one of my table names and that you will need to substitute one of your own. }

 

[lemo]

FROM table1 INNER JOIN
(SELECT Ratings.[Cust ID], First([Cust ID] & [Type]) AS CustType
FROM Ratings
GROUP BY Ratings.[Cust ID]) AS Qry1
ON table1.var1 = Qry1.var1

yes!!! it worked!...
thanks much, MSAccessRookie (are you really?)..
i was struggling with it for a few days now.
feeling dumb and happy now,
l

 

[MSAccessRookie]

FROM table1 INNER JOIN
(SELECT Ratings.[Cust ID], First([Cust ID] & [Type]) AS CustType
FROM Ratings
GROUP BY Ratings.[Cust ID]) AS Qry1
ON table1.var1 = Qry1.var1

yes!!! it worked!...
thanks much, MSAccessRookie (are you really?)..
i was struggling with it for a few days now.
feeling dumb and happy now,
l

The pleasure was mine. I may still be an Access Rookie, but I have been using SQL for many years, and I am always happy to assist when I am able to do so.