Results 1 to 9 of 9
  1. #1
    fbb is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    3

    New to access... Is this possible?

    New to databasing in general. Is it possible to do this?
    I've built the tables but no idea how to do the Query.

    Table 1:


    Code:
    ID |  Country  |  
    -------------------------------------------------
    1      UK        
    2      CANADA
    Table 2:
    Code:
    ID  |  Table1.ID  |  City        |  Priority
    --------------------------------------------
    1       1            London         1
    2       1            Dover          2
    3       1            Brighton       3
    4       2            Vancouver      1
    Query Result:
    Code:
    Country  |  City1  |  City2    |  City3  |
    UK        London   Dover   Brighton
    

    If anyone has any suggestions, it would be much appreciated.

    Thanks

  2. #2
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Response deleted by me.

    Edited: After further review, I have to agree with Micron. Use a crosstab query.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The query result doesn't look concatenated to me. It looks like a crosstab query?
    Wish people would use tables in the foum...
    Country City1 City2 City3
    UK London Dover Brighton
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    duplicate post
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    fbb is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    3
    Thanks for the help. I didn't even know what to google for!

    Edit: looked up crosstab query and all the examples show sums and calculations? Am I doing something like a TRANSFORM?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sometimes Max works just fine and will show you data without summing/totaling. The problem I have at the moment is that City1, City2 and City3 can't just be thrown in as field headings. You have to use data from the table(s) and make row and column headers out of it. Your column headings are coming out of nowhere. The big question is, why would you want to take normalized data and present it as a spreadsheet? To mimic normalized data and make it look like a crosstab without performing aggregate functions on it, I think you'd have to have a copy of table2 in the query once for every city - not something I can recall trying because the goal is usually to normalize, not de-normalize data. So to be honest, I have no idea how to arrive at what you show.
    Maybe a UNION query for each city and join those? Good luck, assuming you are going to be dealing with a lot more than a few cities.

    EDIT (again) just had a thought: you might have to build a table and populate it with a set of append and update queries. Makes me thing of salmon in the fall.
    Last edited by Micron; 02-16-2021 at 08:23 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    would think a crosstab would work

    join the two tables,
    use country as row heading,
    "city" & [priority] as column heading
    and first/last/max or min [city] as value

  8. #8
    fbb is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    3
    You guys are so helpful! I managed to get this working by creating a crosstab query then generating a second query based on the first and renamed the headings. I personally wouldn't organize my data this way but the software I am using looks at each row and requires the city data in the columns. It does not parse any other way. Thanks again!

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @Ajax, excellent! I don't know why but xtabs and subqueries and I don't seem to play well together. I guess I've never really tried to grasp the nuances of either. Access seems to just sort of be a hobby now, and not a dedicated on either.
    Click image for larger version. 

Name:	fbb1.jpg 
Views:	16 
Size:	26.0 KB 
ID:	44276

    Click image for larger version. 

Name:	fbb2.jpg 
Views:	16 
Size:	9.5 KB 
ID:	44277
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

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