sqlms-accessinner-join

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 :

IDPERSON PERSONNAME DATE LOCATION DAYS
1000 Rey 29-May-24 A Wednesday
1000 Rey 30-May-24 A Thursday
1001 Michael 29-May-24 B Wednesday
1001 Michael 30-May-24 B Thursday

Table PERSON

IDPERSON DATE
1000 29-May-24
1000 30-May-24
1001 29-May-24
1001 30-May-24

Table MASTERPERSON

IDPERSON PERSONNAME LOCATION
1000 Rey A
1001 Michael B

Table PERSONDAYS

LOCATION DAYS STATUS
A Wednesday A10
A Thursday A27
B Wednesday B18
B Thursday B29

Desired Result

IDPERSON PERSONNAME DATE LOCATION DAYS STATUS
1000 Rey 29-May-24 A Wednesday A10
1000 Rey 30-May-24 A Thursday A27
1001 Michael 29-May-24 B Wednesday B18
1001 Michael 30-May-24 B Thursday B29

Solution

  • 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');