Solved: Check if record exists

[Zack Barresse]

I made a table and a form. This is my second one doing - ever. So I am quite naive on Access and it's inner workings. Here is basically what I have...

Table:
Fields: Date, Report, Sampled, Field, Crop
There are 6 other fields that are the main data for the record. The above data cannot be duplicated though. There must be at least one field different for it to be a new record.

Form:
Same fields as above. I got a Close button and an Add Record button. I got those working alright.

What I'm looking for is when I hit the Add Record button (it already adds the data to the table wonderfully) I would like to validate the information in the form, by the fields listed above (under Table). Is there a way to do this? Would I need a Query? Any help would be appreciated. :yes

[Norie]

When you say the data can't be duplicated do you mean the individual fields or a combination of them all?

[mdmackillop]

Hi Zack,
I'm no Access expert, but what I have used to delete duplicates (not quite your question) is to cocatenate all relevant fields into one string in a query, which can then be used for comparison,
Regards
Malcolm

[Zack Barresse]

Thanks MD. I'm curious as how to do that. I haven't made but one cobbled query, which I was walked through.

Norie: I mean a combination of all fields. Some will be repeated, i.e. six samples with the same date, same sample ID, same sampler, different field.

[Norie]

firefytr

Any chance of some sample data?

I'm still not 100% clear on what you mean by a duplicate.

[mdmackillop]

Create a new field name in the query, join the fields eg
Cocat: [Select] & [Company] & .....

[Zack Barresse]

Sure. There are other tables/forms in the database. Is there anyway to just zip the form and table?

[mdmackillop]

Sure. There are other tables/forms in the database. Is there anyway to just zip the form and table?

You can copy them into a new database.

[Zack Barresse]

Okay, I think I got both the form and the table.

The three buttons I made myself, so I don't know if ANY of this is using best practices at all.

Let me explain that bottom button though (when you get it open, you'll see it on the form). It probably errors out right now. The point was that I generally get 6-10 samples back in one day. This continues for about a week, and this process only occurs 3-4 times per year. So when I get them, it's a few at once, then nothing.

The point to this was that the same information will be put into the form for each entry (record) except the actual chemical values and the field number. The rest should be the same. I tried getting the last record in there (which I think it does) and then tried clearing the chemical values and inputting new ones.

This doesn't work because it's still affecting the last record which it pulled the data from. Is there any way to get a NEW record pre-populated with those first values of the last record input?

[XLGibbs]

I believe this can be accomplished by setting the required non-duplicate fields as a primary key across all 5 or 6 columns needed...see attached.

[Zack Barresse]

Works beautifully!!!

Marking Solved! :yes

[Norie]

firefytr

Glad to see you've got a solution.:)

Gibbs

It's been a while since I've actually studied the structure of data for a database.

But I'm sure I can recall that it is a bad idea to have a primary key consisting of more than 1 field.

And also a bad idea to use actual data for a primary key.

A primary key should surely just be some sort of arbritratry unique identifier for each record.

Perhaps I'm wrong.:)

[XLGibbs]

Hmm. I am in charge of a 400 GB database work, designed by a MS educated and certified programmer, has actual data--typically customer account level information including the account number, and other/multiple columns acting as primary keys. I am gonna have to check with him, since he is ther resident expert at my disposal, there may be a reason that I am unaware of. You may be right, however, a primary key is used to uniquely identify a row of records and in most cases to prevent duplicates--In Zack's situation, no harm should come of it.

What would be the alternative>?

[Ken Puls]

There is an interesting discussion on this topic at XL-Dennis's site. Link to that discussion here (http://www.excelkb.com/instantforum41/Topic187-14-1.aspx).

:)

[Bob Phillips]

It's been a while since I've actually studied the structure of data for a database.

But I'm sure I can recall that it is a bad idea to have a primary key consisting of more than 1 field.

And also a bad idea to use actual data for a primary key.

A primary key should surely just be some sort of arbritratry unique identifier for each record.

Just read this thread, but my understanding of database theory is actually the opposite. The primary key should NOT be an arbitrary identifier, it should the data item that identifies the data. But it should be unique, that is why you often concatenate fields to produce a primary key, as a single field does not create that uniqueness (obvious example, Lastname, Firstname).

When I started in IT, autonumber ids were almost unheard of, keys were always data related, but they are much more common now. I even argued the case for auto-generated keys in the ExcelKB forum thread that Ken mentions later, which I quote below ...


When I started on database design, I followed the theory and created primary keys that were based upon data elements. Over time I have found a number of problems with that approach, and have since reverted to using autonumber Ids as my primary key. My two main problems were

- primary keys have to be unique, and the data was not always constructed in such a way that a single element is unique. Take a table of People or Persons. Using last name as the primary key is of no use, there are many Phillips' in the world, even in my family . I can make it more unique by concatening first name, but how unique is Bob Phillips? I would need something else to make it absolutely unique, which is difficult, but I already have a data management problem. Autonumbers, no problem.

- what if the structure of the data element changes? For instance, say we have an employee numbering system where we use a 5 digit number. The company expands and we need to use a new 7 digit numbering system. If the employee number is the key, not only do we have to change every row of data, the application changes are likely to be more fundamental than if it is just another data attribute.

I don't think that Dennis' point about gaps if you delete a row is particulalrly important for two reasons, the first being that it is an arbitrary number, so the sequence has no meaning whatsoever other than the order of adding to the database, and secondly, I never delete rows anyway, just mark them as deleted, deleting causes too many problems.

Split databases and replicated DBs are more of an issue, but that is true in every aspect of such databases, and the design has to be very clear of how to handle such issues.

As to not using Ids on warehouse DBs, because of potential performance issues, a warehouse is exactly where you need the big engine, so if an id causes problems, that suggests that the warehouse is under-powered, and will therefore always struggle to justify its existence and provide an RoI.

[geekgirlau]

Hey Zack,

Just a pointer in your ongoing road to Access development ...

Generally it's a good idea not to have spaces in field or table names (or queries or any other type of objects for that matter), and to take care with using any name that Access may confuse with a recognised constant, statement or function (like calling a field ?Date? for example).

Personally I use the following naming convention for fields:

<type>_<table>_<desc>

<type> is data type, for example ?dtm?, ?txt?, ?lng? etc.

<table> is an abbreviation of the table name, for example the table ?tbl_Company? might use ?Co?.

<desc> is a descriptive name for the field, such as ?Address?.


So your Address field might become ?txt_Co_Address?, and Date Joined might become ?dtm_Co_Joined?.

The benefit here (apart from clarity) is it can make queries and SQL statements in code much simpler. For example, if you had a Company and Customer table that both contained an Address field, using the name ?Address? means that you have to identify not only the field but which table the field is located in, as both field names are the same. Using my naming convention means that each field name is unique (txt_Co_Address and txt_Cust_Address), hence no confusion. It also makes it easy to work out what the field relates to, even without knowing the name of the table.

[XLGibbs]

I mentioned that to Zack early on as well, but most of his tables had already been set up, at least with respect to the spaces. Good points on the datatypes in terms of large integrations that would use only forms for viewing and dealing with the data since you can change the labels accordingly.

There is a school of thought that even the underscores aren't the best way, at least if most of the code is handled in VB SQL, although I disagree with it. I much prefer the underscores. Someone at our office brilliantly designed a monster production database which has 30 or 40 tables, several with close to 700 column names. ALL of them have stuff like App-Application-ID and App-Date-Entered-System.

As you know, the SQL statements then require the brackets or interprets each piece of that as separate column name and interprets the dashes as minus signs. This idiocy makes my life hell since the SQL Query analyzer that is used with MS Server 2000 allows for dragging of field names, but does not intuitevely bracket them when dragging them in.

I have a big sign at my desk that says "Proper Prior Planning Prevents **** Poor Performance". Rules to live by.

[Zack Barresse]

Thanks everyone. I have since taken all spaces out of my tables and think I have them performing as they should. Other issues have come up but I'm trying to work through them. :yes

Thanks for the input everyone. Appreciate it from all!!!!