Drill down query (1 Viewer)

tmyers

Well-known member
Local time
Today, 11:37
Joined
Sep 8, 2020
Messages
1,090
While fiddling with stuff I ran into a question.
How can you drill down a query to only show specific things?

To give an example, if I had a quote 12345-20-1, 12345-20-2, 12345-20-3 and 12345-20-4. All the same quote, but different revision numbers. How would I tell a query to only show me the quote with the highest revision number? So the query would only return 12345-20-4?

I do have a linked table that has that broken down into its 3 separate parts if that matters.
 

Isaac

Lifelong Learner
Local time
Today, 08:37
Joined
Mar 14, 2017
Messages
8,738
I would probably start by creating a query with JUST that linked table that has them broken down into separate parts.
Possibly group by the 12345 and 20 parts, and show the max(the 4 column) ? If I'm thinking of this correctly
 

plog

Banishment Pending
Local time
Today, 10:37
Joined
May 11, 2011
Messages
11,611
This is why you don't store the part number all together. Obviously its comprised of 3 pieces of information, so each should be in its own field. Let's call them A, B & C. To find the the one with 4 in the C field you would have this query:

Code:
SELECT A, B, MAX(C) AS LatestC
FROM YourTableNameHere
GROUP  BY A, B
 

tmyers

Well-known member
Local time
Today, 11:37
Joined
Sep 8, 2020
Messages
1,090
This is why you don't store the part number all together. Obviously its comprised of 3 pieces of information, so each should be in its own field. Let's call them A, B & C. To find the the one with 4 in the C field you would have this query:

Code:
SELECT A, B, MAX(C) AS LatestC
FROM YourTableNameHere
GROUP  BY A, B
Was I wrong to save the complete number in one spot, but link it to a table that breaks it down? Or did I convolute the whole thing?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:37
Joined
Oct 29, 2018
Messages
21,358
Was I wrong to save the complete number in one spot, but link it to a table that breaks it down? Or did I convolute the whole thing?
Did the other table with all the separate fields have a single field Primary Key?
 

tmyers

Well-known member
Local time
Today, 11:37
Joined
Sep 8, 2020
Messages
1,090
Did the other table with all the separate fields have a single field Primary Key?
It does.
Capture.PNG


Edit:
FullNumber isnt used. I haven't fully decided what to do with the completed number, if I should do anything. I currently have queries that when a job is created, creates all the parts in the generation table, then updates QuoteNum in job details with the completed number.
 

plog

Banishment Pending
Local time
Today, 10:37
Joined
May 11, 2011
Messages
11,611
You don't store calcualted values. Although, "Number1" + "Number2" + "Number3" isn't a mathematical calculation it is a calculation, so it shouldn't be stored. So there is no need for a [QuoteNum] field.

What are the 3 fields that comrpise [QuoteNum]?

Follow up, is [YearNum] based on another field? BidDate, CreatedDate or CompletedDate?
 

tmyers

Well-known member
Local time
Today, 11:37
Joined
Sep 8, 2020
Messages
1,090
You don't store calcualted values. Although, "Number1" + "Number2" + "Number3" isn't a mathematical calculation it is a calculation, so it shouldn't be stored. So there is no need for a [QuoteNum] field.

What are the 3 fields that comrpise [QuoteNum]?

Follow up, is [YearNum] based on another field? BidDate, CreatedDate or CompletedDate?
No. I could potentially derive the [YearNum] from bid date, but there could be times at the end of the year when the two wouldn't line up. It is currently derived from Right(Year(Date), 2).
RevisionNum is currently set to default of one, then I have code that increments it.
 

Isaac

Lifelong Learner
Local time
Today, 08:37
Joined
Mar 14, 2017
Messages
8,738
No. I could potentially derive the [YearNum] from bid date, but there could be times at the end of the year when the two wouldn't line up. It is currently derived from Right(Year(Date), 2).
RevisionNum is currently set to default of one, then I have code that increments it.
If you have the parts separate than wouldn't my original suggestion work?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:37
Joined
Oct 29, 2018
Messages
21,358
It does.
View attachment 87344

Edit:
FullNumber isnt used. I haven't fully decided what to do with the completed number, if I should do anything. I currently have queries that when a job is created, creates all the parts in the generation table, then updates QuoteNum in job details with the completed number.
Just curious... What is the purpose of the separate table? It seems you could just as easily have added those separate fields into the main table, since you have a 1-to-1 relationship anyway.
 

tmyers

Well-known member
Local time
Today, 11:37
Joined
Sep 8, 2020
Messages
1,090
Just curious... What is the purpose of the separate table? It seems you could just as easily have added those separate fields into the main table, since you have a 1-to-1 relationship anyway.
I didn't like how large that table was getting. I figured since this was its own unique thing, having a separate table wasn't too crazy to do.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:37
Joined
Oct 29, 2018
Messages
21,358
I didn't like how large that table was getting. I figured since this was its own unique thing, having a separate table wasn't too crazy to do.
Okay, thanks for the explanation. Splitting a large table into two would make sense if you divided the fields into two equal parts/tables. How much space would you have saved by moving a couple of fields out? Just a thought...
 

tmyers

Well-known member
Local time
Today, 11:37
Joined
Sep 8, 2020
Messages
1,090
Okay, thanks for the explanation. Splitting a large table into two would make sense if you divided the fields into two equal parts/tables. How much space would you have saved by moving a couple of fields out? Just a thought...
You make a good point. I could move the nitty gritty things out and divide it up a little bit.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Feb 19, 2002
Messages
42,970
I'm pretty sure that this isn't something you need to do with a query. Aren't you using combos to select the record you want. You could be using cascading combos. The first one is the main part of the quote number, the second is the rev number. Please explain the exactuation where you would need the query to return one and only one row and it should be the one with the highest rev number and you haven't already selected the base quote number?
 

tmyers

Well-known member
Local time
Today, 11:37
Joined
Sep 8, 2020
Messages
1,090
I'm pretty sure that this isn't something you need to do with a query. Aren't you using combos to select the record you want. You could be using cascading combos. The first one is the main part of the quote number, the second is the rev number. Please explain the exactuation where you would need the query to return one and only one row and it should be the one with the highest rev number and you haven't already selected the base quote number?
This was just something I thought of while looking at my home screen. On my home screen I have a subform that displays all jobs today onward and I noticed one of my test jobs was there twice because its quote number was different due to the revision number. This questions was mostly out of curiosity, but I have now realized I did the whole quote number thing in a wrong and convoluted manner.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Feb 19, 2002
Messages
42,970
I'm pretty sure I mentioned at some time during this conversation that you might want two tables for quote. The main table with the stuff that doesn't change and a child table for each rev to include the stuff that does change.

A proper table schema solves all problems.
 
Last edited:

tmyers

Well-known member
Local time
Today, 11:37
Joined
Sep 8, 2020
Messages
1,090
In making that separate table, I think I have come to realize that my main table had too many things in it that should have been in a separate table to begin with. Todays task is to get all the fields split off and do better.

I am currently consolidating all info that revolves around the quote number itself into the table and out of the other table. Such as revision name, created date etc. That will help slim down the other table.
 

tmyers

Well-known member
Local time
Today, 11:37
Joined
Sep 8, 2020
Messages
1,090
Think I am done with that mess.

I broke out roughly 5 fields from what I felt was my bloated "master" table, pushed those into the new table tblQuoteDetails (formally tblQuoteNumGeneration), removed any field that had that showed all the pieces of the number put together (my controls now have expressions to display the complete number but I now no longer store the complete number).

Now all details regarding the quote number itself is contained within the new table. The base number, year code, revision number, creation date, revision name etc. The master table now just contains things pertinent to the job. The projects name, bid date and financials etc.

Also, the query I had originally asked about was super easy to make now that all that data is broken into pieces.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Feb 19, 2002
Messages
42,970
Just splitting fields into a 1-1 table doesn't normalize anything. The 1-1 relationship is used primarily for two reasons.
1. Security. Some of the data is ultra secure and needs to be isolated from the rest and your RDBMS does not support column level security.
2. You have multiple entities such as students, teachers, administrators, vendors, guardians, etc. stored in a single table but each entity type has some unique attributes that the others don't have. So rather than have a couple dozen columns that are applicable to only one of the types, you use the entity table for the common attributes and all relationships but create a separate 1-1 child table for each type and stuff them there.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:37
Joined
Oct 29, 2018
Messages
21,358
Just splitting fields into a 1-1 table doesn't normalize anything. The 1-1 relationship is used primarily for two reasons.
1. Security. Some of the data is ultra secure and needs to be isolated from the rest and your RDBMS does not support column level security.
2. You have multiple entities such as students, teachers, administrators, vendors, guardians, etc. stored in a single table but each entity type has some unique attributes that the others don't have. So rather than have a couple dozen columns that are applicable to only one of the types, you use the entity table for the common attributes and all relationships but create a separate 1-1 child table for each type and stuff them there.
Just curious... Which reason (1 or 2) applies in this case with regards to splitting the Quote Number with Revisions in a separate 1-to-1 table?
 

Users who are viewing this thread

Top Bottom