Access DB design

[bigal.nz]

Hello,

A bit of a newbie question here about design.

I have inherited a flat database with one table (tbl_main). This table contains a record of events (Event_ID unique number) and each event has a date, time, person, address and a few other personal details.

Each record on the table should be emailed to one of two different organisations.

I am thinking I would have two buttons on the main screen "email to organisation 1" and "email to organisation 2" each time either of these buttons are clicked a report checks for all the records not previously emailed to that organisation and they are emailed.

To keep track of which records have been emailed I was thinking I would create "tbl_emailed" which would have:

Event_ID, Emailed_Date_Org1, Emailed_Date_Org2

I assume that every time I add a record to tbl_main it would need to insert any new events to tbl_emailed and then when the buttons are clicked it would need to query tbl_emailed

Is this the correct approach?

Cheers in advance

-Al

[SamT]

I take it that you do not have nor can you create a Date_Modified field in the main table.

Then your tbl_emailed would be two single field tables tbl_org1_EmailDate and tbl_org2_EmailDate. Then you can either:

Always replace the first record when an email event occurs, OR
Keep a record of all dates and only reference the last

The advantage is that the code to create and record the sending of emails is identical in all cases, except that the orgName parameter has different values. Therefore only one such set of procedures is needed:


Sub Button1_Click()
SendEmailReport "org1"
End Sub

Sub Button2_Click()
SendEmailReport "org"
End Sub



Sub SendEmailReport(orgName as String)
'
End sub

[bigal.nz]

I take it that you do not have nor can you create a Date_Modified field in the main table.

Then your tbl_emailed would be two single field tables tbl_org1_EmailDate and tbl_org2_EmailDate. Then you can either:

Always replace the first record when an email event occurs, OR
Keep a record of all dates and only reference the last

The advantage is that the code to create and record the sending of emails is identical in all cases, except that the orgName parameter has different values. Therefore only one such set of procedures is needed:


Sub Button1_Click()
SendEmailReport "org1"
End Sub

Sub Button2_Click()
SendEmailReport "org"
End Sub



Sub SendEmailReport(orgName as String)
'
End sub

I could create it in the main table - but the main table is getting VERY large in terms of the number of fields.

With your tbl_emailed table do you also record somewhere which record(s) were emailed in a given report run?

Cheers

-Al

[SamT]

do you also record somewhere which record(s) were emailed
Why? That would require a relational, or separate DataBase,

What I suggested is just two additional flat Databases. The kind that is easy to put on a Worksheet

All records generated or changed since the last email are sent this time.


the main table is getting VERY large in terms of the number of fields.

That is the problem with flat DB's. . . they grow and grow until they prevent everything from working.

What are the chances of breaking it into a relational database? And transporting all the data. And redesigning all the clients. And rewriting all the queries and reports. Uh, never mind.

Seriously, you're uploading *.xls workbooks, are you still using equally old servers? Expect them to die any day. My main computer was a 2002 Workstation, it died last month. I bought a bare bones 3003 server to replace it. Really cheap, But I have built lots of computers since 1979. It only booted two times for me. It burned up 3 capacitors on the motherboard.

Now I am looking at either a refurbished server or a refurbished workstation, each for less than $250USD and both will run Windows 10 64bit. Both have an eSATA port for my external drive, a 256GB SSD, and 4gig of RAM. Both retailed new for >$2000USD.

[bigal.nz]

You need to record which records were emailed, so when you do the next report run you dont end up emailing records which have already been emailed?

[SamT]

If you have a record of the last date a report was emailed and you have record of when records were last updated, just compare the two. Are there no date fields in the DB?


Unless I am misunderstanding how your database is used.