Query not returning results

[Knox]

I have two tables ("Volume" and "Class") in a database. Volume is joined to
Class in a one to many relationship. When I run a query, results are only
returned if there are entries in both the volume and class tables. I want
the query to return results from the volume table even if there are not
entries under the class table.

 

[Golfinray]

In the query, click on the line between the tables then right click, select
join type, and choose #2

 

[Knox]

I tried it and it did not work.

In the query, click on the line between the tables then right click, select
join type, and choose #2

 

[Golfinray]

Did you try option 3?

I tried it and it did not work.

 

[Knox]

I tried that and it didn't work either

Did you try option 3?

 

[Golfinray]

Do you have primary keys assigned on your tables and have the primary keys
linked in your query?

 

[Knox]

Yes, I have a primary key assigned to the Volume table and it is linked to a
field in the class table that is not a primary key.

 

[Dale Fye]

Post your SQL statement!

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Knox]

here it is. It was made with query builder:

SELECT VOLUME.LRS, VOLUME.CO_NUM AS VOLUME_CO_NUM, VOLUME.STATION_ID,
VOLUME.COUNTY_NAME, VOLUME.ROUTE_PREFIX, VOLUME.ROUTE_NUMBER,
VOLUME.ROUTE_SUFFIX, VOLUME.STATION_ID_2, VOLUME.STATION_TYPE,
VOLUME.FUNCTIONAL_CLASS, VOLUME.BEGIN_MP, VOLUME.END_MP, VOLUME.COUNT_MP,
VOLUME.ADT_2008, VOLUME.[Historical_Coun_ Growt_ Rate], VOLUME.[2008],
VOLUME.[2007], VOLUME.[2006], VOLUME.[2005], VOLUME.[2004], VOLUME.[2003],
VOLUME.[2002], VOLUME.[2001], VOLUME.[2000], VOLUME.[1999], VOLUME.[1998],
VOLUME.[1997], VOLUME.[1996], VOLUME.[1995], VOLUME.[1994], VOLUME.[1993],
VOLUME.[1992], VOLUME.[1991], VOLUME.[1990], VOLUME.[1989], VOLUME.[1988],
VOLUME.[1987], VOLUME.[1986], VOLUME.[1985], VOLUME.[1984], VOLUME.[1983],
VOLUME.[1982], VOLUME.[1981], VOLUME.[1980], VOLUME.[1979], VOLUME.[1978],
VOLUME.[1977], VOLUME.[1976], VOLUME.[1975], VOLUME.[1974], VOLUME.[1973],
VOLUME.[1972], VOLUME.[1971], VOLUME.[1970], VOLUME.[1969], VOLUME.[1968],
VOLUME.[1967], VOLUME.[1966], VOLUME.[1965], VOLUME.[1964], VOLUME.[1963],
VOLUME.[Impact or new road yr], VOLUME.[2008 trend Linear], VOLUME.[2033
trend Linear], VOLUME.[GR Linear], VOLUME.[2008 trend AVG], VOLUME.[2033
trend AVG], VOLUME.[GR AVG], VOLUME.[2008 trend EXP], VOLUME.[2033 trend
EXP], VOLUME.[GR EXP], VOLUME.[R-squared EXP], VOLUME.[R-squared Linear],
VOLUME.COUNTS_SINCE_82_OR_IMPACT, VOLUME.[25], VOLUME.Stationcnty,
VOLUME.Stationcntyname, VOLUME.Impactyear, VOLUME.Newroadyear,
VOLUME.Bmpdesc, VOLUME.Empdesc, VOLUME.Lastactualcount, VOLUME.Lacountyear,
VOLUME.[Rec No], VOLUME.Id AS VOLUME_Id, VOLUME.Hwydist, VOLUME.Hwycnty,
VOLUME.Hwycity, VOLUME.Hwycityname, VOLUME.Stname, CLASS.ID AS CLASS_ID,
CLASS.CO_NUM AS CLASS_CO_NUM, CLASS.COUNTY, CLASS.ROUTE, CLASS.Station,
CLASS.STA_ID, CLASS.MP, CLASS.FC, CLASS.DATE, CLASS.ADT_AT_CT,
CLASS.DIRECTION, CLASS.CT_BI_DIR, CLASS.T_CT, CLASS.T_PER, CLASS.AX_PER_T,
CLASS.PK_HR, CLASS.PK_HR_CT, CLASS.PK_T_CT, CLASS.PEAK_T_PER, CLASS.HV_T_CT,
CLASS.HV_T_PER, CLASS.HV_T_PK, CLASS.HV_PK_PER, CLASS.AX_FAC,
CLASS.NUM_HRS_CT, CLASS.MOTORCYCLE, CLASS.CAR, CLASS.PICKUP_VAN, CLASS.BUS,
CLASS.[2AX_6TIRE], CLASS.[3AX_SING], CLASS.[4AX_SING], CLASS.[2AX_TRAIL],
CLASS.[3AX_TRAIL], CLASS.[6AX_SING_TRAIL], CLASS.[5AX_MULT],
CLASS.[6AX_MULT], CLASS.[7AX], CLASS.OTHER, CLASS.LOCATION, CLASS.UNKNOWN,
VOLUME.STATION_ID_2
FROM VOLUME INNER JOIN CLASS ON VOLUME.STATION_ID=CLASS.STA_ID
WHERE (((VOLUME.STATION_ID)=[Enter the Station ID (000XXX)]) AND
((CLASS.DIRECTION)="both"));

 

[Dale Fye]

Well, it looks like you have quite a spreadsheet there (Volume). This is
not the type of table structure I would normall recommend in a relational
database.

To create a query which contains all of the records from one table, and
only those that match from another, the technique normally looks something
like the following. Keep in mind that I strongly recommend that you not use
the * to select all the fields from a table, as you will very rarely need to
retrieve all of the fields from a table in a single query. And it is even
rarer that you would need to retrieve all of the fields from both tables
that are being joined together.

SELECT Table1.*, Table2.*
FROM Table1
LEFT JOIN Table2
ON Table1.SomeField = Table2.SomeField

You can create this type of query in the query design grid (without the
wizard) by selecting the two tables you need. Then, click on the field that
you want to join on, in the table where you want to include all the records,
and drag over to the matching field in the other table. Right click on the
line between the two tables, and select the radio button that corresponds to
"Select all the records from table1 and only the matching records from
table2" (or something like that). Now, add the fields you want from each of
the two tables and run the query. Those records that don't have a match for
the linking field in table2 will show up will all of the fields from table2
as NULL.

Translated to your table and field names, it might look like:

SELECT Volume.*, Class.*
FROM Volume
LEFT JOIN Class
ON Volume.Station_ID = Class.Sta_ID
WHERE Volume.Station_ID = [Enter the Station ID (000XXX)]
AND Class.Direction = "both"

The problem with this, is that if a record doesn't exist in Class for a
particular station_id, then the second part of the WHERE clause will not
evaluate to True (Class.Direction will be NULL), and those records will not
be included in the result set. So, I think you need to try something like:

SELECT Volume.*, Class.*
FROM Volume
LEFT JOIN Class
ON Volume.Station_ID = Class.Sta_ID
WHERE Volume.Station_ID = [Enter the Station ID (000XXX)]
AND (Class.Direction = "both" OR Class.Direction IS NULL)

HTH
Dale

here it is. It was made with query builder:

SELECT VOLUME.LRS, VOLUME.CO_NUM AS VOLUME_CO_NUM, VOLUME.STATION_ID,
VOLUME.COUNTY_NAME, VOLUME.ROUTE_PREFIX, VOLUME.ROUTE_NUMBER,
VOLUME.ROUTE_SUFFIX, VOLUME.STATION_ID_2, VOLUME.STATION_TYPE,
VOLUME.FUNCTIONAL_CLASS, VOLUME.BEGIN_MP, VOLUME.END_MP, VOLUME.COUNT_MP,
VOLUME.ADT_2008, VOLUME.[Historical_Coun_ Growt_ Rate], VOLUME.[2008],
VOLUME.[2007], VOLUME.[2006], VOLUME.[2005], VOLUME.[2004], VOLUME.[2003],
VOLUME.[2002], VOLUME.[2001], VOLUME.[2000], VOLUME.[1999], VOLUME.[1998],
VOLUME.[1997], VOLUME.[1996], VOLUME.[1995], VOLUME.[1994], VOLUME.[1993],
VOLUME.[1992], VOLUME.[1991], VOLUME.[1990], VOLUME.[1989], VOLUME.[1988],
VOLUME.[1987], VOLUME.[1986], VOLUME.[1985], VOLUME.[1984], VOLUME.[1983],
VOLUME.[1982], VOLUME.[1981], VOLUME.[1980], VOLUME.[1979], VOLUME.[1978],
VOLUME.[1977], VOLUME.[1976], VOLUME.[1975], VOLUME.[1974], VOLUME.[1973],
VOLUME.[1972], VOLUME.[1971], VOLUME.[1970], VOLUME.[1969], VOLUME.[1968],
VOLUME.[1967], VOLUME.[1966], VOLUME.[1965], VOLUME.[1964], VOLUME.[1963],
VOLUME.[Impact or new road yr], VOLUME.[2008 trend Linear], VOLUME.[2033
trend Linear], VOLUME.[GR Linear], VOLUME.[2008 trend AVG], VOLUME.[2033
trend AVG], VOLUME.[GR AVG], VOLUME.[2008 trend EXP], VOLUME.[2033 trend
EXP], VOLUME.[GR EXP], VOLUME.[R-squared EXP], VOLUME.[R-squared Linear],
VOLUME.COUNTS_SINCE_82_OR_IMPACT, VOLUME.[25], VOLUME.Stationcnty,
VOLUME.Stationcntyname, VOLUME.Impactyear, VOLUME.Newroadyear,
VOLUME.Bmpdesc, VOLUME.Empdesc, VOLUME.Lastactualcount,
VOLUME.Lacountyear,
VOLUME.[Rec No], VOLUME.Id AS VOLUME_Id, VOLUME.Hwydist, VOLUME.Hwycnty,
VOLUME.Hwycity, VOLUME.Hwycityname, VOLUME.Stname, CLASS.ID AS CLASS_ID,
CLASS.CO_NUM AS CLASS_CO_NUM, CLASS.COUNTY, CLASS.ROUTE, CLASS.Station,
CLASS.STA_ID, CLASS.MP, CLASS.FC, CLASS.DATE, CLASS.ADT_AT_CT,
CLASS.DIRECTION, CLASS.CT_BI_DIR, CLASS.T_CT, CLASS.T_PER, CLASS.AX_PER_T,
CLASS.PK_HR, CLASS.PK_HR_CT, CLASS.PK_T_CT, CLASS.PEAK_T_PER,
CLASS.HV_T_CT,
CLASS.HV_T_PER, CLASS.HV_T_PK, CLASS.HV_PK_PER, CLASS.AX_FAC,
CLASS.NUM_HRS_CT, CLASS.MOTORCYCLE, CLASS.CAR, CLASS.PICKUP_VAN,
CLASS.BUS,
CLASS.[2AX_6TIRE], CLASS.[3AX_SING], CLASS.[4AX_SING], CLASS.[2AX_TRAIL],
CLASS.[3AX_TRAIL], CLASS.[6AX_SING_TRAIL], CLASS.[5AX_MULT],
CLASS.[6AX_MULT], CLASS.[7AX], CLASS.OTHER, CLASS.LOCATION, CLASS.UNKNOWN,
VOLUME.STATION_ID_2
FROM VOLUME INNER JOIN CLASS ON VOLUME.STATION_ID=CLASS.STA_ID
WHERE (((VOLUME.STATION_ID)=[Enter the Station ID (000XXX)]) AND
((CLASS.DIRECTION)="both"));

Post your SQL statement!

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.