Removing Duplicates / Combining Query Results

[Huey462]

I have a database that has two tables that we are concerned with. One is for People (tblAlpha)and the other is for Appointments (tblSchedule). The designer (ages ago and unknown) designed a calendar that would use 14 queries to do some date math and link the results with tblAlpha and ultimately displayed on a form.

The schedulers like to work with month long timeframes and when I tried to add another 15 queries it locked up access.

After some fumbling around I decided to cut out the middleman on the queries and just link the two tables together and create 31 fields similar to the one below. The start date is selected by the user and stored as DStamp in the tblMainInformation.

Appt01: IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1") And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")=-1,[ApptComment],"")
(through)
Appt31: IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+30 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+30=-1,[ApptComment],"")

This runs and the table/datasheet is displayed in well under a second. The new problem is that if an individual has more than one appointment during the timeframe, they will have more than one record (right term?) on the datasheet.

It would seem to be a fairly easy procedure to have Access combine all of the records with the same specified/unique field value (i.e. SSN)…I just don’t know how to do it.

............1 Aug........2 Aug
John, Doe...Dental 0900
John, Doe................Oil Change 0730
 
to
 
............1 Aug........2 Aug
John, Doe...Dental 0900..Oil Change 0730 
 

This was posted here as well, but I don’t think it is getting answered.
http://www.access-programmers.co.uk/...78#post1370078

[zmbd]

I know that you've attempted to tell us the nature of the database; however, you've really not told us enough.

On the surface it sounds as if you might need a cross-tab-query; however, most likely your entire premise is flawed without an understanding of the thought process your developer used...

The problem for us is that you would have to go into great depth about the forms and each of the queries used in order for us to be able to help as it sounds like you've attempted to really alter a project at its foundation.

Instead of adding your additional queries, you might want to take a very careful look at the existing queries and determine how they were written and if they can be modified to accomplish your new goal.

OR

You might want to check out the MS Calendar project... the overview is in our insights articles here: MS Calendar Overview this is a project that ADezii developed and is kind enough to share with our membership. I highly advise reading thru this link and then searching on Bytes.com (use the search box at the top of our page (^_^) next to the orange magnifying glass button) using Adezii Calendar as key words) Adezii has answered and modified the project many times and I'm sure that if the base project doesn't meet your needs that one of the modifications will... although, next to Outlook, the basic project code is really well formed and very easy to work with!

[Huey462]

First, let me start by thanking you for the response. I will have to download the MS Calendar at home because it is blocked from work.

Here is a breakdown of what happens:

-The user (scheduler) selects a start date and then opens the desired calendar (7 day, 14 day or 31 Day).

(Original Version)
- The queries check to see if each appointment (tblSchedule) is on each of the days. The formula is checking to see if the start date is before or equal to the start date (each subsequent formula adds a +1 to the date) and that the end date is greater than or equal to the start date.

-All the queries are then linked to the table that contains the individual’s information (tblAlpha). qryScheduleDay01 would be the start date’s appointments, qryScheduleDay02 would be the next day’s and so on.

The problem with this set up is that when more than 20 or so queries are linked like this, Access locks up.

(Modified Version)
-The two tables are linked in a query with a one to many relationship. There are 31 expressions (right term?) that check to see if any appointments are due on that day (Start Date, Start Date +1 and so on).

-While this is much much faster than the original version (which could take up to a minute to do the math and open the 14 day calendar…the modified version is just a couple of seconds) if an individual has more than one appointment during the timeframe they will be listed more than once in the results.

Because I am completely self-taught and have zero schooling or training on coding I am stuck. I’ve tried to look at “concatenate” functions to combine records that have the same specified criteria (SSN, Last name etc.) but they may as well be written in Egyptian hieroglyphics.

I suspect it would be easier to remove the tblSchedule from the query and just rewrite the following to search in there, adding a WHERE SSN matches the tblSchedSSN…but I’m dangerously close to common sense and that seldom works out.

Appt01: IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1") And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")=-1,[ApptComment],Null)

[zmbd]

Just so that we can actually see how your project was working.

open the query for the "seven day" in SQL view.
Click on the [CODE/] button in the post toolbar and then cut and paste your SQL script between the [code] [/code] tags.
It would good of you to format the SQL ( > Before Posting (VBA or SQL) Code ) but for a first time post we give a lot of wiggle room (^_^)

Let us see if there are not some ways to stream-line what your developer was doing....

Also, if you use the [ADVANCED] button at the bottom of the posting box, you can upload a screen-shot of your form for the "seven-day" Now we do understand sensitive data so if you need to, take it into paint or the like and erase the sensitive parts...

On a side note, and this would need its own thread for depth/detail: you mention SSN... it is Rarely a good idea to have these in plain text. At least an MD5-hash should be used and the actuall SSN never stored. If you do have to store these then either use the file encryption provided within ACC or look into the encryption methods discussed here at bytes

[Huey462]

Attached are the screen shots of the daily query and how they are associated with the tblAlphaList

Below is the SQL code

SELECT 
   [Last] & ", " & [First] & " " & [MI] & " " & [Suffix] 
      AS FullName
   , tblAlphaList.Office
   , tblAlphaList.Status, tblAlphaList.Shift
   , tblAlphaList.Shift12, tblAlphaList.TrainingCheck03
   , tblAlphaList.TrainingCheck04, tblAlphaList.TrainingCheck05
   , tblAlphaList.TrainingCheck06, tblAlphaList.TrainingCheck07
   , tblAlphaList.SSN, tblAlphaList.Last
   , tblAlphaList.First, tblAlphaList.MI
   , tblAlphaList.Suffix, tblAlphaList.LoadCrewNumber
   , tblAlphaList.LoadTeamPosition, tblAlphaList.SkillLevel
   , tblAlphaList.Rank
   , [qryScheduleDay01]![Apptcomment] 
      AS Appt1
   , [qryScheduleDay02]![Apptcomment] 
      AS Appt2
   , [qryScheduleDay03]![Apptcomment] 
      AS Appt3
   , [qryScheduleDay04]![Apptcomment] 
      AS Appt4
   , [qryScheduleDay05]![Apptcomment] 
      AS Appt5
   , [qryScheduleDay06]![Apptcomment] 
      AS Appt6, 
   , [qryScheduleDay07]![Apptcomment] 
      AS Appt7
   , [qryScheduleDay08]![Apptcomment] 
      AS Appt8
   , [qryScheduleDay09]![Apptcomment] 
      AS Appt9
   , [qryScheduleDay10]![Apptcomment] 
      AS Appt10
   , [qryScheduleDay11]![Apptcomment] 
      AS Appt11
   , [qryScheduleDay12]![Apptcomment] 
      AS Appt12
   , [qryScheduleDay13]![Apptcomment] 
      AS Appt13
   , [qryScheduleDay14]![Apptcomment] 
      AS Appt14
FROM (((((((((((((
   tblAlphaList LEFT JOIN qryScheduleDay01 
      ON tblAlphaList.SSN = qryScheduleDay01.[tblSchedSSN]) 
      LEFT JOIN qryScheduleDay03 
         ON tblAlphaList.SSN = qryScheduleDay03.[tblSchedSSN]) 
         LEFT JOIN qryScheduleDay02 
            ON tblAlphaList.SSN = qryScheduleDay02.[tblSchedSSN]) 
            LEFT JOIN qryScheduleDay04 
               ON tblAlphaList.SSN = qryScheduleDay04.[tblSchedSSN]) 
               LEFT JOIN qryScheduleDay05 
                  ON tblAlphaList.SSN = qryScheduleDay05.[tblSchedSSN]) 
                  LEFT JOIN qryScheduleDay06 
                     ON tblAlphaList.SSN = qryScheduleDay06.[tblSchedSSN]) 
                     LEFT JOIN qryScheduleDay07 
                        ON tblAlphaList.SSN = qryScheduleDay07.[tblSchedSSN]) 
                        LEFT JOIN qryScheduleDay08 
                           ON tblAlphaList.SSN = qryScheduleDay08.[tblSchedSSN]) 
                           LEFT JOIN qryScheduleDay09 
                              ON tblAlphaList.SSN = qryScheduleDay09.[tblSchedSSN]) 
                              LEFT JOIN qryScheduleDay10 
                                 ON tblAlphaList.SSN = qryScheduleDay10.[tblSchedSSN]) 
                                 LEFT JOIN qryScheduleDay11 
                                    ON tblAlphaList.SSN = qryScheduleDay11.[tblSchedSSN]) 
                                    LEFT JOIN qryScheduleDay12 
                                       ON tblAlphaList.SSN = qryScheduleDay12.[tblSchedSSN]) 
                                       LEFT JOIN qryScheduleDay13 
                                          ON tblAlphaList.SSN = qryScheduleDay13.[tblSchedSSN]) 
                                             LEFT JOIN qryScheduleDay14 
                                                ON tblAlphaList.SSN = qryScheduleDay14.[tblSchedSSN]
WHERE 
   (((tblAlphaList.Status)="Assigned"))
ORDER BY 
   tblAlphaList.Last
   , tblAlphaList.First
   , tblAlphaList.MI;

If it would help, the database is currently loaded with fake names and SSNs so I can upload that as well without violating any PII regulations.

[zmbd]

It appears that your database is not normalized hence all of the extra queries and hardship.

How is the data displayed to the users?

[Huey462]

Attached are screenshots of the reports (7 and 30 day), both showing the duplication

[zmbd]

OK, the pictures are a tad small; however I think I see what you are talking about... the purple fields are one assignment for the individual, and the row below that has that same individual's name, and the next assignment in that row instead of merged with the row above.
The columns have the dates...
Check out Allen Browne's Crosstab query... Allen Brown CrossTab Query

In the meantime... it will take awhile to digest.

[Huey462]

Thank you again, I'll look at Mr. Brown's website. Hopefully it will make sense =D

[Huey462]

Would it be possible to use the GROUP_CONCAT function with the following query or maybe a DISTINCT clause?

This is the query I was working with that directly links the personnel and appointments tables rather than have a query for each day like the previous post.

SELECT tblAlphaList.Last, tblAlphaList.First, tblAlphaList.Status, IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1") And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")=-1,[ApptComment],"") AS Appt01, IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+1 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+1=-1,[ApptComment],"") AS Appt02, IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+2 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+2=-1,[ApptComment],"") AS Appt03, IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+3 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+3=-1,[ApptComment],"") AS Appt04, IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+4 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+4=-1,[ApptComment],"") AS Appt05, IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+5 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+5=-1,[ApptComment],"") AS Appt06, IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+6 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+6=-1,[ApptComment],"") AS Appt07
FROM tblAlphaList LEFT JOIN tblSchedule ON tblAlphaList.SSN = tblSchedule.tblSchedSSN
GROUP BY tblAlphaList.Last, tblAlphaList.First, tblAlphaList.Status, IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1") And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")=-1,[ApptComment],""), IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+1 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+1=-1,[ApptComment],""), IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+2 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+2=-1,[ApptComment],""), IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+3 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+3=-1,[ApptComment],""), IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+4 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+4=-1,[ApptComment],""), IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+5 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+5=-1,[ApptComment],""), IIf([Scheduled]<=ELookUp("DStamp","tblMainInformation","[ID] = 1")+6 And [StopDt]>=ELookUp("DStamp","tblMainInformation","[ID] = 1")+6=-1,[ApptComment],"")
HAVING (((tblAlphaList.Status)="Assigned"))
ORDER BY tblAlphaList.Last, tblAlphaList.First;