Could one Query answer any query Questions?

[johnseito]

Or do you think that there are query questions that is not possible, and that you have to create a query from the tables, saved it, then


created another query referencing the saved query just to answer your questions and accomplish your
goals?

I feel that there are time where I can't do it in one query and I have to saved it (a query that I build) then
create another query and referencing the saved one.

Is this some of your experience as well or you could always do it in one query?

Thanks!!!

[ItsMe]

You can bind a form to a query, navigate around within the recordset, and then ask questions about the recordset or other relative data

[johnseito]

You can bind a form to a query,

Then that would mean you have to create a query and saved it then bind it to the form, so that means you can't just do it
by a query alone?

I never really did what you just said so I wouldn't really know what you meant.

[ItsMe]

You don't have to. You could create a query within a query too. I guess there is more than one way to go about it, is what I am saying. If you create one query, then you create another, in the second query you can add the first query to it. You can look at the SQL to see what that looks like.

[johnseito]

You could create a query within a query too.

Ok, I tried to create a subquery within a query but that doesn't do it, because the sub-query that I created needs to be join by itself repeatedly.
For example, I created a query:

 
SELECT DISTINCT bookingLeg.bookinglegID, bookingLeg.tid, station.stationName AS station1, lineStation.distance_miles AS distance1, dateadd("n",                                        lineStation.time_mins, trains.depart) AS time1, station_1.stationName AS station2, lineStation_1.distance_miles AS distance2, dateadd("n",                          lineStation_1.time_mins, trains.depart) AS time2, lineStation_1.distance_miles - lineStation.distance_miles AS Diff
FROM trains INNER JOIN (lineStation AS lineStation_1 INNER JOIN (lineStation INNER JOIN ((bookingLeg INNER JOIN station ON bookingLeg.startID =                                        station.stationID) INNER JOIN station AS station_1 ON station_1.stationID = bookingLeg.endID) ON linestation.stationID =                                                station.stationID) ON lineStation_1.stationID = station_1.stationID) ON trains.trainID = bookingleg.tid
WHERE lineStation_1.distance_miles - lineStation.distance_miles >=0;

I called this query nyTOboston, and then I want to join this query to itself but I can't do it in one query so I saved this query, then
I created another query that would joing nyTOboston.bookingLegID to nyTOboston1.bookingLegID and because of this, there is going to be
a many to many relationship because nyTOboston (a query) doesn't have primary and foreign key.

[ItsMe]

I didn't look at the SQL but if there is not a way to make the join, you will have to get the answer by asking it when the time arises. That is why I mentioned the form thing. You can crate a more dynamic subquery so to speak. Also, you can use other techniques like subforms, embedded reports, list boxes, DAO, etc.

[ssanfu]

I feel that there are time where I can't do it in one query and I have to saved it (a query that I build) then
create another query and referencing the saved one.

I frequently use two queries to get the required dataset for my reports.

You could use a query/subquery; I mostly use the two query route because
1) I still struggle a little (ok, a lot) getting the syntax correct and
2) I can see if the results of the first query are what I need/expect. The second query can then total the records or use the first query and another table to further limit the records

[naeemahmad]

To answer your question quickly, YES.. it is common to have multiple (nested) queries to accomplish tasks. there are other options, but they can get very elaborate very quickly.
I have tried exactly what you speaking of, and the most efficient approach is to have nested queries, unless you really have alot of time to spend on researching an alternate approach, which will just get you same result.

Naeem~

[johnseito]

Thank you for the input, that was really helpful.

Something seems to be a little bit puzzling to me. I just want to see if you guys know why.

So as with what I am doing, I created a query and called this query nyTOboston.
I then create another query that called nyTOboston query, and then called it again, so now I have two tables.
One is nyTOboston and another one is called nyTOboston1, I joined the two query by it's bookingLegID field (not a primary key)
and SELECT bookingLegID, nyTOboston.distance2, nyTOboston.distance1, nyTOboston_1.distance2, nyTOboston_1.distance1

What I don't get is, how come nyTOboston_1distance1 doesn't line up with nyTOboston.distance1?

this is the sample data from the first nyTOboston query.


This is the result of the join.


As you could see nyTOboston.distance1 does not exactly lineup with nyTOboston_1.distance1, do you know why that is?
Also since this is a query referencing another query, and joined from another query, it doesn't have any primary and foreign key association
and so the joined created a lot of duplicated data. Think I should created a junction table for this?

[ssanfu]

It looks like you are getting the results of a Cartesian join.

Your table has 5 records. If you look at the column "NY to Boston.distance1", there are 5 13s; there are 5 14s, 10 15s and 5 16s.
Same for "NY to Boston_1.distance1"; same counts.

Not sure what you are trying to do, but Google "Cartesian join".

[johnseito]

Thanks for pointing out about a Cartesian join, I learned something new every day.

The goal was to see the distance between each station, and there are a total of three stations a ticket could have.
So I would say nyTOboston represent the distance of the first station and nyTOboston_1 represent the distance of the second station.
and distance 2 and distance one represent connecting station from 1 to 2 etc.

But I was wondering how come nyTOboston.distance1, nyTOboston_1.distance1 are not in order,
for example the first record
nyTOboston.distance1 has 13 and nyTOboston_1.distance1 has 15,

Why the result of these two column isn't line up with each other?

[ssanfu]

Why the result of these two column isn't line up with each other?

I was curious about that also, but don't have the time to investigate further.

You didn't post the SQL of the query, so I made up my own table/queries.
Since it is a Cartesian join, I'm not sure with the data presented, that the last column can be sorted.

From your description, you used a table and a query to get the result set. A table can't be sorted... maybe the query was sorted and the result order is what it is.

Maybe with real data the results would be different....

[johnseito]

Since it is a Cartesian join, I'm not sure with the data presented, that the last column can be sorted.

If I sort one of the column, the other column will not be in order (they both can't be sort from least to greatest or matching pair
is what I meant). It looks like it is fixed that way (not line up together as a match) that no matter how you sort it, it will not be in line, the reason I came
to this conclusion is because if you have nyTOboston.distance2 minus nyTOboston.distance1 and nyTOboston_1.distance2 minus nyTOboston_1.distance
and sum the difference, the result will always be the same no matter how you sort it. This is what I think.

you used a table and a query to get the result set.

I use a query to get the result set, and I created the query base of from many tables. I think a table can be sorted for your viewing purpose,
but it will not affect how is join even if you sort it. So if you open a table and you sort it, you can see it's display in sorted orders.

I guess it is what it is? not sure why though..