The following query inserts a record into my Email List table. But I want it to only insert if the values don't already exist. Please note that this is done in VBA and that I'm not inserting them from another table or a form. The data is retrieved from a text file.

INSERT INTO [EMAIL LIST]
(
[Email Address],
[Report],
[To],
[Cc],
[Bcc]
)

VALUES
(
'Someone@Something.Com',
'Report Name',
True,
False,
False
);


In Oracle, I believe you can Select the values from a DUAL table, even though the values are not actually on the table. The following code shows how this would be done.

INSERT INTO [EMAIL LIST]
(
[Email Address],
[Report],
[To],
[Cc],
[Bcc]
)
SELECT
'Someone@something.com',
'Report Name',
True,
False,
False
FROM DUAL
WHERE NOT EXISTS (
select *
from [Email List]
where
[Email Address] = 'Someone@something.com' AND
[Report] = 'Report Name' AND
[To] = True AND
[Cc] = False AND
[Bcc] = False
);

The problem with this is that in Access the "Dual" table does not exist. Would anyone happen to know of a workaround, or better, what table in Access is equivalent to Dual?

Thank you for your help.


PS: Admins who may have seen this in the Query section. I deleted the thread from the query section so this is not a duplication/cross-post. I just decided to move it into the VBA section since my code is not based on an already existing table. Again, the post that was previously in the Query section of this forum no longer exists.

Do you want to prevent duplication of email addresses? Or do you want to prevent the duplication of email/report combinations?

Sergeant-
For some reason, some users are both the To and the CC of a report. This means it can't be an address/report combo, it has to be the whole record. I don't know why they would do this (maybe they delete certain files, but would like two sent to them), but, I just want a whole distinct table and would rather do the error checking when inserting, instead of pulling the data.

I want to prevent duplications of an entire record. If it comes downt to it I'll just insert the distinct records from this table into a new table... delete the current table... and then reinsert the distinct records back into this table.

I'm just curious if there was another table in Access similar to Dual.

Sorry this is not a complete answer, but...
Have you considered importing the text file to a temporary table and then using queries to append to the main table? (deplete the temporary table after each import/append operation)
-or-
Storing email addresses in one table and reports in another, and making a link table where you would add records to connect certain people to certain reports?

This could be done in a number of ways, depending on which Normal Form you wish your tables to be and how you organize your tables.

I have two tables listed below with their fields-
[Email List]
Email Address, Report, To, Cc, Bcc

[Report List]
Report, Subject, Message Text, Edit Message, Object Type, Object Name, Output Format

Email List is a list of all the email addresses for a certain report. Report List has only only one record for each report. I believe this is in 3NF, regardless, it works and they are joined through a query by the Report.

I will just do my delete and insert distinct on my temp table as I said above. Thanks for the try. I was really more concerned with the Dual table.

Why would you not simply define a primary key or unique index to prevent duplicates?

Why would you not simply define a primary key or unique index to prevent duplicates?

Using 2 tables to save space, a table could have records like this:

Email Address Report To Cc Bcc
JoeSchmoe@No.com Morning Report Yes No No
JoeSchmoe@No.com Afternoon Report No Yes No
JoeMeedle@No.com Morning Report No Yes No


So how would that primary key idea work?

I know that Pat Hartman will come back to that, but wouldn't this work?
tblReports
RptID (PK)
RptName (No Duplicates)

tblEmail
EmailID (PK)
EmailAddress (No Duplicates)

tblDistro
EmailID (PK, FK-tblEmail)
RptID (PK, FK-tblReports)
SendTo (PK)
SendCC (PK)
SendBCC (PK)

No I don't think that would work. Not only is it a waste of space, it's slower than having two tables when performing searches.

This is okay:
Email Address Report To Cc Bcc
JoeSchmoe@No.com Morning Report Yes No No
JoeSchmoe@No.com Afternoon Report No Yes No
JoeMeedle@No.com Morning Report No Yes No

This is not okay:
Email Address Report To Cc Bcc
JoeSchmoe@No.com Morning Report Yes No No
JoeSchmoe@No.com Morning Report Yes No No
JoeSchmoe@No.com Morning Report Yes No No
JoeSchmoe@No.com Afternoon Report No Yes No
JoeMeedle@No.com Morning Report No Yes No

Instead of using primary keys. All I need is something to check if a row already exists before inserting a new row. I just want to get rid of duplicate records, but still keep the original. I'm thinking the fastest way is going to be to insert the records and then delete duplicates, probably using another table.

This is because I'm looping. If I delete after the records are inserted, it will prevent the program from performing a search each time a new record is inserted. So if I have 50 records I want to insert, it'd probably be better to use one query to check after all the records are loaded, instead of performing 50 searches before loading them.

No I don't think that would work. Not only is it a waste of space, it's slower than having two tables when performing searches.

This is okay:
Email Address Report To Cc Bcc
JoeSchmoe@No.com Morning Report Yes No No
JoeSchmoe@No.com Afternoon Report No Yes No
JoeMeedle@No.com Morning Report No Yes No

This is not okay:
Email Address Report To Cc Bcc
JoeSchmoe@No.com Morning Report Yes No No
JoeSchmoe@No.com Morning Report Yes No No
JoeSchmoe@No.com Morning Report Yes No No
JoeSchmoe@No.com Afternoon Report No Yes No
JoeMeedle@No.com Morning Report No Yes No

Instead of using primary keys. All I need is something to check if a row already exists before inserting a new row. I just want to get rid of duplicate records, but still keep the original. I'm thinking the fastest way is going to be to insert the records and then delete duplicates, probably using another table.

This is because I'm looping. If I delete after the records are inserted, it will prevent the program from performing a search each time a new record is inserted. So if I have 50 records I want to insert, it'd probably be better to use one query to check after all the records are loaded, instead of performing 50 searches before loading them.

Pat:
After looking at Sergeant's post again, did you mean a concatenated key for the whole row?

When I see primary key.. I think one field. But I guess a concatenated key would work. I just didn't think it was possible to have more than one field be a primary key in Access.

Instead of using primary keys. All I need is something to check if a row already exists before inserting a new row. You cannot perform this faster than the db engine can. Always leave declarative RI to the db engine whenever possible.
did you mean a concatenated key for the whole row? - no. A concatenated key would violate not only first normal form but second and third as well. I ment a multi-column key. Access (Jet) supports keys/indexes containing up to 10 columns. Other RDBMS' allow more.

To create a multi-field pk, select the first column and while holding the cntl key, select up to 9 more columns. When all necessary columns are selected, press the key icon.

In your code, you should trap for the duplicate record error and ignore it.

Here's a sub I use to insert attendance records. It ignores duplicates and counts the records that get inserted.
Public Function CreateAttendanceRecords(ctlRef As ListBox) As Integer
On Error GoTo Err_CreateAttendanceRecords

Dim i As Variant
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Dim RecCount As Integer

Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qAttendance
Set rst = qd.OpenRecordset
RecCount = 0
For Each i In ctlRef.ItemsSelected
rst.AddNew
rst!MailingListID = ctlRef.ItemData(i)
rst!AttendanceDate = Me.txtToday
rst.Update
RecCount = RecCount + 1
Next i
Set rst = Nothing
Set qd = Nothing
CreateAttendanceRecords = RecCount

Exit_CreateAttendanceRecords:
Exit Function

Err_CreateAttendanceRecords:
Select Case Err.Number
Case 3022 'ignore duplicate keys
RecCount = RecCount - 1
Resume Next
Case Else
MsgBox Err.Number & "-" & Err.Description
Resume Exit_CreateAttendanceRecords
End Select

End Function

A concatenated key would violate not only first normal form but second and third as well. I ment a multi-column key.

Please forgive me for my ignorance, but I thought a concatenated key is a multi-column key. At least that's how I think it was taught. A concatenated, or compound, key is made up of more than one field of a table to ensure it's uniqueness.

So if all the fields were keys, they would more, or less, be one big concatenated key.

I think we are talking semantics here. Concatenation generally means stringing together multiple fields to end up with a single string. Compound is a better term for a primary key that is made up of several separate fields.

Ultimately when the key is processed, it is quite likely that Jet actually builds a concatenated value. The point I was making is that YOU should not build a concatenated key and store it because that would violate the first three normal forms.

So if all the fields were keys, they would more, or less, be one big concatenated key. That's not the way it works. Defining each field as a key doesn't help with this problem at all. It is the combination of several fields that defines uniqueness.

i think we're saying the same thing, just in different ways. and i'm probably not saying it as well as i would like, because i don't really have the time to say everything i want (or to think about how i would like to say it). regardless, I understand what you are saying and i appreciate you spending as much time as you have on this pat and sergeant.

maybe the microsoft crue can add a feature that automatically rids the tables of duplicate data.

Don't look for that feature any time soon. You are responsible for the cleanliness of your own data.

haha i know ... a little far fetched =)

especially when we have a whole thread based on other access improvements

The problem with this is that in Access the "Dual" table does not exist. Would anyone happen to know of a workaround, or better, what table in Access is equivalent to Dual?

I would also like to know an anwser to the question if there is an access equivalent to the dual table in oracle.

Darbid,

The JET engine lets you omit the From clause:

Select 'a', 'b', 'c'

is perfectly legal and returns the expected value.

ORACLE's Dual table:

Dual
====
X

Let's you do the following:

Select 'a', 'b', 'c' From Dual

And it just returns the one row.

You could make your own Dual table for Access, but there's really no need
to do that. You could also experiment by adding more rows, but it is only
intended to have ONE row.

Wayne

Darbid,

The JET engine lets you omit the From clause:

Select 'a', 'b', 'c'

is perfectly legal and returns the expected value.

ORACLE's Dual table:

Thanks Wayne. For those of you that are beginners like me especially with sql stuff here is what works for me


select shortname, lastname from
(SELECT Top 1 1 as orderfield, " " as shortname, " " as lastname from [tbl_Bearbeiter]
UNION
SELECT Top 1 2 as orderfield, "all" as shortname, " " as lastname from [tbl_Bearbeiter]
UNION
SELECT 3 as orderfield, [tbl_Bearbeiter].[wshortname] as shortname, [tbl_Bearbeiter].[wlastname] as lastname FROM [tbl_Bearbeiter] )
ORDER BY orderfield, shortname, lastname;



So the "SELECT 3 ....." part is what I originally had.

Now I have first a blank line and the I have ALL and then I have the information from the table.