Results 1 to 13 of 13
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    Could one Query answer any query Questions?

    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!!!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    You can bind a form to a query, navigate around within the recordset, and then ask questions about the recordset or other relative data

  3. #3
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    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.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    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.

  5. #5
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    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:

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

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    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.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by johnseito View Post
    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

  8. #8
    naeemahmad is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Location
    Riverside, USA
    Posts
    46
    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~

  9. #9
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    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.
    Click image for larger version. 

Name:	distance.png 
Views:	18 
Size:	2.2 KB 
ID:	14442

    This is the result of the join.
    Click image for larger version. 

Name:	result-11-20-13.png 
Views:	18 
Size:	16.6 KB 
ID:	14443

    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?

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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".

  11. #11
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    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?





  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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....

  13. #13
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    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..

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 06-25-2013, 12:52 PM
  2. Replies: 5
    Last Post: 06-18-2013, 12:53 PM
  3. Sum query returning incorrect answer
    By chawes40 in forum Queries
    Replies: 1
    Last Post: 12-11-2012, 03:52 PM
  4. Replies: 5
    Last Post: 04-24-2012, 10:26 AM
  5. Replies: 2
    Last Post: 11-17-2006, 01:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums