Setting Criteria for one of 3 related fields

[dmelsass]

Hello, bare with me. I am making a database for tracking weight lifting events.



I have Two Tables:
- Lifter (stores lifter's name, Gender, country)
- Event (stores specifics about event including lifterID, date, amount of lifts, etc.)

I related the lifter's Auto ID # and made one of the fields in the Event form a lookup field that includes 3 identifiers from my lifter table (lifterID, Name, gender)

I want to create two queries that shows one as all the male lifters and the other for female lifters.

However when I create the query the criteria will not filter male or female because the field is defined by the lifter's ID number.

I can create the query to display the lifterID field as male or female but it still will not let me criteria the data to female or male because it is related by the ID number and causes a criteria error. I have to manually filter the data each time I run the query instead of the query doing it automatically.

Anyone know a solution?

I attached some photos to hopefully help..

Sent from my SM-G935V using Tapatalk

[JoeM]

Instead of doing Filters, since you are doing Queries, simply join the two Tables in your Query, and use Query Criteria on the Gender field to return what you want.

[dmelsass]

I really feel dumb at this point. I was figuring by adding the two tables into the query separately I would get multiple duplicate records because the lifterID loops back to lifter.

I feel like an idiot.. Thanks for the help.

Sent from my SM-G935V using Tapatalk

[dmelsass]

Well I do get multiple duplicate fields but I can filter that out when I get to forms and records...

Sent from my SM-G935V using Tapatalk

[JoeM]

You do not need to add ALL fields from both table to your Query. You can pick and choose exactly which fields you want to return.
And you can even apply criteria to fields which you are not showing in the Query (just uncheck the "Show" box under the field).

[dmelsass]

You do not need to add ALL fields from both table to your Query. You can pick and choose exactly which fields you want to return.
And you can even apply criteria to fields which you are not showing in the Query (just uncheck the "Show" box under the field).

I understand that, but my event table pushes me toward that 20 field cap if I do not run it astericked to include all fields.

Everything I need listed I believe is in total 24 fields..

Sent from my SM-G935V using Tapatalk

[JoeM]

You can do an asterisk from one table and not the other.

I don't think there is 20 field cap (I have added many more in the past). That might just be what it shows by default. Click on the "Insert Columns" button on the Query Setup ribbon to add more.

[dmelsass]

You can do an asterisk from one table and not the other.

I don't think there is 20 field cap (I have added many more in the past). That might just be what it shows by default. Click on the "Insert Columns" button on the Query Setup ribbon to add more.

I will try that. Thanks for the help.

Sent from my SM-G935V using Tapatalk

[dmelsass]

I will try that. Thanks for the help.

Sent from my SM-G935V using Tapatalk

I think it was a 20 field cap for the wizard. Manual was able to tweek and works great.

Thanks again!

Sent from my SM-G935V using Tapatalk

[JoeM]

You are welcome!
Yes, the Wizards can be a bit limiting (like only allowing you to join two tables or join on one field).
So if you use them, you may sometimes need to manually update the query after it has been created.