Help with a confusing update query

[bchi99]

Hello, I need help with what seems to me like its confusing but maybe for the pro’s out there, it’s easy.



I have a table called “Master” that is pulling in an Outlook folder and storing the emails as records. Each email has an unique ID number field. I would like to run an update query that I can link emails together sort of like parent and child emails.

The field that can marry all the emails together is a field called “Subject2”. For the family to be a true family, the parent email and all the child emails have to have the same Subject2 say for example they all have “america”. Also, the two children emails have the same string of text in their attachment fields such as "xyz".

Here is the full scenario:

- A user sends a parent email which has the following criteria: the subject is constant string of text “start” AND attachment is null and subject2 = america
- Child #1 email has the following criteria: subject is a constant string of text “upload” AND attachment is a string of text “xyz” AND subject2 = America
- Child #2 email has the following criteria: subject is a constant string of text “download” AND attachment is a string of text “xyz” AND subject2 = america

The subject I will always know, the attachment field for the children records I don’t know but they will always match and the subject2 field for all family members I don’t always know but they will always have matching strings of text.

I’d like to create an update query to enter in the for the parent record for field master.child the children record id’s and for the children records for the field master.parent the parent id.

Any help with this is much appreciated.

[kagoodwin13]

This is a bit of a novice answer, but I would definitely consider using another system than Access for your business requirements.

You have too many opportunities for failure in this process: What if someone changes the subject because they don't realize your tracking system works that way? What if someone misspells a word (Amreica, uplaod, downlaod)? Your system is hosed for a simple mistake.

Gmail does parent/child email management really well. It also does tracking and tagging very well. Even if you work at a large corporation (which is probably the case if you are using Outlook), you can create a free shared Gmail account that your department and your customers can use. You would have to properly communicate this change to your customers so that they know and trust the system.

You also might want to consider a Customer Relationship Management (CRM) system. This type of system is very common for sales teams, who heavily depend on keeping a good trail of communication for their customers. Rather than trying to track email files, you can copy/paste the content of emails into a customer note, and continue to update customer notes with each reply and response. You can create this sort of system in Access very easily, but keeping track of attachments would become cumbersome. Microsoft OneNote can also do this with attachments, but you would also need Microsoft SharePoint to use it on a department-wide level.

I do not believe this is one of Access's core competencies, and in addition, there are many solutions for this business requirement that already exist. Those solutions also aren't very expensive (Gmail is free!). Rather than wasting development time and money trying to re-invent the wheel, take the easy route and find a different solution.

[bchi99]

so here's the thing, they are emailing me from templates and the subject can't be mispelled.

[kagoodwin13]

One technique I've used before is using a parent ID field.

Each email conversation needs its own ID (ex. Convo12345)
Within each conversation ID, there needs to be a repeated convo level ID starting at a top level ID and going sequentially. In this example, I will assume 1000 is the top level (which allows up to 999 children).

Convo level ID 1000 has no parent ID (top level)
Convo level ID 1001 has parent ID of 1000 (reply to top level email)
Convo level ID 1002 has parent ID of 1001 (reply to reply)

Even in the use case of someone replying up the email chain rather than on the latest email in the conversation (happens ALL the time), you can still point to a parent ID.

Convo level 1017 has parent of 1001 (the 18th email in the chain replied to the 2nd email in the chain)

I hope that helps. I would still definitely consider another system, but maybe that will get you started.

[June7]

That is confusing.

Sounds like you want to do a self-join on the Master table. How do you know which records are parent and which are children? The attachment field?

The Subject2 value will be identical string for all family members?

Or maybe you just need to do a report that uses Sorting & Grouping features?

[bchi99]

THe parent will always have attachment as blank or null. the children will always have matching attachment in the form of a certain string of text. Subject2 will be identical among all family members. I'l really like to have the id's in their respective fields ex: parent has the id's of the children in master.child and the children records have the corresponding parent id in the master.parent. The reason why is b.c if there is a missing child, we know which once is missing and can correct it.

[June7]

If this is just to find out which families are missing a record, maybe just do an aggregate query that counts the records for each subject2. There should be 2 and only 2 child records?

SELECT subject2, Count(*) AS CountSubject FROM tablename GROUP BY subject2 HAVING Count(*) < 3;

[bchi99]

I think that might work, could you please help me with the syntax if i want to make it an update query to update master.childcount?

[June7]

Saving calculated data is usually a bad idea, especially aggregate data. It can be calculated when needed.

[bchi99]

June, thanks for your help. i guess what i'm getting at is if i have the calcuated data and if i can't see it next to the full display of records without the grouping enabled, it will be hard to see which child is missing. However if everything has a 2 next to it and if i see a record that doesn't have a count of 2, it will be easy for me to point out which child is missing. Sorry for being a pest and thanks.

[June7]

Can join the aggregate query to the original table.