Cross Apply queries in Acces?

[SAugsburger]

I wrote a cross apply query in the SQL Management Studio that works how I want it in SSMS, but I can't seem to reproduce a similar query via an access query. The SQL query is below. How would I structure a similar functioned query in access.

SELECT TOP 100000
SUM(a.[QTY]) AS QTY,
a.[MFGPN],
c.[MANUFACTURER],
c.[DATECODE],
c.[DESCRIPTION],
c.[last_mod]
FROM [DEV].[dbo].[part] a
CROSS APPLY (SELECT TOP 1 * FROM [post].[dbo].[part] b WHERE a.[MFGPN] = b.[MFGPN]) c
GROUP BY a.[MFGPN],
c.[MANUFACTURER],
c.[DATECODE],
c.[DESCRIPTION],
c.last_mod

[June7]

What is a cross apply query? Show sample of source data and desired output.

[SAugsburger]

The sample SQL query is above. Here is an explanation of cross apply:

http://sqlserverplanet.com/sql-2005/cross-apply-explained

[June7]

I saw the SQL and found that information already. Didn't help me understand what you want. A sample of raw data and sample of desired output would be more helpful to clarify what you want to do.

[SAugsburger]

Here would be a test input

QTY,MFGPN,MANUFACTURER,DATECODE,DESCRIPTION
5,Foo,foo,09,test
6,foo,abc,10,test
10,foo2,ooo,09,z
2,foo,zyx,,zy

Output should obviously sum the QTY field and select the first field


QTY,MFGPN,MANUFACTURER,DATECODE,DESCRIPTION
13,Foo,foo,09,test
10,foo2,ooo,09,z

[June7]

This appears to aggregate data by the MFGPN field but shows the MANUFACTURER, DATECODE, DESCRIPTION from the first record of each MFGPN group. Why the first record? How is showing details of one record with group aggregate data meaningful?

Will there be a need to filter the records?

This might get you what you want. However, First and Last aggregate functions can produce unexpected results. Can't always count on the record you think is 'first' or 'last' in table or query to actually be the first or last record.

SELECT Query2.SumOfQTY, Query2.MFGPN, Query1.FirstOfMANUFACTURER, Query1.FirstOfDATECODE, Query1.FirstOfDESCRIPTION
FROM (SELECT Table1.MFGPN, First(Table1.MANUFACTURER) AS FirstOfMANUFACTURER, First(Table1.DATECODE) AS FirstOfDATECODE, First(Table1.DESCRIPTION) AS FirstOfDESCRIPTION FROM Table1 GROUP BY Table1.MFGPN) As Query1 INNER JOIN (SELECT Table1.MFGPN, Sum(Table1.QTY) AS SumOfQTY
FROM Table1 GROUP BY Table1.MFGPN) AS Query2 ON Query1.MFGPN = Query2.MFGPN;