Ms Access Query Issue (1 Viewer)

[firoz.raj]

Can anyone tell me ?.When i try to run the following query it says syntax error(missing operator) in query expression ic_ItemID.itemID =Ic_Itembarcode.ItemID Inner join dbo.IC_Itemdefaultprices p on Ic_ItemID.ItemID=P.ItemID.

SELECT IC_Itembarcode.itemID,
IC_Itembarcode.Barcode,
IC_Itembarcode.Serial1,
IC_Itembarcode.Serial2,
IC_Itembarcode.Serial3,
IC_Itembarcode.Serial4,
IC_Itembarcode.SerialCombination,
IC_ItemID.L1Description,
IC_ItemID.L2Description,
IC_ItemID.ExtraL1Description,
IC_ItemID.ExtraL2Description,
MIN(Price) As Price,
Max(StartDate) As StartDate
From IC_ItemID
INNER JOIN IC_Itembarcode ON IC_ItemID.itemID=IC_Itembarcode.itemID
INNER JOIN dbo.IC_ItemDefaultPrices p ON  IC_ItemID.itemID= P.itemID 
 WHERE  P.STARTDATE = (Select Max(StartDate) from dbo.IC_ItemDefaultPrices As B   Where B.ItemID Like 'BUR304*' and b.startdate <= '2011-12-28')  and IC_ItemID.ItemID Like 'BUR304*'  GROUP BY IC_Itembarcode.itemID, IC_Itembarcode.Barcode, IC_Itembarcode.Serial1, IC_Itembarcode.Serial2,  IC_Itembarcode.Serial3, IC_Itembarcode.Serial4, IC_Itembarcode.SerialCombination, IC_ItemID.L1Description,  IC_ItemID.L2Description, IC_ItemID.ExtraL1Description , IC_ItemID.ExtraL2Description

 

[Galaxiom]

Access wants the joins nested with parentheses.

Make a multiple join query in the query designer and you will see the structure it requires.

 

[firoz.raj]

Access wants the joins nested with parentheses.

Make a multiple join query in the query designer and you will see the structure it requires.

yes that is exactly .but most of time i have worked on sql server .but i need to adapt the this query in access .can you tell me how should
i write in nested way to the inner join ? .let me know please .

SELECT IC_Itembarcode.itemID, IC_Itembarcode.Barcode, IC_Itembarcode.Serial1, IC_Itembarcode.Serial2, IC_Itembarcode.Serial3, IC_Itembarcode.Serial4, IC_Itembarcode.SerialCombination, IC_ItemID.L1Description, IC_ItemID.L2Description, IC_ItemID.ExtraL1Description, IC_ItemID.ExtraL2Description, MIN(Price) AS Price, Max(StartDate) AS StartDate
FROM IC_ItemID
 INNER JOIN IC_Itembarcode ON IC_ItemID.itemID=IC_Itembarcode.itemID
 INNER JOIN IC_ItemDefaultPrices p ON  IC_ItemID.itemID= P.itemID 
WHERE P.STARTDATE = (Select Max(StartDate) from IC_ItemDefaultPrices As B 
 Where B.ItemID Like 'BUR333*' and b.startdate <= '2011-12-28')  and IC_ItemID.ItemID Like 'BUR333*'
GROUP BY IC_Itembarcode.itemID, IC_Itembarcode.Barcode, IC_Itembarcode.Serial1, IC_Itembarcode.Serial2, IC_Itembarcode.Serial3, IC_Itembarcode.Serial4, IC_Itembarcode.SerialCombination, IC_ItemID.L1Description, IC_ItemID.L2Description, IC_ItemID.ExtraL1Description, IC_ItemID.ExtraL2Description;

when i run why it is asking startdate as a parameter it need to
ask ItemID .Because date needs to come from ic_ItemDefaultprices.
Because already inner query returns max date from ic_itemdefaultprices .

SELECT IC_Itembarcode.itemID, IC_Itembarcode.Barcode, IC_Itembarcode.Serial1, IC_Itembarcode.Serial2, IC_Itembarcode.Serial3, IC_Itembarcode.Serial4, IC_Itembarcode.SerialCombination, IC_ItemID.L1Description, IC_ItemID.L2Description, IC_ItemID.ExtraL1Description, IC_ItemID.ExtraL2Description, MIN(Price), Max(STARTDATE) AS sdate
FROM IC_ItemDefaultPrices INNER JOIN (IC_ItemID INNER JOIN IC_Itembarcode ON IC_ItemID.itemID=IC_Itembarcode.itemID) ON IC_ItemDefaultPrices.itemID=IC_ItemID.itemID
WHERE P.STARTDATE = (Select Max(StartDate) from IC_ItemDefaultPrices As B   Where B.ItemID Like 'BUR333*' and b.startdate <= '2011-12-28')  and IC_ItemID.ItemID Like 'BUR333*'
GROUP BY IC_Itembarcode.itemID, IC_Itembarcode.Barcode, IC_Itembarcode.Serial1, IC_Itembarcode.Serial2, IC_Itembarcode.Serial3, IC_Itembarcode.Serial4, IC_Itembarcode.SerialCombination, IC_ItemID.L1Description, IC_ItemID.L2Description, IC_ItemID.ExtraL1Description, IC_ItemID.ExtraL2Description;

 

[Galaxiom]

The join structure looks like this.

SELECT fieldname
FROM
     (
      TableA AS A 
      INNER JOIN 
          (
            TableB AS B
            INNER JOIN TableC AS C
            ON B.field = C.field AND B.Another = C.Another
          )
      ON A.field = B.field
     )
WHERE whatever
 
When I do complex queries I write them like that in Notepad and indent subclauses with special attention to matching subclause parentheses on single lines. It really helps with the reading.

 

[firoz.raj]

Now i think i need to check part by part of this big query .Why the following query does not return anything ?.it Needs to Return the 12/29/2011 from Ic_ItemDefaultprices.

SELECT Max(StartDate)
FROM IC_ItemDefaultPrices AS B
WHERE B.itemID Like 'BUR33* ' And B.startdate<='2011-12-29'

 

[Galaxiom]

B.startdate<=#12/29/2011#
Or even
B.startdate<=#20111229#

Access will accept either order of date parts but not dd/mm/yyyy (well not in any intelligent way). The hash delimiters are essential. Other types of SQL including MS SQL Server use the single quotes.

 

[firoz.raj]

Now i am able to save at least whole query . but why it says Circular reference by alias 'Price' in query definition's select list ? .Kindly let me know please .any help would be greatly appreciated .

SELECT IC_Itembarcode.itemID, IC_Itembarcode.Barcode, IC_Itembarcode.Serial1, IC_Itembarcode.Serial2, IC_Itembarcode.Serial3, IC_Itembarcode.Serial4, IC_Itembarcode.SerialCombination, IC_ItemID.L1Description, IC_ItemID.L2Description, IC_ItemID.ExtraL1Description, IC_ItemID.ExtraL2Description, MIN(Price) AS Price, Max(StartDate) AS StartDate
FROM IC_ItemDefaultPrices
INNER JOIN (IC_ItemID INNER JOIN IC_Itembarcode ON IC_ItemID.itemID=IC_Itembarcode.itemID) ON IC_ItemDefaultPrices.itemID=IC_ItemID.itemID
WHERE P.STARTDATE =  (SELECT Max(StartDate) FROM IC_ItemDefaultPrices AS B WHERE B.ItemID Like 'BUR*' And B.startdate<=#12/29/2011# )  and IC_ItemID.ItemID  Like 'BUR333*'
GROUP BY IC_Itembarcode.itemID, IC_Itembarcode.Barcode, IC_Itembarcode.Serial1, IC_Itembarcode.Serial2, IC_Itembarcode.Serial3, IC_Itembarcode.Serial4, IC_Itembarcode.SerialCombination, IC_ItemID.L1Description, IC_ItemID.L2Description, IC_ItemID.ExtraL1Description, IC_ItemID.ExtraL2Description;

 

[Galaxiom]

The alias cannot have the same as the source field in the aggregates.

Change:
MIN(Price) AS Price

to something like:
MIN(Price) AS PriceMin

Similarly startdate