Solved filter results in a query

109bow

Registered User.
Local time
Today, 06:36
Joined
Oct 24, 2007
Messages
138
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
query.jpg


1742678040743.png
 
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:

Code:
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.
 
I created a small table
ForecastT
shortNoMaxOfIDnumberCarType
019999999Motor
019999998Motor
019999997Motor
019999996Motor
019999994Trailer
019999992Trailer
039999991motor
039999988motor
039999987motor
039999984Trailer
039999982Trailer



and used plog's sql name of carType adjusted
SQL as Query64
Code:
SELECT shortno, cartype, MIN(MaxOfIDnumber) AS LowestID
FROM ForecastT
WHERE cartype = 'Motor' OR cartype='Trailer'
GROUP BY shortno, cartype

with result
shortnocartypeLowestID
01Motor9999996
01Trailer9999992
03motor9999987
03Trailer9999982
 
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:

Code:
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
 

Attachments

  • filtered results.jpg
    filtered results.jpg
    782 KB · Views: 13

Users who are viewing this thread

Back
Top Bottom