Join Query?

[KWarzala]

I have 2 tables: tblLocations with Type (has store locations and whether they are a food or merchandise location) and tblBreakdown (has the date, Location, and the amount of money spent at the location) I am trying to make a report that shows all the store locations (even if no money was spent there) and be able to pull up the report by month and see what dates something was purchased from the store. Basically right now, there is a paper checklist for each month which has all the locations on it, and when something is purchased at the store it gets highlighted, so it is easy to see which one's haven't been visited in the month. I can make a report that shows the location name, and when it was visited, but it wont show the locations that haven't been visited. i don't know if this makes sense. thank you!

[Cleave_1b]

I have the same problem: List of people, list of properties, inner join doesn't show people without properties. wish someone had answered your question!

My tentative solution is to take the results of the current query, make it into a table then add the needed records. Lot of fields though!

Or is there an easy way to make a table out of a query other than stepping through and writing each field?

Dick

[KWarzala]

Oh man, i forgot i even posted this! i actually got it to work about a year ago, give me a minute to figure out what i did to make it work and i'll let you know!

I have the same problem: List of people, list of properties, inner join doesn't show people without properties. wish someone had answered your question!

My tentative solution is to take the results of the current query, make it into a table then add the needed records. Lot of fields though!

Or is there an easy way to make a table out of a query other than stepping through and writing each field?

Dick

[KWarzala]

OK, try doing this (I hope this helps!)

make a query (qryPeople) showing all the people there are, and whatever fields you need (include the linked field).

make a second query (qryProperties) from your properties table with just your linked field (i'm assuming the people list also has a field for property) and date field (i needed mine to just show this month's data when the report opened so i have the following formula in the criteria for date: "Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date())+1,0)"

Then make a third query (qryPeopleAndProperties - this will be the one that the report is based off of) using both of the first two queries, have your join say "Include ALL records from 'qryPeople' and only those records from 'qryLocations' where the joined fields are equal"

include the fields from qryPeople, and just the linked field from qryProperties (you don't need to add the date field to this one) in the totals column of the query select 'Count' for the field from qryProperties

Hopefully this gets you the result you're looking for (i'm not sure which way you need yours, so you may need to switch the two queries depending on how you want it)

I have the same problem: List of people, list of properties, inner join doesn't show people without properties. wish someone had answered your question!

My tentative solution is to take the results of the current query, make it into a table then add the needed records. Lot of fields though!

Or is there an easy way to make a table out of a query other than stepping through and writing each field?

Dick

[orange]

You may want to look at the Unmatched query in the ACCESS query wizard.

[Cleave_1b]

Sounds complex at first reading, but I imagine a bit of study will do it - thanks a lot!

Dick