Adding a related table spoils the query--help!

[Guest]

I'm new at this. I've got tblSales which includes 2 number fields: [CityID]
related to tblCities [CityID] and [CountyID] related to tblCounties
[CountyID]. There are additional fields in all three tables. I can create a
query and enter data using tblSales and tblCities[CityID] ok. However, when
I attempt to add tblCounties to the query (no data just the table) and run
the query the resulting datasheet is blank. Delete tblCounties from the
query, and I can see all the original data in the datasheet. I've checked
for correct data types in the related fields and all field properties are
identical in both tbleCities and tblCounties. Why can't I add tbl Counties
to my query?

I'm brain dead, and need help. Thanks

 

[Guest]

If there is no data in table tblCounties and the join between the table is
inner join then you will get no data back in your query
change the join between the two to right join that include all the data from
the other table.

 

[John Vinson]

I'm new at this. I've got tblSales which includes 2 number fields: [CityID]
related to tblCities [CityID] and [CountyID] related to tblCounties
[CountyID]. There are additional fields in all three tables. I can create a
query and enter data using tblSales and tblCities[CityID] ok. However, when
I attempt to add tblCounties to the query (no data just the table) and run
the query the resulting datasheet is blank. Delete tblCounties from the
query, and I can see all the original data in the datasheet. I've checked
for correct data types in the related fields and all field properties are
identical in both tbleCities and tblCounties. Why can't I add tbl Counties
to my query?

My guess would be that the CountyID in tblSales is empty - i.e. you
have the cities defined, but not their counties.

If each City belongs to one and only one County, then I would suggest
that your tblSales should not contain the CountyID field AT ALL. You
can get it from tblCities, if that table contains the CountyID for
each city.

John W. Vinson[MVP]

 

[Guest]

Neither tblCounties[CountyID] nor tblCities[CityID] is blank. However, I
will only use one (or none) in qrySales that populates tblSales. I need to
leave a null value whenever I choose not to populate the qry with [a CityID]
or [CountyID]. Wish I could normalize by having Cities related to Counties,
but I don't have that data for now. At this point, I'm reasonably sure that
my join types (in qrySales between tblCities[CityID] and
tblCounties[CountyID}are incorrect, but all my experimenting has yet to get
me beyond the error messages. Hope this better defines my problem.
--
Tom

I'm new at this. I've got tblSales which includes 2 number fields: [CityID]
related to tblCities [CityID] and [CountyID] related to tblCounties
[CountyID]. There are additional fields in all three tables. I can create a
query and enter data using tblSales and tblCities[CityID] ok. However, when
I attempt to add tblCounties to the query (no data just the table) and run
the query the resulting datasheet is blank. Delete tblCounties from the
query, and I can see all the original data in the datasheet. I've checked
for correct data types in the related fields and all field properties are
identical in both tbleCities and tblCounties. Why can't I add tbl Counties
to my query?

My guess would be that the CountyID in tblSales is empty - i.e. you
have the cities defined, but not their counties.

If each City belongs to one and only one County, then I would suggest
that your tblSales should not contain the CountyID field AT ALL. You
can get it from tblCities, if that table contains the CountyID for
each city.

John W. Vinson[MVP]

 

[Guest]

can you send your query string?

Neither tblCounties[CountyID] nor tblCities[CityID] is blank. However, I
will only use one (or none) in qrySales that populates tblSales. I need to
leave a null value whenever I choose not to populate the qry with [a CityID]
or [CountyID]. Wish I could normalize by having Cities related to Counties,
but I don't have that data for now. At this point, I'm reasonably sure that
my join types (in qrySales between tblCities[CityID] and
tblCounties[CountyID}are incorrect, but all my experimenting has yet to get
me beyond the error messages. Hope this better defines my problem.
--
Tom

I'm new at this. I've got tblSales which includes 2 number fields: [CityID]
related to tblCities [CityID] and [CountyID] related to tblCounties
[CountyID]. There are additional fields in all three tables. I can create a
query and enter data using tblSales and tblCities[CityID] ok. However, when
I attempt to add tblCounties to the query (no data just the table) and run
the query the resulting datasheet is blank. Delete tblCounties from the
query, and I can see all the original data in the datasheet. I've checked
for correct data types in the related fields and all field properties are
identical in both tbleCities and tblCounties. Why can't I add tbl Counties
to my query?

My guess would be that the CountyID in tblSales is empty - i.e. you
have the cities defined, but not their counties.

If each City belongs to one and only one County, then I would suggest
that your tblSales should not contain the CountyID field AT ALL. You
can get it from tblCities, if that table contains the CountyID for
each city.

John W. Vinson[MVP]

 

[Guest]

Thanks for the help. My problem was with the query joins. Fiddled about a
bit, and BINGO!
--
Tom

can you send your query string?

Neither tblCounties[CountyID] nor tblCities[CityID] is blank. However, I
will only use one (or none) in qrySales that populates tblSales. I need to
leave a null value whenever I choose not to populate the qry with [a CityID]
or [CountyID]. Wish I could normalize by having Cities related to Counties,
but I don't have that data for now. At this point, I'm reasonably sure that
my join types (in qrySales between tblCities[CityID] and
tblCounties[CountyID}are incorrect, but all my experimenting has yet to get
me beyond the error messages. Hope this better defines my problem.
--
Tom

On Tue, 26 Apr 2005 13:16:06 -0700, "Tom U"

I'm new at this. I've got tblSales which includes 2 number fields: [CityID]
related to tblCities [CityID] and [CountyID] related to tblCounties
[CountyID]. There are additional fields in all three tables. I can create a
query and enter data using tblSales and tblCities[CityID] ok. However, when
I attempt to add tblCounties to the query (no data just the table) and run
the query the resulting datasheet is blank. Delete tblCounties from the
query, and I can see all the original data in the datasheet. I've checked
for correct data types in the related fields and all field properties are
identical in both tbleCities and tblCounties. Why can't I add tbl Counties
to my query?

My guess would be that the CountyID in tblSales is empty - i.e. you
have the cities defined, but not their counties.

If each City belongs to one and only one County, then I would suggest
that your tblSales should not contain the CountyID field AT ALL. You
can get it from tblCities, if that table contains the CountyID for
each city.

John W. Vinson[MVP]