Solved filter results in a query

[109bow]

Good evening all, being a very novice user of Access I'm hoping for some help.
I have a query, forcast_planning, which contains multiple rows of data for each number, 01 to 94, in column shortno.
What I need is a query that shows two rows for each shortno, 01 to 94, one where Cartype is Motor and another row for Cartype Trailer, where MaxofIDnumber is the lowest for each Motor and Trailer.
MaxofIDnumber is a unique number for every row.
For example for shortno 01 Motor, it would show row 12, for a trailer it would show row 16. This would be repeated for all 94 numbers in shortno, giving a total of 188 rows. I hoping with this new query I can create a cluster bar chart.

Any help or guidance very much appreciated

 

[plog]

Lots of questions and notes.

1. How far from tables are we? Building this query this way seems like building on an unstable foundation. You need a new query built on forcast_planning, which itself is built on forcast4, which is probably built on forcast3 which is probably built on etc etc etc. How far away from actual tables with this query are we? Its usually best to build as close to tables as possible and not a Rube Goldberg query machine.

... where MaxofIDnumber is the lowest for each Motor and Trailer.

2. This is what I mean. Somewhere along the line you used a GROUP BY with your data and got MaxOfIDnumber. But why? In what context is that the MaxOfIDnumber. And is that what you really want? Often times, people incorrectly use an ID to Max on when in fact they should be using a date. ID numbers usually don't guarantee a specific order and shouldn't be counted on for that.

... giving a total of 188 rows.

3. Are you sure? People make so many wrong assumptions about their data. Does every shortno have a Motor and a Trailer record? What would you like to occur if they don't?

With all that said, you need a subquery:

SELECT shortno, carttype, MIN(MaxOfIDnumber) AS LowestID 
FROM forcast_planning
WHERE carttype = 'Motor' OR carttype='Trailer'
GROUP BY shortno, carttype

You then take that query and build another query using it and forcast_planning. Link them by LowestID to MaxOfIDnumber. Bring in all the fields from forcast_planning and no records from the above query (it is only used as a filter) and that will give you your records. Again, though, I seriously question if this is the best way to achieve what you want.

 

[jdraw]

I created a small table
ForecastT

shortNo	MaxOfIDnumber	CarType
01	9999999	Motor
01	9999998	Motor
01	9999997	Motor
01	9999996	Motor
01	9999994	Trailer
01	9999992	Trailer
03	9999991	motor
03	9999988	motor
03	9999987	motor
03	9999984	Trailer
03	9999982	Trailer

and used plog's sql name of carType adjusted
SQL as Query64

SELECT shortno, cartype, MIN(MaxOfIDnumber) AS LowestID
FROM ForecastT
WHERE cartype = 'Motor' OR cartype='Trailer'
GROUP BY shortno, cartype

with result

shortno	cartype	LowestID
01	Motor	9999996
01	Trailer	9999992
03	motor	9999987
03	Trailer	9999982

 

[109bow]

Lots of questions and notes.

1. How far from tables are we? Building this query this way seems like building on an unstable foundation. You need a new query built on forcast_planning, which itself is built on forcast4, which is probably built on forcast3 which is probably built on etc etc etc. How far away from actual tables with this query are we? Its usually best to build as close to tables as possible and not a Rube Goldberg query machine.



2. This is what I mean. Somewhere along the line you used a GROUP BY with your data and got MaxOfIDnumber. But why? In what context is that the MaxOfIDnumber. And is that what you really want? Often times, people incorrectly use an ID to Max on when in fact they should be using a date. ID numbers usually don't guarantee a specific order and shouldn't be counted on for that.



3. Are you sure? People make so many wrong assumptions about their data. Does every shortno have a Motor and a Trailer record? What would you like to occur if they don't?

With all that said, you need a subquery:

SELECT shortno, carttype, MIN(MaxOfIDnumber) AS LowestID
FROM forcast_planning
WHERE carttype = 'Motor' OR carttype='Trailer'
GROUP BY shortno, carttype

You then take that query and build another query using it and forcast_planning. Link them by LowestID to MaxOfIDnumber. Bring in all the fields from forcast_planning and no records from the above query (it is only used as a filter) and that will give you your records. Again, though, I seriously question if this is the best way to achieve what you want.

plog, many thanks for taking the time to reply.
I will try and answer your questions as bet I can,
1, I am four queries away from the main data table. My database has evolved over time meaning it is by far from being the most streamlined database, but for now it works.

2, MaxofIDnumber was created as it was needed to generate a report. Its definately the number I need to use, the original IDnumber is generated when a row is added to the table. This number can be relied upon. Dates wouldn't work in this instance as I have the same date for up to 16 rows of the same shortno.

3, Definitely, every shortno has an entry for Motor and Trailer.

I have repeated the steps you gave and have got the results I was looking for. Hopefully I can create a chart from this data.
Many thanks