UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Query Conversion Type Error, Access 2016    
 
   
Mouse11
post May 16 2018, 01:45 PM
Post#1



Posts: 14
Joined: 15-June 11



Hi All,

I am getting some errors with an update query I am working with.
The error is a Conversion Type Error. The field I am trying to update is a Number field type. It has a lookup combobox query so it displays text.
The table name is [Products] the field name is [pSection]

My query/SQL is:
SELECT [1932-47Inventory].pID, [1932-47Inventory].pSection, products.pID, products.pSection
FROM [1932-47Inventory] LEFT JOIN products ON [1932-47Inventory].[pID] = products.[pID];

Any suggestions on the best way to handle this? I am not very experienced but wanted to take a crack at this. I cant seem to find info that would help me.

Thank you all in advance.
J

Go to the top of the page
 
theDBguy
post May 16 2018, 01:56 PM
Post#2


Access Wiki and Forums Moderator
Posts: 72,162
Joined: 19-June 07
From: SunnySandyEggo


Hi,

What you have posted is not an UPDATE query. Are you sure this is the correct one?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post May 16 2018, 01:56 PM
Post#3


UtterAccess VIP
Posts: 9,780
Joined: 6-December 03
From: Telegraph Hill


You've posted a SELECT query.

Is that the source of your combo box?

Please post the SQL of the UPDATE query as well.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Mouse11
post May 16 2018, 02:05 PM
Post#4



Posts: 14
Joined: 15-June 11



lol, Sorry,

This is the update query below.
UPDATE Products INNER JOIN [1932-47Inventory] ON [Products].[pID]=[1932-47Inventory].[pID] SET Products.pSection = [1932-47Inventory].[pSection];


This is the combobox/lookup for the field [pSection] in the table [Products]
SELECT DISTINCTROW sections.sectionID, sections.sectionName, sections.sectionWorkingName
FROM sections
ORDER BY sections.sectionName, sections.sectionWorkingName;

This post has been edited by Mouse11: May 16 2018, 02:08 PM
Go to the top of the page
 
theDBguy
post May 16 2018, 02:13 PM
Post#5


Access Wiki and Forums Moderator
Posts: 72,162
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Just checking...

The data type of [1932-47Inventory].[pSection] is Number (Long Integer), correct?

If so, it's also the same for Products.pSection, right?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Mouse11
post May 16 2018, 02:35 PM
Post#6



Posts: 14
Joined: 15-June 11



For the Products Table, the field pSection type is Number, long integer. There is also a lookup ComboBox Query that displays the number into text by refering to another table.
Here is that SQL Query,
SELECT DISTINCTROW sections.sectionID, sections.sectionName, sections.sectionWorkingName
FROM sections
ORDER BY sections.sectionName, sections.sectionWorkingName;



For the 1932-47Inventory Table the field pSection type is Short Text.

This is where the problem is I believe?
This post has been edited by Mouse11: May 16 2018, 02:43 PM
Go to the top of the page
 
cheekybuddha
post May 16 2018, 04:58 PM
Post#7


UtterAccess VIP
Posts: 9,780
Joined: 6-December 03
From: Telegraph Hill


Just to re-cap:

Products.pSection -> Long Integer
[1932-47Inventory].pSection -> Short Text

These are the fields you are joining on?

Is [1932-47Inventory] a table or a query?

Quick fix:
CODE
UPDATE Products INNER JOIN [1932-47Inventory] ON [Products].[pID]=[1932-47Inventory].[pID] SET Products.pSection = Val([1932-47Inventory].[pSection]);

(Not ideal - you won't be able to take advantage of any indexes)

Better fix:
Make [1932-47Inventory].pSection return a Long Integer

>> The field I am trying to update is a Number field type. It has a lookup combobox query so it displays text. <<

I don't quite understand this. Do you mean you have defined the field in the table as a 'Lookup Field'?

If so, then that is your problem - see here for why

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Mouse11
post May 16 2018, 06:32 PM
Post#8



Posts: 14
Joined: 15-June 11



No,

I have two tables, The Source Table [1932-47Inventory] and the other Table the Target table that i wish to update [Products].

Each table has two identical fields. The Source Table [1932-47Inventory] Fields are [pID]Type(short text) and [pSection] type(short text). The target table [Products] the fields are [pID] Primary Key,type (short text) and [pSection] type (Number, Long Integer).

The Target table [Products] and the field to update [pSection] has a lookup query, a combobox, that converts the number type to text selection. The query for this lookup query is,
SELECT DISTINCTROW sections.sectionID, sections.sectionName, sections.sectionWorkingName
FROM sections
ORDER BY sections.sectionName, sections.sectionWorkingName;


This table/field [Products] [pSection] lookup query is based from another table called [Sections], It identifies the [SectionID]number type to [SectionName] text type For example.
SectionID SectionName
203 1948-56 Engine
102 1932-47 Transmission

I am keying my update query off of the [pID] fields in both tables. [Products] and [1932-47Inventory] as it should be

I want to update the table [Products] and one Field [pSection] from the source table [1932-47Inventory] field [pSection].

The update query i attempted to create doesnt work do to the fields [pSection] in both tables being different types, one being short text and the other being a number. Here is the update query.
UPDATE Products INNER JOIN [1932-47Inventory] ON [Products].[pID]=[1932-47Inventory].[pID] SET Products.pSection = [1932-47Inventory].[pSection];

What i need to figure out is how to make the update query work and deal with the difference in the field types, I dont know how to do this.

Thanks in advance
J
Go to the top of the page
 
cheekybuddha
post May 16 2018, 08:01 PM
Post#9


UtterAccess VIP
Posts: 9,780
Joined: 6-December 03
From: Telegraph Hill


So, did you try the query I suggested?

However, there is a more fundamental issue: why do you need Products.pSection at all, if you can just get it by joining table [1932-47Inventory]?

If there's a reason I'm missing for having the field in both tables, then why aren't the fields the same datatype in each table?

--------------------


Regards,

David Marten
Go to the top of the page
 
Mouse11
post May 17 2018, 01:22 AM
Post#10



Posts: 14
Joined: 15-June 11



The Table [1932-47Inventory] is from an outside excel spreadsheet that i have converted to a table due to the fact that Excel cant deal with a CSV file.

I could join it,,,, just cant remember how this is going to solve my problem? Also not sure how joining this table is going to deal with the field data type since I cant change it as I am collecting the text name of the section from the outside world... again, this table is from data collected from a csv file from scraping. This database is also part of a program written in C# so I am playing in the Devs backyard storage....

To simplify this a bit, I dont mind messing around and making needed changes, just not sure how it will affect the rest of the working program. However I need a way to collect inventory from my competitors, put it in a database table, then update the fields; Section, Price, PartNumber, Description, as these are the main ones.

The last time I worked with MS Access was version 97 which i like very much. Strangely all my vast knowledge of Access has left me!!!!! confused.gif as well as simple basic db relationship rules.... which again,,, im not sure how the rest of the program is going to react to any relationship or joining changes I make/add... but I would be more than willing to try it.... What is really freaking me out at this very moment is I cant remember any of the years of working with MS Access 97, it makes me wonder if im having an early onset of Dementia... fundrink.gif Additionally i would actually like to join my tables as necessary.... would appear to make my life a little easier???

I will assume that i need to join the tables [1932-47Inventory] and the [Products] by the primary key [pID]?
I have also set the join properties as: include ALL records from '1932-47Inventory' and only those records from 'products' where the joined fields are equal. The table [1932-47Inventory] has repeated [pID] numbers due to the each record having more than one section listed.

After I join this table then what should i expect or where should I look to view the info?,,, Where would the results to view be after joining these tables? " I really cant believe I cant remember any of this" Wow, I feel like a 2 year old trying to learn Calculus.

I really appreciate all the help, Hopefully some of this will come back to me and I wont be asking such silly questions?
This post has been edited by Mouse11: May 17 2018, 01:31 AM
Go to the top of the page
 
cheekybuddha
post May 17 2018, 02:58 AM
Post#11


UtterAccess VIP
Posts: 9,780
Joined: 6-December 03
From: Telegraph Hill


So, did you try the query I suggested? (in Post #7)

--------------------


Regards,

David Marten
Go to the top of the page
 
Mouse11
post May 17 2018, 04:12 PM
Post#12



Posts: 14
Joined: 15-June 11



Hi David,
Yes, I tried it, however it didn't work as expected.

I have stripped down my database and have uploaded it. I will attempt to explain what I have and what Im trying to do.

I have 4 tables
  • 1932Inventory, This comes from an outside CSV file which is results from webscraping
  • Products, This is where I need the info from the 1932Inventory table to go.
  • Sections, This is the table that houses the fields SectionID,SectionName,SectionWorkingName
  • Multisections, This table list additional sections for the products.

What I am trying to accomplish is to take the data from the 1932Inventory table and update the Products table with it. A couple of issues are causing problems.
  • The Fields pSection in the tables 1932Inventory and Products are two different data types. This cant be helped as the 1932Inventory table is all short text type due to coming from a csv file from a webscrape. The Products table field pSection is a number type as its info comes from the Sections Table which is where the Section Names resides. I am trying to compare text to text, however it really is trying to compare text to number which doesn't work.
  • The first or primary section is in the Products table, the additional sections are in the Multisections table.
  • I have taken a stab at creating the proper relationships as to make some of this easier?


Hopefully, I have explained properly.... this is my biggest challenge is using proper terminology which still alludes... uuhhgg

Thanks again, for everyone's help... I promise eventually I will get it...

thanks.gif
This post has been edited by Mouse11: May 17 2018, 04:15 PM
Attached File(s)
Attached File  Database5.zip ( 448.83K )Number of downloads: 5
 
Go to the top of the page
 
cheekybuddha
post May 17 2018, 04:15 PM
Post#13


UtterAccess VIP
Posts: 9,780
Joined: 6-December 03
From: Telegraph Hill


J,

You haven't managed to upload the db.

>> however it didn't work as expected <<

Can you explain how it didn't work?

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post May 17 2018, 04:24 PM
Post#14


UtterAccess VIP
Posts: 9,780
Joined: 6-December 03
From: Telegraph Hill


From my post #7:
QUOTE
Do you mean you have defined the field in the table as a 'Lookup Field'?

If so, then that is your problem - see here for why


products.pSection is defined as a Lookup field.


--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post May 17 2018, 04:47 PM
Post#15


UtterAccess VIP
Posts: 9,780
Joined: 6-December 03
From: Telegraph Hill


I think you 're query should be more along the lines of:
CODE
UPDATE products p
INNER JOIN (
  SELECT
    i1.pID,
    s1.sectionID
  FROM [1932-47Inventory] i1
  INNER JOIN sections s1
          ON i1.pSection = s1.sectionWorkingName
) i
        ON p.pID = i.pID
  SET p.pSection = i.SectionID
;

(untested)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post May 17 2018, 04:51 PM
Post#16


Access Wiki and Forums Moderator
Posts: 72,162
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Here's a copy of your original query:

UPDATE Products INNER JOIN [1932-47Inventory] ON [Products].[pID]=[1932-47Inventory].[pID] SET Products.pSection = [1932-47Inventory].[pSection];

Now, try changing it to this one:

UPDATE (products INNER JOIN [1932-47Inventory] ON products.pID = [1932-47Inventory].pID) INNER JOIN sections ON [1932-47Inventory].pSection = sections.sectionWorkingName SET products.pSection = [sections].[sectionID];

Hope it helps...

Edit: Oops, too slow...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Ratfink
post May 18 2018, 12:29 AM
Post#17



Posts: 1
Joined: 18-May 18



Is there a way to change the query to a select query or a way to view the results of the query prior to doing an update?
Go to the top of the page
 
cheekybuddha
post May 18 2018, 03:24 AM
Post#18


UtterAccess VIP
Posts: 9,780
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

Hi Ratfink, are you related to Mouse11? grin.gif

Using the example from this thread you can do something like:
CODE
SELECT
  p.*,
  s.sectionID,
  i.pSection
FROM (
  products p
  INNER JOIN [1932-47Inventory] i
          ON p.pID = i.pID
)
INNER JOIN sections s
        ON i.pSection = s.sectionWorkingName;


If you have more specific requirements, probably better to start a new thread and reference this one.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th May 2018 - 05:16 AM