Need A Query To Update Product Information In A Table (1 Viewer)

Akmarii

New member
Local time
Today, 08:32
Joined
Apr 9, 2020
Messages
2
I have limited experience with creating functions in access, but I was asked to create a function for this.

I have two tables that are connected in access The tables are lists of products and their corresponding product number as well as other information. Most of the products in the table have a corresponding company-specific product number but there are a few that don't for those I was asked to create an IF function that will pull the national product number from the connected table for each product that doesn't a company product number already listed.

I have only been able to create a function that leaves those without a product number blank, but I need to figure out how to populate those blank fields with those national product numbers.

I'm not sure how to move forward and would really appreciate some tips.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:32
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! Doesn't sound like you need a function. You should be able to use an UPDATE query for that. Can you show us some sample data? Thanks.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:32
Joined
Jul 9, 2003
Messages
16,245
I would suggest that you post a sample database, not your actual database, make one up just include the main components. Add some fictitious data to the tables you can use the data from the northwind database... See Here:-

Setup the Northwind dB

 

vba_php

Forum Troll
Local time
Today, 08:32
Joined
Oct 6, 2019
Messages
2,884
a few things. this:
I was asked to create an IF function that will pull the national product number from the connected table for each product that doesn't a company product number already listed.
is a little strange. PULL? PULL HOW? display data? display it WHERE? in a table? print it out? furthermore, ''IF'' is a conditional statement, not a function. a little symantical nonsense for ya. =) the purpose of a function is to issue a RETURN. kind of like giving change to someone who gives you a paper dollar for a 50 cent candy bar.

this:
I have only been able to create a function that leaves those without a product number blank, but I need to figure out how to populate those blank fields with those national product numbers.
is a little strange as well. you need to post the code you used. if you don't, there's no way to tell what you did, what you discern RETURN actually means, or how you're trying to get this done.

but yes, and update query could solve the issue more than likely, but then again you didn't give enuf info for that solution to make sense anyway.
 

Akmarii

New member
Local time
Today, 08:32
Joined
Apr 9, 2020
Messages
2
Hey all,
I’m sorry I don’t think I have the knowledge base to ask and effective question based on these replies I’m going to ask for more information and then update this question according. Thanks for your effort.
 

vba_php

Forum Troll
Local time
Today, 08:32
Joined
Oct 6, 2019
Messages
2,884
Hey all,
I’m sorry I don’t think I have the knowledge base to ask and effective question based on these replies I’m going to ask for more information and then update this question according. Thanks for your effort.
we can ask you questions of a lesser nature. you can ask the most basic questions here. don't be afraid of sophisticated answers. just because some people here seem too smart for their own good (I'm included in this group). it doesn't mean they can't communicate with you on the level that you are at. how else can we help you get this done?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:32
Joined
Feb 28, 2001
Messages
27,001
akmarii - Basically, you have two tables that have a relationship between them. You are saying you want to copy a particular number from a second table if and only if the number in the first table is empty or blank. You didn't name the fields so I have to make something up for you. This will not actually be an IF statement but it will have the effect of one.

Let's say the tables are LocalTable and NationalTable, and the ID fields are LocalTable!LocalID and NationalTable.NationalID. The field that the tables have in common is called SKU. Then the way you might do what you want is a query that looks like this:

Code:
UPDATE LocalTable INNER JOIN NationalTable ON LocalTable.SKU = NationalTable.SKU 
SET LocalTable.LocalID = NationalTable.NationalID
WHERE NZ( LocalTable.LocalID, 0 ) = 0 ;

Since you didn't give us table names, I had to make up something. Hope you could follow this.
 

Users who are viewing this thread

Top Bottom