Querying Multiple Tables

[Justin]

I have a database that records details of people who either fundraise, donate, hold events or volunteer for our charity. The Main table (containing personal data) is linked to the other four tables FUNDRAISING, DONATIONS, EVENTS and VOLUNTEERS. One person can do one or more of these things. My problem is that I want my users to be able to search the database by name, postcode etc but I want ALL data returned and displayed in a form I have designed respective of whether there is data or not. For example if I was entered and had entries in the donations table, i would want to see that there are empty fields for the other 3 tables because a donor may become a fundraiser/volunteer etc at a later date. At the moment my query only works if I include the MAIN table and the DONATIONS table. How do I deal with blank fields so that the query doesnt appear not to return any info becuase some of the corresponding fields in other tables are blank? I hope this is not too confusing....

 

[neileg]

I'm not sure exactly what you want, but here's what I would do.

Create a form that has your Main table as its source. Create four subforms based on FUNDRAISING, DONATIONS, EVENTS and VOLUNTEERS. Add these subforms to the Main form. This will then show the form for each person in the data, and will have entries on the subforms according to which category or categories the person falls into. I think this is what you want.

It is preferable, of course, to base your forms on queires, even if the query simply returns all the data in the table. If you want to manipulate your data in the furute, you will find it much easier.

Aesthetically, you might want to create a main form with four tabs, one for each subform.

 

[Justin]

Thanks for this - I should have mentioned I have got to this stage already. What I now want to do is add a search facility so that my users can search by name, postcode, who is a donor etc BUT my query comes back blank even though there is data in the database. However I think this is because some of the linked tables are blank - in the example I am testing the individual is a donor but not a fundraiser,event or volunteer but I want to return all fields even the blank ones as these might need updating when someone is searched for....

 

[neileg]

Ah, I think you have a join problem. I suspect you have used an inner join between your tables in the query, i.e. Only include rows where the joined fields are equal. You need a Left join, i.e. Include all records from your Main table and only those items from the others that match.

 

[Justin]

Thanks Neil - just to claify this is the SQL statement for the query as it stands

SELECT DISTINCTROW Individuals.IndividualID, Individuals.Name, Individuals.Address1, Individuals.Address2, Individuals.Address3, Individuals.Postcode, Individuals.Telephone, Individuals.Email, Individuals.[Company Name], Individuals.CompanyAdd1, Individuals.CompanyAdd2, Individuals.CompanyAdd3, Individuals.CompanyPostcode, Individuals.[Company Type], Individuals.Notes, Individuals.[Info Requested], Individuals.[Pack Returned], Individuals.Communication, Individuals.[Corporate Contact], Individuals.Donor, Individuals.[Individual Fundraiser], Individuals.[Event Attended], Individuals.[Gift Aid], Individuals.Volunteer, Interests.InterestID, Interests.IndivID, Interests.SportInterest, Interests.MusicInterest, Interests.FilmInterest, Volunteers.[Volunteer ID], Volunteers.IndivID, Volunteers.WeekendDay, Volunteers.WeekendEve, Volunteers.WeekendAllDay, Volunteers.DaytimeMorning, Volunteers.DaytimeAfternoon, Volunteers.DaytimeEvening, Volunteers.Week, Volunteers.Month, Volunteers.MorethanOnce, Volunteers.HALCOnly, Volunteers.DataEntry, Volunteers.Mailouts, Volunteers.CountingMoney, Volunteers.Letters, Volunteers.MailMerge, Volunteers.TrekSupport, Volunteers.Internet, Volunteers.Word, Volunteers.Excel, Volunteers.Access, Volunteers.Writing, Volunteers.Phone, Volunteers.Research, Volunteers.Newsletters, Volunteers.Organising, Volunteers.Events, Volunteers.Training, Fundraiser.FundraiserID, Fundraiser.IndivID, Fundraiser.Event, Fundraiser.[Event Date], Fundraiser.[Target Amount], Fundraiser.[Amount Raised], Fundraiser.Description, Fundraiser.[Date Banked], EventDetail.EventDetailID, EventDetail.IndivID, EventDetail.EventID, Event.EventID, Event.[Event Date], Event.[Event Name], Event.[Amount Paid], Event.Notes, Donations.DonationID, Donations.IndivID, Donations.[Donation Date], Donations.[Donation Amount], Donations.[Donation From], Donations.DonationMethod, Donations.Campaign, Donations.[Gift Aid]
FROM ((((Individuals INNER JOIN Donations ON Individuals.IndividualID = Donations.IndivID) INNER JOIN (Event INNER JOIN EventDetail ON Event.EventID = EventDetail.EventID) ON Individuals.IndividualID = EventDetail.IndivID) INNER JOIN Fundraiser ON Individuals.IndividualID = Fundraiser.IndivID) INNER JOIN Interests ON Individuals.IndividualID = Interests.IndivID) INNER JOIN Volunteers ON Individuals.IndividualID = Volunteers.IndivID;

DO I change all INNER JOIN to LEFT JOIN? I tried this and I get an error....sorry my knowledge in this area is pretty limited thanks

 

[neileg]

Yuk! I see you're not using forms and subforms.

I've waded through this and I think the following should do it.

FROM ((((Individuals LEFT JOIN Donations ON Individuals.IndividualID = Donations.IndivID) LEFT JOIN (Event INNER JOIN EventDetail ON Event.EventID = EventDetail.EventID) ON Individuals.IndividualID = EventDetail.IndivID) LEFT JOIN Fundraiser ON Individuals.IndividualID = Fundraiser.IndivID) LEFT JOIN Interests ON Individuals.IndividualID = Interests.IndivID) LEFT JOIN Volunteers ON Individuals.IndividualID = Volunteers.IndivID;

You have not explained the Event table, so perhaps the join here is wrong.

Oh, and your structure is not normalised. Search in these forums on normalisation (or normalization!).

 

[Justin]

thanks for your help with this !