Hi guys! Happy new year to everyone!

I have a couple of existing queries that come together to produce sales information. Right now, it is done only at the national level but we are looking to calculate it by zone (atlantic, prairie, quebec, etc.).

I have another table that is not currently part of this query that contains the information about which zone each dealer belongs to. I just dont know how to go about adding it into this query...

In the SQL, I have included comments as to my understanding of what is going on. If anyone notices any mistakes, please correct me! I appreciate your help!

' select all into this table
SELECT * INTO [19c) vehicle_sales_temp]
' these fields
FROM
' retail sales only, count each record as a sale
[SELECT 'RETAIL' AS TYPE, Count(*) AS SALES,
' extract year and month, put a space between and set as the transaction date
Left([DWP_F_DEALER_VEHICLE_INVENTORY]![PROCESS_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![PROCESS_DATE_SID],5,2) AS TRANSACTION_DATE
' these tables
FROM DWP_F_DEALER_VEHICLE_INVENTORY INNER JOIN DWP_D_VEHICLE_TYPE_APX
' the inner join occurs on this field
ON DWP_F_DEALER_VEHICLE_INVENTORY.VEHICLE_TYPE_SID = DWP_D_VEHICLE_TYPE_APX.VEHICLE_TYPE_SID
' select records where the process date is between the following dates
WHERE (((DWP_F_DEALER_VEHICLE_INVENTORY.PROCESS_DATE_SID ) Between 20080101 And 20081231)
' and where series in the first table are found in the next table
AND ((DWP_D_VEHICLE_TYPE_APX.SERIES_CD) IN (SELECT [19c1) vehicle_abbreviation_temp].VehicleAbbreviation FROM [19c1) vehicle_abbreviation_temp]) ))
' group by RETAIL first, then by date
GROUP BY 'RETAIL', Left([DWP_F_DEALER_VEHICLE_INVENTORY]![PROCESS_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![PROCESS_DATE_SID],5,2)
' also select all records into the same table
UNION ALL
' wholesale only, count each record as a sale
SELECT 'W/S' AS TYPE, Count(*) AS SALES,
' extract year and month, put a space between and set as the transaction date
Left([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],5,2) AS TRANSACTION_DATE
' these tables
FROM DWP_F_DEALER_VEHICLE_INVENTORY INNER JOIN DWP_D_VEHICLE_TYPE_APX
' the inner join occurs on this field
ON DWP_F_DEALER_VEHICLE_INVENTORY.VEHICLE_TYPE_SID = DWP_D_VEHICLE_TYPE_APX.VEHICLE_TYPE_SID
' select records where the wholesale date is between the following dates
WHERE (((DWP_F_DEALER_VEHICLE_INVENTORY.WHOLESALE_DATE_S ID) Between 20080101 And 20081231)
' and where the series in the first table are found in the second table
AND ((DWP_D_VEHICLE_TYPE_APX.SERIES_CD) IN (SELECT [19c1) vehicle_abbreviation_temp].VehicleAbbreviation FROM [19c1) vehicle_abbreviation_temp]) ))
' group by W/S first, then by date
GROUP BY 'W/S', Left([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],5,2) ].
' temp table
AS vehicleSales;

The other table that has the dealer & zone information I want to include in this query would work like this:

table: DWP_D_DEALER
field: DEALER_SID

join:
DWP_D_DEALER INNER JOIN DWP_F_DEALER_VEHICLE_INVENTORY ON DWP_D_DEALER.DEALER_SID = DWP_F_DEALER_VEHICLE_INVENTORY.DEALER_SID

Ok... no help so far...

Can anyone suggest where I would put this grouping? Each sale records the dealer code, which in the table I mentioned in post #2 is associated to one of 7 Zones.

Would I add "UNION ALL" and then the SELECT statement to the end of the SQL? Do I start the SQL off with the grouping by zone?

Any suggestions would be greatly appreciated...

Cheers!

First lets clean up that SQL to something readable....

SELECT * INTO [19c) vehicle_sales_temp]
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
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
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'
, 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]![WHOLESALE_DATE_SID],4) & ' ' &
Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],5,2) AS TRANSACTION_DATE
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

WHERE DWP_F_DEALER_VEHICLE_INVENTORY.WHOLESALE_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'
, Left([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],4) & ' ' &
Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],5,2) ]. AS vehicleSales;

Now that is much better with all the extra () gone and properly spaced


The way to join multiple tables is....
From Table1
Join Table2 on Table1.Field1 = Table2.Field1
Join Table3 on Table1.Field1 = Table3.Field1
Join Table4 on Table2.Field2 = Table4.Field2

Thusly your DWP_D_DEALER INNER JOIN DWP_F_DEALER_VEHICLE_INVENTORY ON DWP_D_DEALER.DEALER_SID = DWP_F_DEALER_VEHICLE_INVENTORY.DEALER_SID
Is slightly off... I hope you can fix that for "us" now.... without me telling you exactly how to...

You will then have to add this 'ammended' join to both queries ...
Query1
Union
Query2

And add your region field to the select AND group by part of your queries...

I hope you understand, if not post back with your problems.

Good luck!

Mailman,

Thank you for not handing me the answer on a silver platter. I come this website for help with issues, not a hand out... Plus this is the only way I will both learn AND retain information! I really appreciate your help!

So, here is what I have come up with based on the hints you left in your reply...

SELECT * INTO [19c) vehicle_sales_temp]
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, DEALER_SID, [DWP_D_DEALER]![DEALER_CODE]
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, DWP_F_DEALER_VEHICLE_INVENTORY 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 'RETAIL' , [DWP_D_DEALER]![DEALER_CODE], 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]![WHOLESALE_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],5,2) AS TRANSACTION_DATE, DEALER_SID, [DWP_D_DEALER]![DEALER_CODE]
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, DWP_F_DEALER_VEHICLE_INVENTORY INNER JOIN DWP_D_DEALER ON DWP_F_DEALER_VEHICLE_INVENTORY.DEALER_SID = DWP_D_DEALER.DEALER_SID
WHERE DWP_F_DEALER_VEHICLE_INVENTORY.WHOLESALE_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_CODE], Left([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],5,2) ].
AS vehicleSales;

Cheers!

You really should reformat your SQL to be more readable, like I did.... Goes towards maintainability.

If your code now works as you want it to... Great! and Happy to help... I also prefer not handing out solutions on a silver plater... Helps to get the mind juices flowing... on both sides...

hmm... when I try to save the SQL, I get this error message:

Invalid bracketing of name ' SELECT 'RETAIL' AS TYPE, Count(*) AS SALES, Left([DWP_F_DEALER_VEHICLE_INVENTORY'.

any ideas?

FROM [ SELECT

I couldn't find the closing bracket, but that could easily be me.

Brian

correction found it.

You can look at the SQL with the [] around the subselect, but you cannot paste it for some reason.... Replace the []. by () and try and paste that.

After running though, access will happily return it to []. but pasting it doesnt work for some reason.... :confused:

I don't get it either...

It took it this time, and let me save it... but now if I try to run the query, it says there is a Sytax error in the FROM clause... :confused:

I tried rewriting the SQL, but I am still getting the "syntax error" in the FROM clause...

here is the SQL

SELECT * INTO [PNV_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, DEALER_SID, [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
'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]![WHOLESALE_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY],5,2) AS TRANSACTION_DATE, DEALER_SID, [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.WHOLESALE_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]![WHOLESALE_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],5,2) )

AS vehicleSales;


Any one have any ideas?

Cheers,

Colin

There are no comma's in the from clause if you use the "Join" syntax

Also when you post code... please Please, pretty please.... format it so its semi-readable?

So those commas are probably causing the syntax error? thanks

Sorry about my poor formatting skills... I thought I had made it "semi-readable" :o

version 2:

SELECT * INTO PNV_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, DEALER_SID, [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 '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]![WHOLESALE_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY],5,2) AS TRANSACTION_DATE, DEALER_SID, [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.WHOLESALE_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]![WHOLESALE_DATE_SID],4) & ' ' & Mid([DWP_F_DEALER_VEHICLE_INVENTORY]![WHOLESALE_DATE_SID],5,2)
].
AS ZonevehicleSales;

version 2:

So now its working??

Formatting, better...:cool:

Points of note:

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, DEALER_SID, [DWP_D_DEALER]![DEALER_ZONE_DESCRIPTION]

The left edge of the Select statement should ONLY contain Select words... Check my format in Post #2 ...
Also every , should also trigger a new line...


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

Do things one way.... either the = at the start or the end... Its inconcistancies like this that make things hard to follow in big queries.

The same about the 2 sub-queries... their formatting in the from part is inconcistant....

** Disclaimer **
I am no saint either and far from perfect just trying to help you here.

So now its working??

No unfortunately it isn't... I'm getting a different error message...
It says:

Syntax error (missing operator) in query expression
'DWP_F_DEALER_VEHICLE_INVENTORY.VEHICLE_TYPE_SID = DWP_D_VEHICLE_TYPE_APX.VEHICLE_TYPE_SID
INNER JOIN DWP_F_DEALER_VEHICLE_INVENTORY.DEALER_SID = DWP_D_DEALER.DEALER_SID'

Formatting, better...:cool:

Points of note:

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, DEALER_SID, [DWP_D_DEALER]![DEALER_ZONE_DESCRIPTION]

The left edge of the Select statement should ONLY contain Select words... Check my format in Post #2 ...
Also every , should also trigger a new line...


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

Do things one way.... either the = at the start or the end... Its inconcistancies like this that make things hard to follow in big queries.

The same about the 2 sub-queries... their formatting in the from part is inconcistant....

** Disclaimer **
I am no saint either and far from perfect just trying to help you here.

When I typed it up in word, it looked all nice, but when I copied it in between the code quotations, it screwed everything up... :D

Make sure there is a space between the last character of the previous line and the new line.

Change the []. to ()

See if that helps?
If it fails... put () around the first inner join...

Excuse my butting in and I may be being stupid but I could not find

'DWP_F_DEALER_VEHICLE_INVENTORY.VEHICLE_TYPE_SID = DWP_D_VEHICLE_TYPE_APX.VEHICLE_TYPE_SID
INNER JOIN DWP_F_DEALER_VEHICLE_INVENTORY.DEALER_SID = DWP_D_DEALER.DEALER_SID'

in the code posted and I thought that the syntax was
Inner Join tablename On

that is in the posted code but not the message


Brian

No luck...

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

Excuse my butting in and I may be being stupid but I could not find



in the code posted and I thought that the syntax was
Inner Join tablename On

that is in the posted code but not the message


Brian

That's what I was wondering... in the code it reads:

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

But it doesn't include the first part in the error message... :confused:

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

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...

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

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


Brian

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

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

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

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

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?

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

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

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 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.

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

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? :eek:

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

[QUOTE=Colin@Toyota;798304]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? :eek:

QUOTE]

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

Brian

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

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

Cheers,

Colin

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

Brian

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=DW P_D_VEHICLE_T YPE_APX.VEHICLE_TYPE_SID
INNER JOIN DWP_D_DEALER ON
DWP_F_DEALER_VEHICLE_INVENTORY.DEALER_SID=DWP_D_DE ALER.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;

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

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.