how to use sql innerjoin of two columns criteria from one table in an MS access database

I'M Trying to use sql innerjoin of two columns criteria from one table in an MS access database And I only want one sql query.

Please Guide Me

Thanks

SELECT PERSON.IDPERSON, MASTERPERSON.PERSONNAME, PERSON.[DATE], MASTERPERSON.LOCATION, FORMAT(PERSON.DATE,'dddd') AS DAYS
FROM PERSON INNER JOIN MASTERPERSON ON PERSON.IDPERSON = MASTERPERSON.IDPERSON;

Result from Sql Above :

Table PERSON

Table MASTERPERSON

Table PERSONDAYS

Desired Result

Does sound like need another JOIN to include PersonDays table. This would involve a compound JOIN on Location and Date/Days fields. Access is picky about use of parens with multiple JOIN clauses. Use query designer to get correct syntax.

In your case, must calculate day name from date value to JOIN on day names. Unfortunately, cannot JOIN directly to calculated value in design view. Can switch to SQL View to add this additional JOIN AND PersonDays.DAYS = FORMAT(PERSON.DATE, 'dddd') but then can never open in design view.

SELECT Person.IDPerson, MasterPerson.PersonName, Person.Date, MasterPerson.Location, PersonDays.Days, PersonDays.Status
FROM PersonDays INNER JOIN (Person INNER JOIN MasterPerson 
ON Person.IDPerson = MasterPerson.IDPerson) 
ON PersonDays.Location = MasterPerson.Location AND PersonDays.DAYS = FORMAT(PERSON.DATE, 'dddd');

Otherwise, build a query object that calculates a field with Format([Date],'dddd') then use that query in another query so design view will be able to display join.

An alternative is to use WHERE clause for this additional relationship criteria. Produces same result and can be displayed in Design View but may perform less efficiently.

SELECT Person.IDPerson, MasterPerson.PersonName, Person.Date, MasterPerson.Location, PersonDays.Days, PersonDays.Status
FROM PersonDays INNER JOIN (Person INNER JOIN MasterPerson 
ON Person.IDPerson = MasterPerson.IDPerson) 
ON PersonDays.Location = MasterPerson.Location
WHERE PersonDays.DAYS = FORMAT(PERSON.DATE, 'dddd');