2 queries 2 different results

[amerifax]

I have two quarries that should come up with the same total. The only difference.

Uses a relationship and the other one does not. listed below is the code:


11CTY Total 127009
SELECT SEQNO, SEAL, SC, PER_DATE, SB, LOT_SIZE, BLDCERT, BLD, JOBADDRESS, JOB_ZIP, JOB_CITY, JOB_PHN, PHN_SEARCH, SPEC, OWN, OWN_FST, OWN_SEC, OWN_LST, OWN_PHN, OWNADDRESS, OWN_CITY, OWN_ST, OWN_ZIP, OCCUPANCY, CON_TYPE, STORIES, FOUNDATION, USE, HAVC_EQUIP, SEWER, SEPTIC_NO, WATER, ENERGY, PRICE, SQ_BASE, SQ_LIVE, SQ_GRG, LAT, LON, IPDATE
FROM permit
WHERE (((PER_DATE)>=#1/1/1989#) AND ((Left([permit.sc],2)) In ("14","20","28","30","40","45","51","59","64","66" ,"67")));

11CTY (Fixed) Total 126577
SELECT PERMIT.SEQNO, PERMIT.SEAL, PERMIT.SC, PERMIT.PER_DATE, PERMIT.SB, PERMIT.LOT_SIZE, PERMIT.BLDCERT, PERMIT.BLD, PERMIT.JOBADDRESS, PERMIT.JOB_ZIP, PERMIT.JOB_CITY, PERMIT.JOB_PHN, PERMIT.PHN_SEARCH, PERMIT.SPEC, PERMIT.OWN, PERMIT.OWN_FST, PERMIT.OWN_SEC, PERMIT.OWN_LST, PERMIT.OWN_PHN, PERMIT.OWNADDRESS, PERMIT.OWN_CITY, PERMIT.OWN_ST, PERMIT.OWN_ZIP, PERMIT.OCCUPANCY, PERMIT.CON_TYPE, PERMIT.STORIES, PERMIT.FOUNDATION, PERMIT.USE, PERMIT.HAVC_EQUIP, PERMIT.SEWER, PERMIT.SEPTIC_NO, PERMIT.WATER, PERMIT.ENERGY, PERMIT.PRICE, PERMIT.SQ_BASE, PERMIT.SQ_LIVE, PERMIT.SQ_GRG, PERMIT.LAT, PERMIT.LON, PERMIT.IPDATE, BUILDER.BLD, BUILDER.BUILDER, BUILDER.TOTS_BLD, BUILDER.STAT_3, BUILDER.STAT_5, BUILDER.STAT_10, BUILDER.YR_A, BUILDER.YR_B, SUB.SC, SUB.SUB, SUB.TOTS_SUB, SUB.TOT_A1, SUB.TOT_B5, SUB.TOT_B10, CITY.SC, CITY.typecity, CITY.tots_city, CITY.county, CITY.nw_city, CITY.Date, CITY.tot, CITY.Last, CITY.yr_a, CITY.yr_b, CITY.tot_a1, CITY.tot_b1, CITY.tot_b3
FROM ((permit INNER JOIN BUILDER ON permit.BLD = BUILDER.BLD) INNER JOIN SUB ON permit.SB = SUB.SB) INNER JOIN CITY ON permit.SC = CITY.SC
WHERE (((PERMIT.PER_DATE)>=#1/1/1989#) AND ((Left([permit.sc],2)) In ("14","20","28","30","40","45","51","59","64","66","67")));

It sure seems the same.

Bob

 

[recyan]

Perhaps INNER JOIN is the cause.
Have you tried removing the WHERE clause from both the queries & then see how many records each of them returns.

Thanks

 

[amerifax]

I have and I am getting different results from each one. I basically want the query dependent on the WHERE clause because I need records with those specific dates and substring of SC field. But once I have those records, I want to fill in a few values or additional fields from other tables.

Such as, ((Left([permit.sc],2)), is equivalent to the county name. So I want to pull the county name from the city table but that is all. I still want the same records from the permit table.

How would I do this? Do I not need a join? In dBase I would set a relationship between the two based on a substr of SC which both tables would contain and then just pull the name from the City table and the rest of the record from the permit table.

Thanks for any help.

Bob

 

[recyan]

Hi,

Based on your first post, are you looking for
127009 records
in the results of the join query.
If that is the case,
have you tried using the
11CTYTotal query
on the Left hand side of the join,

Try something like below in the query builder design grid & see how it goes,

11CTYTotal query
LEFT JOIN
BUILDER
ON 11CTYTotal.BLD = BUILDER.BLD

LEFT JOIN
SUB
ON 11CTYTotal.SB = SUB.SB =

LEFT JOIN
CITY
ON 11CTYTotal.SC = CITY.SC

PS : I assume, you don't have any space in your query Name

Thanks

 

[Galaxiom]

How would I do this? Do I not need a join? In dBase I would set a relationship between the two based on a substr of SC which both tables would contain and then just pull the name from the City table and the rest of the record from the permit table.

Access SQL can do a join on a substring of a field if that is what you need. It just can't manage that in the Design View.

Similarly joins on less than, greater than, etc.

However a normal join is much faster than using a substring which requires a function applied to every record.

 

[amerifax]

>>on the Left hand side of the join,<<

I'm not familiar with the term Left Hand Side. Does this have something to do with how the query processes?

Bob

 

[Galaxiom]

A LEFT JOIN includes all records from the table on the left side of the join statement plus those records which match in the ON statement on the right side.

 

[amerifax]

>>recyan<<
I will research the term "left join" this might be my problem. I also suspect if a value is blank I might get a undesirable response.

Bob