Getting Can't Add or Update Records when Making Table with a Query (1 Viewer)

Dilbert99

New member
Local time
Yesterday, 21:35
Joined
Jan 9, 2020
Messages
27
Hello,
I'm currently stumped and am hoping someone can help me.
I have a query with 38 fields which I pull together data to Create a Table. When I put the query into Select mode, it runs fine with no errors, but when I switch it to Make Table mode, I get the following error (see attachment for a pic). It's as though Access thinks that I'm trying to update an existing table and the field types aren't compatible.

However, since I'm making a new table, all of the fields for the new table are supposed to inherit the field properties of the fields used in the query to make the table.

Can somone look at this and help give me some direction on what might be causing it?
Thank you so much in advance!
 

Attachments

  • access error.JPG
    access error.JPG
    36.1 KB · Views: 331

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:35
Joined
Oct 29, 2018
Messages
21,358
Hi. Is there any chance you can post a demo version of your db instead?
 

plog

Banishment Pending
Local time
Yesterday, 20:35
Joined
May 11, 2011
Messages
11,613
Why do you need to make a new table? What's the big picture concept of what you are trying to achieve?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:35
Joined
Jan 20, 2009
Messages
12,849
When Access makes a table it has a look at the first few records it processes and decides on the dataypes based on them. If it encounters an incompatible value further along it will throw an error.

Modify the query to declare the datatype of the fields by using one of the datatype cast functions CStr(), CInt(), CLng(), CSng(), CDbl() etc as appropriate to define the datatypes.

Alternatively, insert the data into a preexisting table that has the datatypes defined instead of using Make Table.
 

Dilbert99

New member
Local time
Yesterday, 21:35
Joined
Jan 9, 2020
Messages
27
When Access makes a table it has a look at the first few records it processes and decides on the dataypes based on them. If it encounters an incompatible value further along it will throw an error.

Modify the query to declare the datatype of the fields by using one of the datatype cast functions CStr(), CInt(), CLng(), CSng(), CDbl() etc as appropriate to define the datatypes.

Alternatively, insert the data into a preexisting table that has the datatypes defined instead of using Make Table.

Thank you for your response! The table that I create is doesn't already exist - so I can't understand when it creates a new table, why it would matter what the data type is? I'm not trying to update an existing table, where i can understand if there would be incompatible types. My routine is to delete the old table before having the query create a new table.
 

Micron

AWF VIP
Local time
Yesterday, 21:35
Joined
Oct 20, 2018
Messages
3,476
My money is on Nulls as the cause, but the question in post 3 shouldn't be ignored. The whole issue might be avoidable by updating rather than mt query.
 

Dilbert99

New member
Local time
Yesterday, 21:35
Joined
Jan 9, 2020
Messages
27
Why do you need to make a new table? What's the big picture concept of what you are trying to achieve?

Thank you for your reply! I'm creating a table when I then have Access export to a spreadsheet. Before the query runs, I delete the former copy of the table, that is created by my query. The query merges data from multiple and creates a new table of the merged data. VBA that runs before the query runs deletes the table.

What confuses me is how a Make Table query (versus an append or update query) would create this error, when I'm not appending or updating an existing table.
 

plog

Banishment Pending
Local time
Yesterday, 20:35
Joined
May 11, 2011
Messages
11,613
So, the big picture doesn't require a table to be made-You can just export the data from the query:

 

Dilbert99

New member
Local time
Yesterday, 21:35
Joined
Jan 9, 2020
Messages
27
My money is on Nulls as the cause, but the question in post 3 shouldn't be ignored. The whole issue might be avoidable by updating rather than mt query.

my query has a criteria where the key field is not null, so there are no null records (or at least there shouldn't be). Maybe it would be easier for you to review the access program (I've attached it). The query is " CreateCompareExport".
 

Attachments

  • CROSSWALK MASTER-AccessProgramv23.zip
    353.8 KB · Views: 460

Dilbert99

New member
Local time
Yesterday, 21:35
Joined
Jan 9, 2020
Messages
27
So, the big picture doesn't require a table to be made-You can just export the data from the query:



Yes, you are correct. But, I wasn't aware there was a query function that exports the data, that's why I created a table first.

I'll research how to use a query to export.

Here's the program. The query in question is "CreateCompareExport"
 

Attachments

  • CROSSWALK MASTER-AccessProgramv23.zip
    353.8 KB · Views: 479

Dilbert99

New member
Local time
Yesterday, 21:35
Joined
Jan 9, 2020
Messages
27
Hi. Is there any chance you can post a demo version of your db instead?

The query name is CreateCompareExport

So, the big picture doesn't require a table to be made-You can just export the data from the query:

So, the big picture doesn't require a table to be made-You can just export the data from the query:



Thank you for the post - I did read it and understand that I can how to do this manually, but I'm trying to have the program do this with as little VBA as possible. This query worked in the past,and as you can see the code deletes the former table before it the query creates a new table.
I want the query to create the table (like it used to to do) automatically. It's just odd to me why I'm now getting this error when it worked before
 

Attachments

  • CROSSWALK MASTER-AccessProgramv23.zip
    353.8 KB · Views: 477

Micron

AWF VIP
Local time
Yesterday, 21:35
Joined
Oct 20, 2018
Messages
3,476
Regardless of whether or not you simply export the query, I'd like to take a look at your db for fun. Your comment about mt query vs append or update may not be valid as upon further thought, I wouldn't be surprised if you had the same issue. It just occurred to me that you might also have calculated fields that either
a) attempt to divide by Null or 0;
b) calculate and return fractional values (e.g. 0.5) yet the target field is integer for example
c) target field is string/number and the values are number/string (this one is just a wild guess since I don't know where your data originally comes from).
You can tell text from numbers (sometimes numbers are text, not numeric values) by looking at field justification - text is left, numbers right.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:35
Joined
Jan 20, 2009
Messages
12,849
What confuses me is how a Make Table query (versus an append or update query) would create this error, when I'm not appending or updating an existing table.

The engine must create the table before it starts inserting records into it. It decides on the datatype of the fields when it makes the table by looking at the first page of records in the results it is going to insert. If it sees only integers in a field on the first page then it creates an integer field. Further down when it encounters numbers with decimal places or strings it throws an error.
 

Dilbert99

New member
Local time
Yesterday, 21:35
Joined
Jan 9, 2020
Messages
27
Regardless of whether or not you simply export the query, I'd like to take a look at your db for fun. Your comment about mt query vs append or update may not be valid as upon further thought, I wouldn't be surprised if you had the same issue. It just occurred to me that you might also have calculated fields that either
a) attempt to divide by Null or 0;
b) calculate and return fractional values (e.g. 0.5) yet the target field is integer for example
c) target field is string/number and the values are number/string (this one is just a wild guess since I don't know where your data originally comes from).
You can tell text from numbers (sometimes numbers are text, not numeric values) by looking at field justification - text is left, numbers right.

I think you just hit the nail on the head - you are very smart and experienced!

I'll be it is (a), as I think there are some items which have a zero price which probably creates item (a).

The query is "CreateCompareExport"

Here's the program.



It just occurred to me that you might also have calculated fields that either
a) attempt to divide by Null or 0;
b) calculate and return fractional values (e.g. 0.5) yet the target field is integer for example
c) target field is string/number and the values are number/string (this one is just a wild guess since I don't know where your data originally comes from).
You can tell text from numbers (sometimes numbers are text, not numeric values) by looking at field justification - text is left, numbers right.
 

Attachments

  • CROSSWALK MASTER-AccessProgramv23.zip
    353.8 KB · Views: 493

Micron

AWF VIP
Local time
Yesterday, 21:35
Joined
Oct 20, 2018
Messages
3,476
At the moment, it looks like your price-per usf field is the culprit. You have #Error values in the results, which will never go into a number field. Still looking...
 

Micron

AWF VIP
Local time
Yesterday, 21:35
Joined
Oct 20, 2018
Messages
3,476
Pretty sure that this expression
Price-Per USF: IIf([CrossWalk]![Factor_USFoods]>0,Round([USFoods]![Product Price]/[CrossWalk]![Factor_USFoods],2),0)
results in "Division By Zero" error, which results in #Error in the query. Crosswalk table has lots of zero's in the Factor_USFoods field. You probably know that you cannot divide by zero, and #Error cannot go into your numeric field. Not sure how you'd want to fix this. One way might be first filter out any records where a field in an expression involving math could contain 0 or Null by using a Select query to start with. This query's criteria would be used to eliminate Null or = zero (note that I'm not saying >1 in case numbers can be negative). Then you'd base your make table query off of clean data. You could also try just adding criteria to your current query but I don't like the idea of specifying criteria on a calculated field. Just a preference perhaps.

Here's a friendly warning not intended to be critical: you are probably going to have a lot of issues with this db going down the road, and the sad part is that the more time, effort and data you put into it the less likely you're going to want to fix it. Aside from your naming convention (which is not good in terms of you using characters you should not use) you join fields in relationships that if related, the names ought to reflect that, but some have entirely different names. Then there is the fact that you've made relationships all over the place where the fields are not required, indexed, allow null or empty strings, or have pretty much useless default values (or any combination of these things). Relationships are usually made in a more controlled fashion.

Anyway, that's enough bad news. I won't go in to normalization because I don't understand the nature of the business and you might have left tables out of this demo db anyway.

Edited for clarification
 

Dilbert99

New member
Local time
Yesterday, 21:35
Joined
Jan 9, 2020
Messages
27
Pretty sure that this expression
Price-Per USF: IIf([CrossWalk]![Factor_USFoods]>0,Round([USFoods]![Product Price]/[CrossWalk]![Factor_USFoods],2),0)
results in "Division By Zero" error, which results in #Error in the query. Crosswalk table has lots of zero's in the Factor_USFoods field. You probably know that you cannot divide by zero, and #Error cannot go into your numeric field. Not sure how you'd want to fix this. One way might be first filter out any records where a field in an expression involving math could contain 0 or Null by using a Select query to start with. This query's criteria would be used to eliminate Null or = zero (note that I'm not saying >1 in case numbers can be negative). Then you'd base your make table query off of clean data. You could also try just adding criteria to your current query but I don't like the idea of specifying criteria on a calculated field. Just a preference perhaps.

Here's a friendly warning not intended to be critical: you are probably going to have a lot of issues with this db going down the road, and the sad part is that the more time, effort and data you put into it the less likely you're going to want to fix it. Aside from your naming convention (which is not good in terms of you using characters you should not use) you join fields in relationships that if related, the names ought to reflect that, but some have entirely different names. Then there is the fact that you've made relationships all over the place where the fields are not required, indexed, allow null or empty strings, or have pretty much useless default values (or any combination of these things). Relationships are usually made in a more controlled fashion.

Anyway, that's enough bad news. I won't go in to normalization because I don't understand the nature of the business and you might have left tables out of this demo db anyway.

Edited for clarification


Thank you for the time to provide honest feedback. Honestly, Ive learned Access on my own. I tried to develop this program with minimal VBA (of which I am an not good at), and to use queries and macros to handle the data. But, due to limitations of the queries and macros, I've had to input some VBA.

I agree with your assessment.
Would you be interested in $100 to clean up the program?
 

Dilbert99

New member
Local time
Yesterday, 21:35
Joined
Jan 9, 2020
Messages
27
Pretty sure that this expression
Price-Per USF: IIf([CrossWalk]![Factor_USFoods]>0,Round([USFoods]![Product Price]/[CrossWalk]![Factor_USFoods],2),0)
results in "Division By Zero" error, which results in #Error in the query. Crosswalk table has lots of zero's in the Factor_USFoods field. You probably know that you cannot divide by zero, and #Error cannot go into your numeric field. Not sure how you'd want to fix this. One way might be first filter out any records where a field in an expression involving math could contain 0 or Null by using a Select query to start with. This query's criteria would be used to eliminate Null or = zero (note that I'm not saying >1 in case numbers can be negative). Then you'd base your make table query off of clean data. You could also try just adding criteria to your current query but I don't like the idea of specifying criteria on a calculated field. Just a preference perhaps.

Here's a friendly warning not intended to be critical: you are probably going to have a lot of issues with this db going down the road, and the sad part is that the more time, effort and data you put into it the less likely you're going to want to fix it. Aside from your naming convention (which is not good in terms of you using characters you should not use) you join fields in relationships that if related, the names ought to reflect that, but some have entirely different names. Then there is the fact that you've made relationships all over the place where the fields are not required, indexed, allow null or empty strings, or have pretty much useless default values (or any combination of these things). Relationships are usually made in a more controlled fashion.

Anyway, that's enough bad news. I won't go in to normalization because I don't understand the nature of the business and you might have left tables out of this demo db anyway.

Edited for clarification

One additional note: The zero factor values aren't an issue because the formulas in the queries don't execute unless the factor is greater than zero. But I appreciate that you don't have the whole picture on the design, even thought the design is admittedly very poor because I am not great at Access. The program has been working well, until I encountered zeros in the price file for RestDepot. I can just make sure that no zero prices are imported and that should fix the problem. But, i wouldn't mind having someone clean it up.
 

Micron

AWF VIP
Local time
Yesterday, 21:35
Joined
Oct 20, 2018
Messages
3,476
I should have looked closer at the expressions - I'd have to agree with you. What I'm going to do now is see if the other half of the expression contains Null values. In that case, you'd be trying to divide Null by some number but AFAIK, the result should be Null, not #Error - at least it ought to be in vba. I'll see if I can figure out what's causing that.
 

Users who are viewing this thread

Top Bottom