Need help adding grouping to an existing query

[Brianwarnock]

No luck...

Should the WHERE clause that refers to the first INNER JOIN come before the second INNER JOIN?

No the From is all one clause, then the Where clause.

Brian

 

[Colin@Toyota]

No the From is all one clause, then the Where clause.

Brian

That's what I thought, but I was wondering if you could nest it in there like with an IIf statement or something...

 

[Brianwarnock]

Have you tried ( ) round the first inner join?

FROM ( DWP_F_DEALER_VEHICLE_INVENTORY
INNER JOIN DWP_D_VEHICLE_TYPE_APX ON
DWP_F_DEALER_VEHICLE_INVENTORY.VEHICLE_TYPE_SID
= DWP_D_VEHICLE_TYPE_APX.VEHICLE_TYPE_SID )
INNER JOIN DWP_D_DEALER ON
DWP_F_DEALER_VEHICLE_INVENTORY.DEALER_SID
= DWP_D_DEALER.DEALER_SID


Brian


Edit Namlian suggested this in post 17

 

[Brianwarnock]

Ran a quick and simple test and you do need () round the first inner join


Brian

 

[Colin@Toyota]

I am still getting a syntax error in the from clause...

 

[Colin@Toyota]

Here is my SQL in a word document... layed out a bit nicer, and easier to read.

 

[Brianwarnock]

If been musreading you code, I thought you had 3 tables with 1 joined to two.
Try
FROM DWP_F_DEALER_VEHICLE_INVENTORY,
(INNER JOIN DWP_D_VEHICLE_TYPE_APX ON
DWP_F_DEALER_VEHICLE_INVENTORY.VEHICLE_TYPE_SID
= DWP_D_VEHICLE_TYPE_APX.VEHICLE_TYPE_SID )
INNER JOIN DWP_D_DEALER ON
DWP_F_DEALER_VEHICLE_INVENTORY.DEALER_SID
= DWP_D_DEALER.DEALER_SID

Put my eyes back in and see that you have 1 to 2, sorry

Brian

 

[Colin@Toyota]

If been musreading you code, I thought you had 3 tables with 1 joined to two.
Try
FROM DWP_F_DEALER_VEHICLE_INVENTORY,
(INNER JOIN DWP_D_VEHICLE_TYPE_APX ON
DWP_F_DEALER_VEHICLE_INVENTORY.VEHICLE_TYPE_SID
= DWP_D_VEHICLE_TYPE_APX.VEHICLE_TYPE_SID )
INNER JOIN DWP_D_DEALER ON
DWP_F_DEALER_VEHICLE_INVENTORY.DEALER_SID
= DWP_D_DEALER.DEALER_SID

I do have three tables:
DWP_F_DEALER_VEHICLE_INVENTORY
DWP_D_VEHICLE_TYPE_APX
DWP_D_DEALER

DWP_F_DEALER_VEHICLE_INVENTORY is joined to the other two tables by different fields.

... at least, that's what I'm trying to do!

I'll try that and let you know

 

[Colin@Toyota]

Still not workin'...

Also, any idea why everytime I open it, it changes the brackets in the very first FROM clause and the end of the sub-query from "(" and ")" to "[" and "].", but it wont let me save it like that? I have to change them back to the "(" and ")", but if I save and close, then go back in, they have changed back to "[" and "]."

Weird?

 

[Brianwarnock]

The answer to your last question is no, but wonder if the system is trying to tell us something.

My silly little test looks like this

SELECT Table1.id, Table10.id, table1a.id, Table1.[date of return], table1a.[date of return], Table10.a, Table1.bookname
FROM (Table1 INNER JOIN Table10 ON Table1.id = Table10.id) INNER JOIN table1a ON Table1.[date of return] = table1a.[date of return]
WHERE (((Table1.bookname)="aa"));


It works and I get the same syntax error as the one you quoted if I leave the ( ) out of the From.

I'm out of ideas.

Brian

 

[Colin@Toyota]

So I was playing around with the placement of brackets in the FROM statement, and I tried to run the query and got a message saying something about DEALER_SID potentially belonging to more than one table... Was I missing something as silly as a rule that if the three tables share a field, it can't be used as the rule for an INNER JOIN?

After I got that, I thought that maybe in the SELECT statement of the first sub-query, I had better reference which table I wanted to pull DEALER_SID from, since it was just there by itself. I added the table reference to both sub-queries, and now I get a different error message... somthing about me not including the specified expression 'DEALER_SID' as part of an aggregate function.

This is getting annoying!

 

[Colin@Toyota]

I think Access automatically surrounds source tables with the [] characters. That may be why. What happens when u simply suppress the [] at the beg. and end of the outside FROM clause? I believe the error is referring to the OUTSIDE clause, not ne one of the inside ones...

It didn't like them at the beginning and end, but I put the [] at the beginning, and I saved and closed it, and it changed to a UNION query instead of the MAKE-TABLE it should be.

 

[Brianwarnock]

So I was playing around with the placement of brackets in the FROM statement, and I tried to run the query and got a message saying something about DEALER_SID potentially belonging to more than one table... Was I missing something as silly as a rule that if the three tables share a field, it can't be used as the rule for an INNER JOIN?

After I got that, I thought that maybe in the SELECT statement of the first sub-query, I had better reference which table I wanted to pull DEALER_SID from, since it was just there by itself. I added the table reference to both sub-queries, and now I get a different error message... somthing about me not including the specified expression 'DEALER_SID' as part of an aggregate function.

This is getting annoying!

I think you might be getting there, There are 5 fields selwected, 4 are grouped, one is counted and Dealer_sid is well not part of an aggregate function, I think that you have got that now because you have corrected other prior faults, prior as far as the system cares.

Brian

PS slow response because of T and Tele

 

[Colin@Toyota]

I think I may have come to a realization... I included DEALER_SID in the Select statement for no other reason that it is the field that the tables are joined on. I don't need to do that do I?

I'm going to try taking that out now...

 

[Brianwarnock]

I think I may have come to a realization... I included DEALER_SID in the Select statement for no other reason that it is the field that the tables are joined on. I don't need to do that do I?

QUOTE]

Been there, done that, washed the &*&* off the T shirt.

Brian

 

[Colin@Toyota]

It worked! I'm not totally useless at SQL!

Thanks for your collective help Aje, Namlaim and Brian! I appreciate it!

Cheers,

Colin

 

[Brianwarnock]

Great , just out of interest/curiosity could you post your working SQL

Brian

 

[Colin@Toyota]

Sure, Brian. Here it is:

SELECT * INTO [SALES_BY_ZONE]
FROM 
[
SELECT 		'RETAIL' 	AS TYPE, 
                Count(*) 	AS SALES, 
                Left([DWP_F_DEALER_VEHICLE_INVENTORY]![PROCESS_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![PROCESS_DATE_SID],5,2) 	AS TRANSACTION_DATE, 
                [DWP_D_DEALER]![DEALER_ZONE_DESCRIPTION]

FROM 		DWP_F_DEALER_VEHICLE_INVENTORY
INNER JOIN 	DWP_D_VEHICLE_TYPE_APX 	        ON 
                      DWP_F_DEALER_VEHICLE_INVENTORY.VEHICLE_TYPE_SID=DWP_D_VEHICLE_T                      YPE_APX.VEHICLE_TYPE_SID
INNER JOIN 	DWP_D_DEALER 			ON 
                      DWP_F_DEALER_VEHICLE_INVENTORY.DEALER_SID=DWP_D_DEALER.DEALER_S                      ID

WHERE 	   DWP_F_DEALER_VEHICLE_INVENTORY.PROCESS_DATE_SID Between 20080101 And 20081231 And DWP_D_VEHICLE_TYPE_APX.SERIES_CD In (SELECT [19c1) vehicle_abbreviation_temp].VehicleAbbreviation FROM [19c1) vehicle_abbreviation_temp])

GROUP BY 	'RETAIL', 
                [DWP_D_DEALER]![DEALER_ZONE_DESCRIPTION], 
                Left([DWP_F_DEALER_VEHICLE_INVENTORY]![PROCESS_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY].[PROCESS_DATE_SID],5,2)

UNION ALL

SELECT 		'W/S' 		AS TYPE, 
                Count(*) 	AS SALES, 
                Left([DWP_F_DEALER_VEHICLE_INVENTORY]![PROCESS_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![PROCESS_DATE_SID],5,2) 			AS TRANSACTION_DATE, 
                [DWP_D_DEALER]![DEALER_ZONE_DESCRIPTION]

FROM 	        DWP_F_DEALER_VEHICLE_INVENTORY
INNER JOIN 	DWP_D_VEHICLE_TYPE_APX 	        ON 
                      DWP_F_DEALER_VEHICLE_INVENTORY.VEHICLE_TYPE_SID = 
                      DWP_D_VEHICLE_TYPE_APX.VEHICLE_TYPE_SID
INNER JOIN 	DWP_D_DEALER 			ON 
                      DWP_F_DEALER_VEHICLE_INVENTORY.DEALER_SID = 
                      DWP_D_DEALER.DEALER_SID

WHERE 	             DWP_F_DEALER_VEHICLE_INVENTORY.PROCESS_DATE_SID Between 20080101 And 20081231 And DWP_D_VEHICLE_TYPE_APX.SERIES_CD In (SELECT [19c1) vehicle_abbreviation_temp].VehicleAbbreviation FROM [19c1) vehicle_abbreviation_temp])

GROUP BY 	'W/S', 
                [DWP_D_DEALER]![DEALER_ZONE_DESCRIPTION], 
                Left([DWP_F_DEALER_VEHICLE_INVENTORY]![PROCESS_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY].[PROCESS_DATE_SID],5,2)
]. AS ZoneVehicleSales;

 

[Brianwarnock]

Thanks but erm , there are no extra () in the From clause, infact as far as my old eye can make out the only difference between this and Post 14 is the disappearance of DEALER_SID from the Select clauses.
Don't say that's what caused all the problems, I know Microsoft's diagnostics and error messages are poor but that would take the biscuit.

Brian

 

[Colin@Toyota]

I forgot to put the brackets in, but yes... DEALER_SID was causing all the problems. As soon as I took it out, the query ran perfectly.