Delete only unused related records in another table

[Trevor_S]

I have used Excel for many years, but am new to Access!

I am creating a database on which to log invoices received and track progress with authorisation, disputes, etc. through to eventual payment. As each invoice could have multiple progress updates relating to it, and each progress update could relate to multiple invoices, I'm setting up three tables - an invoices table, a progress update table and a relationships table. The final one simply has three fields - a Relationship ID (allocated by Access) and the Invoice and Progress Update IDs - to allow each progress update to only be stored once, but be associated with the relevant invoices. There's also a form showing the invoice fields with a subform showing the progress updates related to that invoice.

Some progress update records would include attachments, so I'm thinking that in time I'll need to start deleting records that are no longer needed. Invoice records are easy - deletion would be a set period after payment. But is there a way to delete related progress update records only if there are no other remaining invoice records that relate to them?

Thanks!

 

[welshgasman]

A InvoiceProgress table would link Invoices and Progress, but would have one record for each link.?
So if two invoices were 'Ready for Dispatch", there would be two records in the table, one for each invoice.?

Do not store the attachments in the DB, just links to a folder where the attachments are located.

 

[Trevor_S]

Thanks - and apologies for the delay in replying.
The main reason for wanting multiple tables was due to the filesizes of the potential attachment. If a single email is sent to chase progress on several invoices, I only want to store the email file once, rather than a copy on each invoice. But yes, saving it in a shared folder and adding links on each invoice record would work too. Also without any attachments in the database, I shouldn't have to delete records for paid invoices, I can just have a marker field.

 

[Micron]

Agree with not storing attachments in Access tables. It matters not if you put them all in 1 or over several; you can quickly reach the max file size.
Have to wonder why you don't just update the status of an invoice as you go. It cannot be in 2 states at once, yes? Or maybe you want to keep a rolling history, showing details about each stage. In that case, I agree that a status history table would be the way to go. This assumes you know something about normalization - your Excel comment is kind of a red flag.

 

[Trevor_S]

Thanks. Just to clarify, this is for invoices from certain suppliers that we receive and need to pay. Most will be paid within the supplier's terms, and won't be recorded here at all. But if I'm contacted by a supplier about an overdue unpaid invoice, I ask them for a copy and trace who in my organisation is responsible for authorising / processing it. Depending on the reason for the delay (e.g. just oversight, or some dispute with the supplier) there could be a considerable amount of correspondence in respect of each invoice, until it is eventually paid by us, or cancelled by the supplier. During this time I need to keep a full trail of the correspondence, as once the invoice is a set number of weeks overdue, the supplier is entitled to request independent arbitration.

At the moment, I create an outlook task for each invoice I investigate. I copy all emails and their attachments as they are sent or received into the body of the relevant task, and log details of any phone calls. This works fine - but it means that where I'm contacting one person about a number of invoices, I'm copying the same email into multiple outlook tasks. Clearly from both a time and a data storage perspective, this isn't the most efficient option! It's not feasible to use a single outlook task for multiple invoices, as although initial emails may cover them all, as the investigation progresses, later emails may not.

So this is why I thought of a database - one table for the invoices, one for the investigation updates (whether that is the email/attachment itself, or a link to a file). Because the relationship between these tables would essentially be "many to many", I thought of the third table in the middle, essentially converting it to two relationships - a "many to one" and a "one to many". My lack of access experience is largely because it's not been included in office packages at previous employers. It's not a lack of understanding of efficient data storage - I've often had to build what is effectively a relational database in excel simply because I haven't had access!

The way that I would have dealt with this had access not been available would be to have a separate Excel worksheet for each of the three tables, and write some VBA with a userform to create new records, allow emails to be selected from outlook and copied across to update records. The userform would also have contained either a large text box or browser window to display the emails relating to the invoice being queried. However ... it just seems the sort of thing that access was more designed to do.

The only access problem I had was how the updates table would know when a stored email record was no longer needed. An invoice table record could be deleted soon after it had been paid/cancelled - but an update table record would need to remain until the last invoice that referenced it was deleted. I read about "cascade delete", but didn't think that it would follow through the middle table correctly!

 

[Micron]

Thanks for the detailed explanation - really helps. I may be a bit off here, but this is what I'm concluding:
An investigation is about 1 invoice. If there are 2 or more concurrent 'complaints' from the same supplier it is 2 investigations because each can take different paths and arrive at different conclusions. Thus tblInvestigations is the one side of the investigation process steps/progress table (tblInvestProg). The progress table contains a record for each activity/step/notable comment/etc that would constitute a record for a single investigation. That may not be what you plan, but it is probably the right way to go.

You can drag an email into a folder, which if clicked will open as a normal email. The file name will take on the subject line if I'm not mistaken, so it would be really good if you could train suppliers to send email only about a specific invoice AND preface the subject line with the invoice number, or if you're providing an identifier such as a case number, use that or some other data that makes it easier to discern between emails in your Outlook inbox list and the folder(s). Since they have the want, they should be willing to comply with your request, especially after you tell them (OK maybe after a few times) that failure to comply will delay the process as it adds extra work at your end. Now you would have a file path associated with this investigation activity. You use the FileDialog object to choose the identified file and store the path in either the investigation step record, or if preferred, tblInvestFiles. Using a separate table would allow you to have several files (messages, documents) associated with the same step rather than repeating all the other fields related to that step.

How you manage data at the end of an investigation would be up to you but I can say the usual response to deleting records is "don't" unless you have a very pressing need to do so. Not seeing completed investigations showing up in queries/reports is not a good enough reason. An investigation and/or a step should have a date field for completion dates (e.g. ComplDate) rather than a simple checkbox field since a check provides no other valid info. Queries eliminate completed records by specifying that the date field is not null.
Hope some of that helps.

 

[Trevor_S]

Thanks for your suggestions! Regarding deletion of records, I won't now be doing that. I'd only thought that I may need to for total filesize reasons when I'd been planning to attach files to the database. Having a completed date field is better.