Where Conditions in Tables

[Janiem1217]

Hi there – I was wondering if you can place a “where condition†within a table?

What I would like to do is import data that may contain duplicates for my
primary key – without creating a second record for the same id number –
Currently we allow borrowers to come in for 1 deposit per year – our program
has changed so that a borrower can come in twice per year – The file is very
large so I import the information from Excel into the table – if the ID
number is the same it kicks it out to a paste error table – Which is how we
monitor each borrower – if I remove the yes, no duplicates and indicate yes,
duplicates ok – I will lose the integrity of the database in that a borrower
could come in 4 times instead of only 2 –

I hope this makes sense – any help you can give is greatly appreciated!
Thanks!

 

[KARL DEWEY]

Why not allow duplicates but use an append query with DCount <2.

 

[Klatuu]

To do this correctly, you can't import directly into your main table. You
can't put a Where condition on the table itself, you have to do it through a
query to eliminate the duplicates.

I would suggest you create an import table that has the same structure as
your main table and import the excel data into that table, then use an append
query that filters out records that already exist in the main table. You do
that by using a subquery.

So the steps are:
Run a Delete query to clear out data in the import table from the previous
import.
Run a TransferSpreadsheet to import the Excel data into the import table.
Run an Append query to add the new records to the main table.

To filter out the existing records, you use a Where condition in your append
query that includes subquery that determines whether the id is already in the
table:
WHERE NOT IN (SELECT id FROM MainTable)

 

[Klatuu]

Using a DCount in a query is very inefficient.
That would only exclude id's where there are two rows with the same id in
the imported data.

 

[jerryb123]

I would suggest creating a "Transaction Table" with a record for each
transaction, so that Borrower 1 could have Transaction 1 and Transaction 2,
with a date field for each transaction. Then you could run a query to
determine how many records they have in the Transaction Table, and if they
have more than 1 in the past year, you could refuse them. (I'm assuming you
want the year to be a "moving year" so that if they tried to borrow more than
once between last June and now that would not be OK, but if they tried to
borrow more than once since the beginning of last year, that would be OK.)

 

[Klatuu]

What is a Transfer Table?

 

[Janiem1217]

That's what I thought - Thank you all so much for the quick responses -

 

[jerryb123]

I was just suggesting that instead of keeping her data in one monstrous table
with multiple line items for an individual, she might want to figure a way to
import the data and query it out so she will have a "Borrower" table that
lists all borrowers, with a Primary Key on it, and a "Transactions" table
that lists all transactions for each borrower, along with the date of each
transaction. That way people can come in more than twice, but they will only
be rejected if both of their transactions are in the last year.