Transform Query in Access

[kiss.shoremishra]

Hi I am trying to concatenate 3 numeric fields in a table Item Prefix, Item Suffix and WSI (Item Created), so that I could get the year wise count of concatenated values but I am getting "Syntax error in transform statement".

Could you please help, below is the access query I wrote.

TRANSFORM COUNT([Tbl - Supplier-Item].[Item Prefix] & "-" & [Tbl - Supplier-Item].[Item Suffix] & "-" & [Tbl - Supplier-Item].[WSI #]) AS SI
SELECT Format([Tbl - Supplier-Item].[SI Added],"yyyy") AS [YEAR ADDED]
FROM [Tbl - Supplier-Item]
GROUP BY Format([Tbl - Supplier-Item].[SI Added],"yyyy");

 

[theDBguy]

I think you’re missing the PIVOT ON clause.

 

[kiss.shoremishra]

I agree but I have only 2 column and I don't need Pivot. Looking to get it without Pivot.

 

[theDBguy]

Hi. I think the proper syntax requires the PIVOT, so I don't think you can do it without the PIVOT. If you only have two columns, you could try pivoting on the column you're transforming.

 

[isladogs]

Or change to an aggregate query.
GROUP BY Year and COUNT your required field(s).

 

[kiss.shoremishra]

I got it, I am able to get it without Transform statement.


SELECT tblSupplierItem.YearAdded, count(tblSupplierItem.SI) AS SI_COUNT
FROM (SELECT DISTINCT [Tbl - Supplier-Item].[Item Prefix] & "-" & [Tbl - Supplier-Item].[Item Suffix] & "-" & [Tbl - Supplier-Item].[WSI #] AS SI, Format([SI Added],"yyyy") AS YearAdded
FROM [Tbl - Supplier-Item]) AS tblSupplierItem
GROUP BY tblSupplierItem.YearAdded
ORDER BY tblSupplierItem.YearAdded DESC;

 

[kiss.shoremishra]

Here is another task that I am working on

I am trying to create a new field using 3 numeric fields and use the same in Transform statement.

Below Query gives me the SI Count year wise

SELECT tblSupplierItem.YearAdded, count(tblSupplierItem.SI) AS SI_COUNT
FROM (SELECT DISTINCT [Tbl - Supplier-Item].[Item Prefix] & "-" & [Tbl - Supplier-Item].[Item Suffix] & "-" & [Tbl - Supplier-Item].[WSI #] AS SI, Format([SI Added],"yyyy") AS YearAdded
FROM [Tbl - Supplier-Item]) AS tblSupplierItem
GROUP BY tblSupplierItem.YearAdded
ORDER BY tblSupplierItem.YearAdded DESC;

but I want to add Region/Area etc and get the SI_COUNT by YEAR and BY AREA also, different Years being Row header & Region/Area being Column Header.

Tried this query below

TRANSFORM [Tbl - Supplier-Item].[Item Prefix] & "-" & [Tbl - Supplier-Item].[Item Suffix] & "-" & [Tbl - Supplier-Item].[WSI #] AS SI
SELECT Format([Tbl - Supplier-Item].[SI Added], "yyyy") AS YEAR_ADDED
FROM [Tbl - WRIN] INNER JOIN [Tbl - Supplier-Item] ON ([Tbl - WRIN].[Item Suffix] = [Tbl - Supplier-Item].[Item Suffix]) AND ([Tbl - WRIN].[Item Prefix] = [Tbl - Supplier-Item].[Item Prefix])
GROUP BY Format([Tbl - Supplier-Item].[SI Added], "yyyy")
PIVOT [Tbl - WRIN].[Where Used];

but getting error "Your query does not include the specified expression [Tbl - Supplier-Item].[Item Prefix] & "-" & [Tbl - Supplier-Item].[Item Suffix] & "-" & [Tbl - Supplier-Item].[WSI #] as part of an aggregate function.

 

[isladogs]

Can you post a cut down version of your db with the relevant table(s) & queries together with some sample data

Do you really have 2 separate tables called tblSupplierItem & Tbl - Supplier-Item?
If so, that way madness lies!

 

[June7]

Are you using query designer to build CROSSTAB? The field in TRANSFORM needs to be in an aggregate function. The concatenation of those 3 fields is probably unnecessary. Try just Count(*).

TRANSFORM Count([Tbl - Supplier-Item].[Item Prefix] & "-" & [Tbl - Supplier-Item].[Item Suffix] & "-" & [Tbl - Supplier-Item].[WSI #]) AS SI
SELECT Year([Tbl - Supplier-Item].[SI Added]) AS YEAR_ADDED
FROM [Tbl - WRIN] INNER JOIN [Tbl - Supplier-Item] ON ([Tbl - WRIN].[Item Suffix] = [Tbl - Supplier-Item].[Item Suffix]) AND ([Tbl - WRIN].[Item Prefix] = [Tbl - Supplier-Item].[Item Prefix])
GROUP BY Year([Tbl - Supplier-Item].[SI Added])
PIVOT [Tbl - WRIN].[Where Used];

 

[kiss.shoremishra]

@isladogs

No I don't have 2 separate tables called tblSupplierItem & Tbl - Supplier-Item.
tblSupplierItem is an alias name for Tbl - Supplier-Item & other table is [Tbl - WRIN].

@June7
Yes, I am using both "query designer" and SQL mode to create CROSSTAB query.
Without the concatenation, I am not going to get Supplier Item which is nothing but association of 3 numeric fields to generate a unique value

 

[isladogs]

There is no need to alias the table unless you are using it twice in a query.
If you do alias the idea is to make the alias simple.
Adding hyphens and spaces to table names or alieses makes it harder to read and work with the objects.

Not sure from your answer whether you now have a solution.
If not, please post something for one of us to look at

 

[kiss.shoremishra]

The below query worked.

transform count(format([Tbl - Supplier-Item].[SI Added],"yyyy"))
SELECT format([Tbl - Supplier-Item].[SI Added],"yyyy") as YearAdded
FROM [Tbl - WRIN] INNER JOIN [Tbl - Supplier-Item] ON ([Tbl - WRIN].[Item Suffix] = [Tbl - Supplier-Item].[Item Suffix]) AND ([Tbl - WRIN].[Item Prefix] = [Tbl - Supplier-Item].[Item Prefix])
group by format([Tbl - Supplier-Item].[SI Added],"yyyy")
pivot [Tbl - WRIN].[Where Used];

I just got rid of Supplier Item (SI) which is nothing but association of 3 numeric fields & every time such association is set up, there is a unique date added (SI Added), AS I needed only count, I used [SI Added] to get the count but numbers were not matching & hence analysed the reason and cause, then changed inner join to RIGHT OUTER JOIN. It worked. Thank you all.

Help me on another post where I am working with 3 tables with predefined relationship. will soon post that as separate post. Thanks again and again to all.