How to create a query with multiple conditions

[Jimruns919]

Hi,



This is my first post.

Over the last 3 weeks or so I have taught myself MS Access and created a rather large database which is going to help the front desk employees with a specific task - Day Camp Registration.

There are many different moving parts but the specific I am trying to fix is a query I created. I created a query for each of the 7 weeks of camp. When someone signs up they can sign up for any of three spots, Camp, Sports or crafts. So they do not necessarily have to sign up for camp for the day and only sign up for the sports or craft activities for later in the day. The issue is that the query lists everyone attending camp to make a roster for the counselors for each week and breaks it down by the 4 age groups.

That works. The issue is if someone does not sign up for camp but only the sports or crafts activities they are not listed.

I have all the fields in the query and the criteria is set for CAMP as <> 0.

How do I adjust it so if either camp, sports or crafts are <> 0 then they will be listed on the query?

[Welshgasman]

Put the criteria on separate lines in the query designer, though it sounds like your db is not normalised.

[Micron]

I created a query for each of the 7 weeks of camp

Not wise. One query that uses a parameter(s) is usually best.

Actually, I think the db may very well be normalized since if the activity is in (eg.) tblActivities and the records are ActivityType (camp, sport, craft or their related numeric pk versions) then you will need each type on a separate line as advised. But if what you need are numbers by participant type, that would require either a Totals query or a report or a form/subform setup with calculated controls. Reports generally do a better job of presentation and are meant to be printed if that helps.

EDIT - a pic of your table relationships, if you have created them, would probably help with getting focused answers. See "how to attach files" at the top of the page if needed, since you cannot copy/paste images here.

[Jimruns919]

Not wise. One query that uses a parameter(s) is usually best.

Actually, I think the db may very well be normalized since if the activity is in (eg.) tblActivities and the records are ActivityType (camp, sport, craft or their related numeric pk versions) then you will need each type on a separate line as advised. But if what you need are numbers by participant type, that would require either a Totals query or a report or a form/subform setup with calculated controls. Reports generally do a better job of presentation and are meant to be printed if that helps.

EDIT - a pic of your table relationships, if you have created them, would probably help with getting focused answers. See "how to attach files" at the top of the page if needed, since you cannot copy/paste images here.

This is the query. I have Week6Camp criteria <>0 but I also need week6craft and week6sports to show even if week6camp =0

[mike60smart]

Hi
To start with the Relationships between the 2 tables are wrong.

A Camper would attend Camp 1 or more Weeks

Your CamperInfo_tbl has a PK of CamperID

The related table Week6Camper_tbl should have a FK named CamperID

You would then Link CamperID in Both Tables and Enforce Referential Integrity.

I also thing you have created a table for Each Week of Camp?

This again is wrong. You should have 1 table to record all of the Weeks.

Can you upload a copy of the database without any Confidential data?

[Micron]

I agree - needs some fixing. Suggest that as you go, you stop using spaces in object names. Also CamperAge field is a no-no, especially if it is a calculated field. You would perform that calculation as required. Think of a table as being an entity and the fields are attributes of the entity. If you were a camper, do you have a feature (attribute that describes you) named TodaysDate? There are several others like that as well. I suggest you forget about uploading anything just yet, and instead, research db normalization but you are free to do otherwise of course. Then take another crack at your tables and repost the relationships for consideration. I'm seeing 6 tables or more - depends on what your intent is with some of them (e.g. BirthCert, shirt stuff, which also probably shouldn't be where you have them). As for Camper Sex, a table like tlbGender would be better than a field, and IMO, Sex is rarely included in a field named by accomplished designers.

[ArviLaanemets]

When you design a database, then design it and it's components to be usable for any time period, not for specific year, month, or week
So looking at your relations picture, you probably need:
tblCampYears: CampYear, [CampLocation], [FirstWeek], [LastWeek], ...;
tblCampYearWeeks: CampYear, CampWeek;
tblCampActivities: CampActivityID; ActivityDescription (ActivityDescriptions will be like "camping", "sport", "crafting", etc.);
tblCampers: CamperID, FirstName, LastName, Gender, DOB;
tblYearCampers: YearCamperID, CampYear, CamperID, RegDate, ...;
tblYearCampWeekActivities: YearCampWeekActivityID, CampYear, CampWeek, CampAcctivityID, ...;
tblYearCampWeekActivityCosts: YearCampWeekActivityCostID: YearCampWeekActivityID, ActivityCost (this design allows activities of same type on different weeks have different costs);
tblYearCamperActivities:YearCamperActivityID, YearCampWeekActivityID, YearCamperID;
etc.

I think you got the idea!

[Jimruns919]

Thank you for your input. i know I still have a lot to learn and I make fields without spaces but I guess I missed on some.

however, I did get this to work and I figured out my error. Basically I did the OR criteria wrong. Once I added the weekxcraft <>0 and weekxsports <>0 it worked perfectly.

[mike60smart]

You still need to think about your table structures.

If you do not fix these now then you will regret it later down the line.