I have a several tables that I have joined together to get raw data.
Some information about my query in the most basic form:
- Table1 has a column [FacilityID] that is a unique identifier in that table (as well as several other columns, that are unimportant to the question).
- Table2 has a column [FacilityID] that matches Table1.[FacilityID], but not all of the records from Table1.[FacilityID] are found in Table2.[FacilityID]
- Because of this, Table1.[FacilityID] is Leftjoined to Table2.[FacilityID].
- Table2 has additional columns [ActivityCode], [ActivityDate], [ActivityEval?], and [ActivityOutcome]
- Table2.[ActivityOutcome] is only populated if Table2.[ActivityEval?] = "Yes"
- Table2.[ActivityCode] contains several string values, all nonnumeric.
- For simplicity and purposes of this example, Table2.[ActivityCode] contains values "EVALA", "EVALB", "EVALC", "EVALD", "EVALE", "EVALF", "EVALG", "EVALH", "EVALI", "EVALJ", "NOEVALA", "NOEVALB, "NOEVALC", and "NOEVALD" (not in that order, nor with the same prefixes or suffixes)
Right now my query is pulling: Table1.[FacilityID], Table2.[ActivityCode], Table2.[ActivityOutcome], Where (Table2.[ActivityDate] is between a begin date and end date) And Table2.[ActivityEval?] = "Yes"
My problem lies within the codes listed in Table2.[ActivityCode].
- Codes: EVALA, EVALB, EVALF, EVALG, EVALH, EVALI, and EVALJ can all stand on their own and are counted as a single event
- Codes: EVALC, EVALD, and EVALE cannot be counted as a single event, as they would only happen with at least 1 code from above.
- If EVALA, EVALB, and EVALC are all entered within the timeframe set by the query, then an EVAL3 event has taken place.
- If EVALA, EVALB, EVALC, EVALD, and EVALE are all entered within the timeframe set by the query, then an EVAL5 event has taken place.
I need a calculated field in my query to list out either an EVAL3 or EVAL5 event, or list out the individual codes from Table2.[ActivityCode]. This is to help our data entry folks quickly check if all their codes are entered into the system for credit. (We've been having a hard time getting consistent data entry with thousands of data points entered every year, by hundreds of people.)
I cannot, for the life of me, find how to complete this task out on the google.
Can anyone help me? Will I just end up with a Crosstab query that I have to filter in Excel? I really want this to happen all in one fell swoop, so I don't spend a "billion" hours each time the data is pulled refiltering in Excel.
I'm not the best with SQL or VBA, but I definitely try my damnedest to understand. I've tried using the Expression Builder and Concatrelated, but I am not getting the results that I want. That's why I'm finally turning here.
It's an access db, so yes, it runs on SQL. I don't necessarily need a single row, unless the 3 or 5 criteria are true and there are no other codes. Say for 1 FacilityID EVAL3 is TRUE, but they also have an EVALD, but not an EVALE, I want one row for EVAL3 and another for EVALD. Once an EVALE is added for the facility, I only want one row for the facility to show EVAL5. Any other EVAL codes should be listed out on separate rows.
Also, the ActivityCode may not be unique for the FacilityID in the query. There may be times that an EVALA occurs as part of an EVAL3 or EVAL5 event, but later in the timespan pulled for the query (usually a year) another EVALA occurs because of new information about or a change at the Facility. In this case the new EVALA is acceptable and should be counted in the activities for that facility, but not counted as part of the EVAL3 or EVAL5 event.