serialize or rank individual values in a query

[DaveMSAccess]

Hi, I'm looking for a relatively simple way to serialize values in a query. My data has 3 key fields, Warehouse, Item and Code Date. I would lie to label or rank the Code Date within each Item and Warehouse as 1,2,3,,,, I am not familiar with VB or SQL, would like to use standard functions if possible. In my mind, the data would look like this:

WAREHOUSE	ITEM	CODE DATE	RANK	SumOfLYQTY	FirstOfCurrent Date	Expr1
1	01500	20190325	1	1254	20181218	-1
1	01500	20190401	2	3328	20181218	-1
1	01500	20190408	3	3520	20181218	-1
2	01500	20190325	1	1969	20181218	-1
2	01500	20190401	2	4032	20181218	-1
2	01500	20190408	3	3264	20181218	-1

[orange]

It appears that your sample data is ordered by WarehouseID and CodeDate.
You could use an Order By clause in your Query.

If you are using the query grid, then these fields would be in the Sort row of the grid.

[isladogs]

You will need to do an aggregate query where you group by warehouse and sort by Code Date
If you need to create a Rank order field then you need an additional step

Rank order can easily be done in reports but needs a bit more work in queries

Have a look at the Rank Order in Queries page on my website http://www.mendipdatasystems.co.uk/r...ies/4594424063
It includes the use of a Serialize function and several examples

Another approach involves the use of subqueries - Allen Browne explains this in some detail here: http://allenbrowne.com/ranking.html

[DaveMSAccess]

It appears that your sample data is ordered by WarehouseID and CodeDate.
You could use an Order By clause in your Query.

If you are using the query grid, then these fields would be in the Sort row of the grid.

Yes, but that doesn't address the Rank or numbering of the records. I need that to create a cross-tab that isn't 100 fields wide. I got a link from another member, trying that.

[DaveMSAccess]

Yes, your post gave me all of the info I need.

[isladogs]

Excellent. Which method did you decide to use for your solution?

[DaveMSAccess]

Excellent. Which method did you decide to use for your solution?

Still working on it. How can I reference other records in a query (the previous record)? If I can't do that, I may need to 'kluge' it.

[isladogs]

Once again using Serialize or using a subquery.
See http://www.mendipdatasystems.co.uk/g...lue/4594484854 for the first approach with a link to the second.

If you have a lot of records and find those methods slow, you could write a function specifically for the purpose if necessary

[DaveMSAccess]

Once again using Serialize or using a subquery.
See http://www.mendipdatasystems.co.uk/g...lue/4594484854 for the first approach with a link to the second.

If you have a lot of records and find those methods slow, you could write a function specifically for the purpose if necessary

OK, I get it now, thanks.

[orange]

Dave can you post your solution. It might help someone else.