Data type correct for some records, not for others

[Guest]

Hello~

I am adding records to a table from another with an Append query in Access. The column names and types are exactly the same. However, when I look at the query results on a web page (how I am outputting my data), the page displays perfectly for the older records that were already present in the table, but do not work for the new records from the Append table. It seems to be hanging on a particular number field, practiceTypeID, which is very similar to other fields in the table that come before it, and seem to be pulling just fine! I have attached the error and the query, but my big question is, why do some records work and others do not? Please help! Thanks!

[-__cfSearching__-]

semi star gazer wrote:
> I have attached the error and the query, but my big question is,
> why do some records work and others do not?

There are a number of potential problems with those queries.

1) Does the "practiceTypeID" column always have a value? Empty or null values would likely cause a syntax error in the second query.

2) A column named "id" exists in both the mentors and practiceType table. Because you are using SELECT mentors.*, (ie select all columns), the query results contain two columns with the same name: "id". That may is cause issues.

Use a column list instead, and eliminate any duplicate column names by using aliases. Aliases let you assign a different name to an object within a query.

SELECT

mentors.id,
practiceType.id AS PracticeTypeID, <!--- use an alias to rename the column "PracticeTypeID" --->
... other columns ...

FROM mentors,
internOps_new,
practiceType,
practiceArea
WHERE
.... other conditions ...

3) Your query appears to be missing some join conditions. There are four (4) tables in the FROM clause but only three (3) conditions in your WHERE clause. So the query may return too many records. How are the tables related?

> getPracticeID

4) Since you are already retrieving "practiceTypeID" in the first query, what is the purpose of the "getPracticeID" query?

[Newsgroup_User]

you forgot to put ## around mentors.practiceTypeID in your query.
it should be:

WHERE
mentors.id = #id# and internOps_new.mentorID = #id# and practiceType.id
= #mentors.practiceTypeID#

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[-__cfSearching__-]

Azadi wrote:
> you forgot to put ## around mentors.practiceTypeID in your query

No. It think "mentors" is a table name, not a query name. Though there is still the issue of a few missing join conditions, and a few other problems ..

[Newsgroup_User]

right you are! silly me...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Guest]

Thanks for all of your replies! I know that the queries are messy right now, but what confuses me is that the records that were present before I added new records with an append query display perfectly, while I get the above error ONLY with the records that were added with the append query in Access. There is always a value for practiceTypeID, and it works correctly with older records! I can't figure it out. The field types are exactly the same, and even if I go into the database and retype in the practiceTypeID in the mentors table in one of the new records, it still throws the error.

Quick update... if I enter a new record through the online form, the record displays correctly, with the PracticeTypeID and all. If I enter it manually into the database, the error pops up. Why would it be different entering it manually or through the web? It's just a number field, Long Integer type.

[-__cfSearching__-]

semi star gazer
Why would it be different entering it manually or through the web?

It could be a number of things. Examine the record(s) and determine what is different about them.

What about the other issues with your query, such as missing join statements?

[paross1]

One thing for sure, if you had foreign key constraints enabled between the mentors.practiceTypeID and practiceType.ID fields, then you would not be able to add any practiceTypeID value to the mentors table that wasn't a valid ID value in the practiceType table. (Since practiceType.ID field is an autonumber, I am also assuming that it is designated as the primary key in that table as well, so it should be unique and not null.)

In other words, regardless of the reason why Access is allowing you to manually add a "bad" value to the mentors table, if you had FK constraints enabled, the transaction would not be allowed and Access would give you an error message. This is a basic data integrity issue and you should always "protect the database" first.

Phil

[Guest]

I have a one-to-many relationship set up between practiceType.id and mentors.practiceTypeID in the Relationships window in Access-- is that what you mean? When I open the practiceType table and expand a record, it shows both new and old records under the correct practice types. I have looked extensively at any possible differences between the new and old records, and there are none! I really don't know what to do here. I know that my queries need work, but they used to do what they were supposed to do, and now they don't! Thank you again for all of your help...

[paross1]

If you Edit that relationship, and select (check) Enforce Referential Ingegrity, what happens if you try adding a value manually? I sounds like this is somehow adding a value of a different data type when you add it via Access.

Phil

[Guest]

I still get the exact same error for that record, and the rest of them work great!

[paross1]

Is practiceTypeID actually a numeric data type? What happens if you do this (enclose the parameter in single quotes)?

practiceType.id = '#getMentorInfo.practiceTypeID#'

You probably should be using cfqueryparam tags in your queries for numerous reasons, but that is another discussion.

Phil