Query Trouble

[nancybrown]

I am trying to create a very simple query using two tables and am not having any luck.

tblCity: 1) pkCityID; 2) City.
tblContribution: 1) pkContribution; fkCityID; Payee; City (other fields that aren't relative to this problem)

tblContribution data is popoulated using a data entry form.

My relationship is tblCity/pkCityID one to many to tblContribution/fkCityID, and the Join Type is set to match everything from both tables.

All cities that are in tblCity are also in tblContribution, nothing returns when I run the query.

Can someone advise . . . never had this happen before.

 

[xenou]

match everything from both tables.

I may be misunderstanding what you mean by match everything from both tables (especially since one table is just a list of cities). But taken at face value, this means:

SELECT * 
FROM City 
LEFT JOIN 
Contribution 
ON City.CityID = Contribution.CityID

IF the cities match, you'll get the matching contributions, and if the cities don't match you get the city but null for contribution (no matching records). If that's what you mean by match everything from both tables, anyway.

If you take away the left join and make it an inner join, you get the matching records only, but not literally everything from both tables anymore.

 

[nancybrown]

Here's what I have:
SELECT *
FROM tblCity INNER JOIN tblContributionInput ON tblCity.CityID = tblContributionInput.CityID;

city name appears in both the tblContributionInput and tblCity tables. I even tried to create two small tables in a new data base with just a few lines of data just to test and could not
get that to work either. At least I know my query structure is correct . . . so I'll keep trying. Thanks again.

 

[xenou]

city name appears in both the tblContributionInput and tblCity tables.

But City name should not appear in tblContributionInput, for an efficiently designed DB structure. Only City ID.

 

[nancybrown]

but this shouldn't have any impact on why the query isn't working correct?

 

[xenou]

IT shouldn't but I was curious why you had even mentioned it and whether city name was actually in both tables. I'm not sure why this would cause any trouble at all. Can you give your actual queries and a few rows of sample data from the tables?

 

[nancybrown]

I actually do have city in both files. The reason for this is the tblContributions was an excel file that the company used to record all contributions. I imported this into the Access database hoping to streamline this process a bit. I left all the fields and use it as the resource for a user form to add new records. I then created individual tables for city, state, events, etc., to use as the cbo boxes in the user form. Is this not the right approach? I've been away from Access for a while. How would you like me to send sample tables and queries?

 

[xenou]

Data base design is a large topic but you would consider not having city names and state names in the contribution table so that all of that data is consistent. For example, if the only way to enter the data is from a lookup on the city id, and all that is in the table is an id, then you only have one "truth" about how to spell the city name. But that requires you to have all cities in the city table, which isn't as hard as it sounds (you can probably get that data from googling it). Many large companies today use address validation routines instead.

On the other hand, if cities aren't really important for your reports and queries, you can just not worry about one person entering Cincinnati and another entering Cincinatti. Potato potato.

For sample data just post some relevant fields here for a couple of rows from each table.

 

[nancybrown]

Actually, what I've done is create individual tables for City, State, etc., using all the City's we deal with and not just contributions cities, then in the user form I've brought the fields in from the contribution table that I imported and have changed the city field to be a cbo with the "City table" so individuals cannot enter whatever they want. I spent a lot of time cleaning up the spreadsheet before importing it into the database for consistency. From what I remember when you create a form that has a cbo and subform, pkCityID should be the parent, but when I try to add the pkCityID to the contribution query, this is where I'm not getting any results.

 

[xenou]

Hmm. I think if you are trying to create an entry form, the cbo is simply a field in the form. It is bound to the table that you are entering data into, just like every other field. But Its row source (the looked up data) is the city table. When you make a selection, it gets that city as its value (or actually the PK of the city, if done right). The only thing about having the city table as the rowsource is that it is pre-populated with choices, so to speak,which is all a combobox is anyway. I always first drag all the fields into the form as textboxes, then convert the textbox to a combobox for a case like this. I'm not sure what a subform would have to do with any of this. I'm probably a little confused about why we are talking about data entry forms and query results at the same time, since they are not the same thing! Data entry is only about getting a record into a table (or tables). Query results come later.