Nested Query in Ms Access (1 Viewer)

JCRamirez

New member
Local time
Today, 14:21
Joined
Sep 15, 2020
Messages
14
I have a table that shows the net cost and selling cost depending on the weight. The Slab of the rates are from Min, -45, +45, 100, 300, 500, 1000, Pivot

I created 8 queries to filter a different Slab of the table for example the query of Min is as below

Code:
RateAirlineSlabTable.RateAirlineid, RateAirlineSlabTable.RateSlab, RateAirlineSlabTable.NetRate, RateAirlineSlabTable.SellingRate FROM RateAirlineSlabTable WHERE (((RateAirlineSlabTable.RateSlab)="Min"));

Then to be able to show the slab in different columns I created a query that depends on this other 8 queries as below

Code:
SELECT RateAirlineTable.RateAirlineID, RateMinQuery.NetRate AS MinNR, RateMinQuery.SellingRate AS MinSR, [Rate-45Query].NetRate AS [-45NR], [Rate-45Query].SellingRate AS [-45SR], [Rate+45Query].NetRate AS [+45NR], [Rate+45Query].SellingRate AS [+45SR], [Rate+100Query].NetRate AS [+100NR], [Rate+100Query].SellingRate AS [+100SR], [Rate+300Query].NetRate AS [+300], [Rate+300Query].SellingRate AS [+300SR], [Rate+500Query].NetRate AS [+500NR], [Rate+500Query].SellingRate AS [+500SR], [Rate+1000Query].NetRate AS [+1000NR], [Rate+1000Query].SellingRate AS [+1000SR], RatePivotQuery.RateSlab AS [Pivot], RatePivotQuery.NetRate AS PivotNR, RatePivotQuery.SellingRate AS PivotSR FROM RatePivotQuery RIGHT JOIN ([Rate+1000Query] RIGHT JOIN ([Rate+500Query] RIGHT JOIN ([Rate+300Query] RIGHT JOIN ([Rate+100Query] RIGHT JOIN ([Rate+45Query] RIGHT JOIN ([Rate-45Query] RIGHT JOIN (RateMinQuery RIGHT JOIN RateAirlineTable ON RateMinQuery.RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate-45Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate+45Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate+100Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate+300Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate+500Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON [Rate+1000Query].RateAirlineid = RateAirlineTable.RateAirlineID) ON RatePivotQuery.RateAirlineid = RateAirlineTable.RateAirlineID;

I have being trying to replace the 8 queries in this Main Query, but I cannot make it happen.

Can someone advise me how to do it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:21
Joined
May 7, 2009
Messages
19,169
maybe create a Total query from RateAirlineSlabTable table.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:21
Joined
May 21, 2018
Messages
8,463
I use one of the many free online SQL formatters to make it easier to read.
SQL:
SELECT rateairlinetable.rateairlineid,
       rateminquery.netrate         AS MinNR,
       rateminquery.sellingrate     AS MinSR,
       [rate-45query].netrate       AS [-45NR],
       [rate-45query].sellingrate   AS [-45SR],
       [rate+45query].netrate       AS [+45NR],
       [rate+45query].sellingrate   AS [+45SR],
       [rate+100query].netrate      AS [+100NR],
       [rate+100query].sellingrate  AS [+100SR],
       [rate+300query].netrate      AS [+300],
       [rate+300query].sellingrate  AS [+300SR],
       [rate+500query].netrate      AS [+500NR],
       [rate+500query].sellingrate  AS [+500SR],
       [rate+1000query].netrate     AS [+1000NR],
       [rate+1000query].sellingrate AS [+1000SR],
       ratepivotquery.rateslab      AS [Pivot],
       ratepivotquery.netrate       AS PivotNR,
       ratepivotquery.sellingrate   AS PivotSR
FROM   ratepivotquery
       RIGHT JOIN ([rate+1000query]
                   RIGHT JOIN ([rate+500query]
                               RIGHT JOIN ([rate+300query]
                                           RIGHT JOIN ([rate+100query]
                                                       RIGHT JOIN (
                                                       [rate+45query]
                                                       RIGHT JOIN (
                                                       [rate-45query]
                                                       RIGHT JOIN (
                                                       rateminquery
                                           RIGHT JOIN rateairlinetable
                                                   ON
                                           rateminquery.rateairlineid =
                                           rateairlinetable.rateairlineid)
                                                   ON
                                           [rate-45query].rateairlineid =
                                           rateairlinetable.rateairlineid)
                                                   ON
                                           [rate+45query].rateairlineid =
                                           rateairlinetable.rateairlineid)
                                                   ON
                                           [rate+100query].rateairlineid =
                                           rateairlinetable.rateairlineid)
                                                   ON
                                           [rate+300query].rateairlineid =
                                           rateairlinetable.rateairlineid)
                                       ON [rate+500query].rateairlineid =
                                          rateairlinetable.rateairlineid)
                           ON [rate+1000query].rateairlineid =
                              rateairlinetable.rateairlineid)
               ON ratepivotquery.rateairlineid = rateairlinetable.rateairlineid
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:21
Joined
May 21, 2018
Messages
8,463
Any chance you could provide a small set of sample data? I am thinking you might be able to do this with two Crosstabs.
Rows would be the rateID, columns would be your rates.
In the first crosstab you would put your NetRate. The Second would be your SellingRate. You will have to alias your columns in the properties. Then link these two queries.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:21
Joined
May 21, 2018
Messages
8,463
You might also be able to do an aggregate query like @arnelgp suggested. However, that would be in row not column format. You may be able to then do a crosstab on that.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:21
Joined
Feb 19, 2002
Messages
42,981
Using special characters and embedded spaces is poor practice in a relational database. It is a symptom of Excel where the interface and the data are combined. In a relational database, the user NEVER sees the actual tables and unless you are exporting data, NEVER sees queries either. Therefor, you can normalize the tables and use well formed names and the forms/reports use pretty layouts and pretty captions for the name fields.
 

JCRamirez

New member
Local time
Today, 14:21
Joined
Sep 15, 2020
Messages
14
maybe create a Total query from RateAirlineSlabTable table.
Dear Arnelgp I check the cross tables and total queries but it is not convenient because I have the Pivot column. This range is not always the same. The value will change every time. For a better understanding of what is my request you can download a sample of my database
maybe create a Total query from RateAirlineSlabTable table.
 

Attachments

  • Data-Base-Sample.accdb
    832 KB · Views: 262

JCRamirez

New member
Local time
Today, 14:21
Joined
Sep 15, 2020
Messages
14
Any chance you could provide a small set of sample data? I am thinking you might be able to do this with two Crosstabs.
Rows would be the rateID, columns would be your rates.
In the first crosstab you would put your NetRate. The Second would be your SellingRate. You will have to alias your columns in the properties. Then link these two queries.
MajP, I though about a cross table but as my understanding will not work. See the sample file that I attach I'm sure will give you a clear picture of what I intent to do
 

Attachments

  • Data-Base-Sample.accdb
    832 KB · Views: 187

JCRamirez

New member
Local time
Today, 14:21
Joined
Sep 15, 2020
Messages
14
Using special characters and embedded spaces is poor practice in a relational database. It is a symptom of Excel where the interface and the data are combined. In a relational database, the user NEVER sees the actual tables and unless you are exporting data, NEVER sees queries either. Therefor, you can normalize the tables and use well formed names and the forms/reports use pretty layouts and pretty captions for the name fields
 

JCRamirez

New member
Local time
Today, 14:21
Joined
Sep 15, 2020
Messages
14
You are right. This Database is old and I'm trying to improve it in many aspects, including the suggestions that you made
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:21
Joined
May 21, 2018
Messages
8,463
Something like this? Using crosstabs.

qryAirIntegration qryAirIntegration

RateAirlineidMin_NRMin_SR-45_NR-45_SR+45_NR+45_SR+100_NR+100_SR+300_NR+300_SR+500_NR+500_SR+1000_NR+1000_SRRateSlabNetRateSellingRate
131​
545​
650​
133​
870​
970​
134​
6500
900​
1000​
135​
200​
300​
136​
545​
600​
138​
600​
650​
140​
475​
530​
141​
850​
950​
144​
1000​
1100​
146​
495​
600​
147​
1859​
1960​
148​
1400
980​
1080​

I do not know if this saves much work. This took 2 crosstabs, the "pivot" query, and then a select query. You could do this with a union, xtab, and the "Pivot", and the select. Again not saving a whole bunch of work.

If there was a lot more columns then this crosstab approach would be much easier then writing the individual queries, but it still took me 4 queries.
 

Attachments

  • Data-Base-Sample v2.accdb
    1.5 MB · Views: 421
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:21
Joined
May 7, 2009
Messages
19,169
see Query1.
 

Attachments

  • Data-Base-Sample.accdb
    1.2 MB · Views: 297

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:21
Joined
May 21, 2018
Messages
8,463
@JCRamirez,
Although the solutions presented are shorter, I bet your original solution is the most efficient, and fastest. On large data sets it may be noticeable.
 

JCRamirez

New member
Local time
Today, 14:21
Joined
Sep 15, 2020
Messages
14
Something like this? Using crosstabs.

qryAirIntegration qryAirIntegration

RateAirlineidMin_NRMin_SR-45_NR-45_SR+45_NR+45_SR+100_NR+100_SR+300_NR+300_SR+500_NR+500_SR+1000_NR+1000_SRRateSlabNetRateSellingRate
131​
545​
650​
133​
870​
970​
134​
6500
900​
1000​
135​
200​
300​
136​
545​
600​
138​
600​
650​
140​
475​
530​
141​
850​
950​
144​
1000​
1100​
146​
495​
600​
147​
1859​
1960​
148​
1400
980​
1080​

I do not know if this saves much work. This took 2 crosstabs, the "pivot" query, and then a select query. You could do this with a union, xtab, and the "Pivot", and the select. Again not saving a whole bunch of work.

If there was a lot more columns then this crosstab approach would be much easier then writing the individual queries, but it still took me 4 queries.
MajP thanks for your proposal, but the problem is that all the other slab that are not Min, -45 +45, +100, +300, +500, +1000 should be in the same column
 

JCRamirez

New member
Local time
Today, 14:21
Joined
Sep 15, 2020
Messages
14
@JCRamirez,
Although the solutions presented are shorter, I bet your original solution is the most efficient, and fastest. On large data sets it may be noticeable.
Majp, the proposal @arnelgp it is good. Now my issue is that I want to be able to modify the value.
For this case I think to use the query from @arnelgp and create a new table and from there use it as a base of my record source
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:21
Joined
May 7, 2009
Messages
19,169
why do you need to create another table?
you already have a "normalized" table, RateAirlineSlabTable?
 

JCRamirez

New member
Local time
Today, 14:21
Joined
Sep 15, 2020
Messages
14
why do you need to create another table?
you already have a "normalized" table, RateAirlineSlabTable?
Because I need to be able to update the information. I want to use the query to use as a record source in a form. The form need to show all the slabs and I need to be able to edit the information or to add a new rates.

So far the customer wants to use a similar form like the one it is shown in the picture
 

Attachments

  • Screen Shot 2021-08-24 at 20.28.15.png
    Screen Shot 2021-08-24 at 20.28.15.png
    260 KB · Views: 311

Users who are viewing this thread

Top Bottom