?
Solved

SQL Question: Multiple tables and Group By

Posted on 2006-05-24
11
Medium Priority
?
1,774 Views
Last Modified: 2008-08-24
Need some advice on a single sql or sequence of sqls required for the following:

Table1 contains two relevant fields: home_state and job_state.  Both are 2 char state abbreviations.

Table2 also contains two relevant fields: state and region.  This table contains 50 rows, one for each US state.  Region is a one byte field containing 7 region codes 1..7.  These codes identify which US region the associated state is in.  The regions are essentially equivalent to northest, southeast, etc.

I want to build a report that shows, for each home_state region, the count of job_state regions. So the first row of the report would show, for all home_states in region 1, the number of job_states in regions 1..7,  one report column for each for the 7 job_state regions. The second row of the report would show for all home_states in region 2, the number of job_states in regions 1..7.  The last row of the report would show for all home_states in region 7, the number of job_states in regions 1..7.

I've tried a couple different joined tables with Group By region, but have had no success, and now I'm just confused.
0
Comment
Question by:lennyh
  • 6
  • 3
  • 2
11 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16757234
this is a cross-tab query.  Open a New Query, use the Query Builder to Build a Cross-tab query joining Table1 to Table2 TWICE (once joind on Home_State and once joined on Job-State)  then select the Row label as Home_State_Region, the Column label as Job_State_Region, and then value being Count().

AW
0
 

Author Comment

by:lennyh
ID: 16760571
AW, thank you for your response.  

Unfortunately, I'm still confused.  Generally, I build SQL statements manually, so I'm not conversant with the Access query builder (more simply, really just dumb about it).  I did try playing with it using your instructions, but didn't know how to (as a starter) indicate the multiple joins within thte context of the query builder.  Also, tried the crosstab wizard, but since the query involves mulitple tables, the wizard indicated that I needed to first build a query requesting the multiple tables.   I was never sure what this really meant  (i.e. exactily what kind of intermediate query was needed).

Also, and maybe more significantly, although I'm using an Access mdb file as the database, I'm not using Access itself as the sql processor.  The database is being accessed via the web using an SQL-enabled scripting language (language is ODBScript -- conceptually similar to php).  So any Access-specific SQL extensions are likely not usable.

Again, thank you for your help.  I look forward to any further enlightenment.

Lenny
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16765448
in the Query Builder, you can add several tables to the panel at the top of the screen.  Then 'join' the tables by clicking on the field from one table, hold the left mouse button down, and drag the field to the corresonding field in the second table.  You can do this for multiple fields betwen two tables (one field at a time), or between different tables.

then you can select the fields from the joined tables to be shown in the result set of the query.

Save this query and give it a name.

You can then base the Cross-tab query on this saved query.

Build the cross-tab query, give it a name like xtbMyQuery, and you can then access that query from your web-page code.  

This does mean that you need to build some of the Structure for this to work, in the Access database, using Access the program.

AW



0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 58

Expert Comment

by:harfang
ID: 16769074
Hello lennyh

Your basic query will link Table1 to two copies of Table2, to get both regions. This would be:

    SELECT HOME.region, JOB.region
    FROM (
        Table1
        INNER JOIN Table2 As HOME
        ON Table1.home_state = HOME.state )
        INNER JOIN Table2 As JOB
        ON Table1.job_state = JOB.state

You can now count something with:

    SELECT HOME.region, JOB.region, Count(*) As nb_records
    FROM ...
    GROUP BY HOME.region, JOB.region

However, you might ask, what does this count? This counts the number of records in Table1. I suspect you want instead to count the number of *different* states, in which case, you will need an inner query finding  all unique combinations, and then count that:

    SELECT home_region, job_region, Count(*) As nb_states
    FROM (
        Select DistinctRow HOME.region As home_region, JOB.region As job_region
        From (
            Table1
            Inner Join Table2 As HOME
            On Table1.home_state = HOME.state )
            Inner Join Table2 As JOB
            On Table1.job_state = JOB.state
        )
    GROUP BY home_region, job_region;

Notice the "DISTINCTROW" modifier. If you want in fact to count the number of job regions for each home region, change the query to:

    SELECT home_region, Count(*) As nb_regions
    FROM (
        Select Distinct HOME.region As home_region, JOB.region As job_region
        From ...
        )
    GROUP BY home_region;

Notice the difference between DistinctRow (unique rows) and Distinct (unique values) in the inner query.

Let's say you want the version that counts states per region. We can transform that into a cross-tab using this syntax (the "value" i.e. the count() --> TRANSFORM, while the "column header" --> PIVOT):

    TRANSFORM Count(*)
    SELECT home_region
    FROM (
        Select DistinctRow
            HOME.region As home_region,
            JOB.region As job_region
        From (
            Table1
            Inner Join Table2 As HOME
            On Table1.home_state = HOME.state )
            Inner Join Table2 As JOB
            On Table1.job_state = JOB.state
        )
    GROUP BY home_region
    PIVOT job_region

I believe it's still a good idea to try these out in Access, you will loose less time debugging these queries.

Good luck!
(°v°)
0
 

Author Comment

by:lennyh
ID: 16771573
Hi harfang,

Thank you for you very detailed, thoughful response.

Unfortunately, success still eludes me.

I initially tried your suggested sequence for counting the job regions as below:

SELECT  home_region, Count(*) As nb_regions
FROM (
Select Distinct HOME.region As home_region, JOB.region As job_region
From (
Table1 Inner Join Table2 As HOME
On Table1.home_state = HOME.state)
Inner Join Table2 As JOB
On Table1.job_state = JOB.state)
GROUP BY home_region;

Access indicated "Syntax error in From clause" and highlighted the second Select cmd.

I then tried your inital suggestion to see if perhaps that would be helpful in isolating/identifying the problem:

Select HOME.region, JOB.region, Count(*) As nb_records
From
(SELECT HOME.region, JOB.region
FROM (
Table1
INNER JOIN Table2 as HOME
On Table1.home_state = HOME.state)
INNER JOIN Table2 As JOB
On Table1.job_state = JOB.state)
GROUP BY HOME.region, JOB.region;

Access again indicated "Syntax error in From clause" and highlighted the second Select cmd.

As a further step, I tried to merely execute the inner query:

SELECT HOME.region, JOB.region
    FROM (
        Table1
        INNER JOIN Table2 As HOME
        ON Table1.home_state = HOME.state )
        INNER JOIN Table2 As JOB
        ON Table1.job_state = JOB.state

Access had no syntactical propblem with this and returned an empty table with column headings: HOME.region and JOB.region

I looked over the sql statements and could find no typos (naturally, this doesn't mean there are none).  Whether or not there are logical problems is an assessment that unfortunately is currently beyond me.

Note  that the relevant field/table names in my test.mdb exactly match those in the SQL statements.  Is it obvious what is wrong?  Or do you have a further suggestion regarding how I might proceed to isolate the problem?

Lenny


0
 

Author Comment

by:lennyh
ID: 16771662
Arthur_Wood,

Thank you for the further clarification.  Based on that, I've played around a bit more with the query builder, but still apparently am doing something wrong. lA related concern: I am connecting to the access mdb via ODBC.  Assuming I'm evenaully able to generate the corrrect sqDo you happen to know if the Transfom and Pivot facilites are supported by ODBC?

Lenny
0
 

Author Comment

by:lennyh
ID: 16771689

Arthur Wood,

The above got sent while I was still updating/editing it.  The last 2 sentences should read:

As a related concern, I am connecting to the access mdb via ODBC.  Assuming I'm eventually able to generate the corrrect sql using ACCESS, do you happen to know if the Transfom and Pivot facilities are supported by ODBC?
0
 
LVL 58

Accepted Solution

by:
harfang earned 1000 total points
ID: 16772152
Hello Lenny,

I guess you will have to look for the problem elsewhere. My queries all work as posted. If needed, here is my test database: http://www.harfang.ch/expert/Q_21863083.zip

I'm guessing the problem has to do with the "SQL-enabled scripting language". As I don't know what it's capable of, I suggest you try to open the queries themselves from there or that you try variations of queries with named subqueries (instead of embedded subqueries).

Good luck!
(°v°)
0
 

Author Comment

by:lennyh
ID: 16795114
harfang, thank you for sending the zip file.  It enabled the solution to at least a part of my problem.  The database I had been using was an old one created w/ Access 97.  When I used your mdb file, everything wortked fine.  The queries executed properly -- no syntax errors (likely no surprise to you). After using the database uitilities to uplevel my mdb file, my queries also worked fine.  This would never had occurred to me without the reference to your zip file.

I still have a couple issues, but -- thanks to you -- I'm in far better shape than I had been, and I'm going to close out this question.  After I experiment a bit more on my own , if I have further questions, I might -- if you are available -- try to avail myself of your excellent help again.

Lenny
0
 

Author Comment

by:lennyh
ID: 16795162
Arthur_Wood, thank you as well for your advice.  If I had been more familiar with the query builder, perhaps I would have more successfully taken advantage of the instruction you were kind enough to provide.

Lenny
0
 
LVL 58

Expert Comment

by:harfang
ID: 16796355
Lenny,

I would not have thought about the version, of course. If needed, you could have kept the A97 version with the following change:

    SELECT ...
    FROM [
        ... subquery ...
        ]. As AnyName

A97 does not recognize the round brackets () for subqueries in the FROM clause. It needs the weird internal "[ ]. As" notation (with the dot) instead. As a matter of fact A2k also uses that notation, but is willing and able to translate from the more widespread use of round brackets.

I'm glad you sorted it out, good luck with your project!
(°v°)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question