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