Update Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have tblWholesaler and tblCities. Both tables have City, Priority,
Interested, and ID. I would like to:
1) If tblWholsaler.City can be found in tblCities.City then update
tblWholsaler.Interested = Yes, else no.
2) If Interested = yes then Update tblWholesaler.Priority to be the
tblCities.Priority.

So, in english, I want to check if the city in tblWholesaler is of
interested and, if so, mark tblWholesaler.Interest = yes and set the priority
for that tblWholesaler.Priority as defined in tblCities.Priority.

Here is as far as I got:
UPDATE tblWholesaler INNER JOIN tblCities ON tblWholesaler.City =
tblCities.City SET tblWholesaler.Interest = True;

Thanks
 
Rod

Why? As in "why have two tables with identical structure?" This sounds
like a very spreadsheetly way to do something.

I don't have a very clear image of your overall business need, but it seems
to me that you could add a single field to your tblCities table to hold a
Yes/No value ... maybe you would name the field [InterestedWholesaler]...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
the data comes to me via a spreadsheet. I put it in a database in order to
do away with excel.

Cities has an "Interested" field

The idea here is to be able to import tblWholesaler data based on theprority
of the city. Some cities in the data coming to me is of no interest. For
example, if a city is w/in 30 minutes away, then a priority 1 should be
assigned to that city; all of the priority 1's should be appended to the
tblCand before 2's, then 3's, then 4's. But the data coming to me is all
mixed up. So, if I could use tblCities to assign priorities and update
tblWholesaler I could do something like:
while priority 1
get record
append record to tblCand
end
While priority 2
get record...

Does this sound like a good approach given I am just a bit beyond a beginner
in Access?

Jeff Boyce said:
Rod

Why? As in "why have two tables with identical structure?" This sounds
like a very spreadsheetly way to do something.

I don't have a very clear image of your overall business need, but it seems
to me that you could add a single field to your tblCities table to hold a
Yes/No value ... maybe you would name the field [InterestedWholesaler]...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Rod said:
Hello,
I have tblWholesaler and tblCities. Both tables have City, Priority,
Interested, and ID. I would like to:
1) If tblWholsaler.City can be found in tblCities.City then update
tblWholsaler.Interested = Yes, else no.
2) If Interested = yes then Update tblWholesaler.Priority to be the
tblCities.Priority.

So, in english, I want to check if the city in tblWholesaler is of
interested and, if so, mark tblWholesaler.Interest = yes and set the priority
for that tblWholesaler.Priority as defined in tblCities.Priority.

Here is as far as I got:
UPDATE tblWholesaler INNER JOIN tblCities ON tblWholesaler.City =
tblCities.City SET tblWholesaler.Interest = True;

Thanks
Click to expand...
Click to expand...
 
I still don't have a very clear "map" of what you get, and what you do with
it...

I'll point out that stuffing Excel data straight into Access tables may not
allow you to make the best use of Access' relationally-oriented features and
functions.

Is your Access data structure well-normalized, or merely a copy of the Excel
data?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Rod said:
the data comes to me via a spreadsheet. I put it in a database in order to
do away with excel.

Cities has an "Interested" field

The idea here is to be able to import tblWholesaler data based on theprority
of the city. Some cities in the data coming to me is of no interest. For
example, if a city is w/in 30 minutes away, then a priority 1 should be
assigned to that city; all of the priority 1's should be appended to the
tblCand before 2's, then 3's, then 4's. But the data coming to me is all
mixed up. So, if I could use tblCities to assign priorities and update
tblWholesaler I could do something like:
while priority 1
get record
append record to tblCand
end
While priority 2
get record...

Does this sound like a good approach given I am just a bit beyond a beginner
in Access?

Jeff Boyce said:
Rod

Why? As in "why have two tables with identical structure?" This sounds
like a very spreadsheetly way to do something.

I don't have a very clear image of your overall business need, but it seems
to me that you could add a single field to your tblCities table to hold a
Yes/No value ... maybe you would name the field [InterestedWholesaler]...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Rod said:
Hello,
I have tblWholesaler and tblCities. Both tables have City, Priority,
Interested, and ID. I would like to:
1) If tblWholsaler.City can be found in tblCities.City then update
tblWholsaler.Interested = Yes, else no.
2) If Interested = yes then Update tblWholesaler.Priority to be the
tblCities.Priority.

So, in english, I want to check if the city in tblWholesaler is of
interested and, if so, mark tblWholesaler.Interest = yes and set the priority
for that tblWholesaler.Priority as defined in tblCities.Priority.

Here is as far as I got:
UPDATE tblWholesaler INNER JOIN tblCities ON tblWholesaler.City =
tblCities.City SET tblWholesaler.Interest = True;

Thanks
Click to expand...
Click to expand...
Click to expand...
 
What I get is about 90,000 contacts across two spreadsheets. They have area
code, number, name, city, etc.. I need to process all of the records and
appendthe ones of interest in the specified order to an existing
"tblCandidates" database. Those closest are called first. I would like to
automate doing this by the process outlined below. I do not get the
informationin any order; I need to order it as the incoming information is
appended to the tblCandidates, thus, append priority 1's then2's, then 3's,
etc.. The priority and interest is based in the city.

Jeff Boyce said:
I still don't have a very clear "map" of what you get, and what you do with
it...

I'll point out that stuffing Excel data straight into Access tables may not
allow you to make the best use of Access' relationally-oriented features and
functions.

Is your Access data structure well-normalized, or merely a copy of the Excel
data?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Rod said:
the data comes to me via a spreadsheet. I put it in a database in order to
do away with excel.

Cities has an "Interested" field

The idea here is to be able to import tblWholesaler data based on theprority
of the city. Some cities in the data coming to me is of no interest. For
example, if a city is w/in 30 minutes away, then a priority 1 should be
assigned to that city; all of the priority 1's should be appended to the
tblCand before 2's, then 3's, then 4's. But the data coming to me is all
mixed up. So, if I could use tblCities to assign priorities and update
tblWholesaler I could do something like:
while priority 1
get record
append record to tblCand
end
While priority 2
get record...

Does this sound like a good approach given I am just a bit beyond a beginner
in Access?

Jeff Boyce said:
Rod

Why? As in "why have two tables with identical structure?" This sounds
like a very spreadsheetly way to do something.

I don't have a very clear image of your overall business need, but it seems
to me that you could add a single field to your tblCities table to hold a
Yes/No value ... maybe you would name the field [InterestedWholesaler]...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello,
I have tblWholesaler and tblCities. Both tables have City, Priority,
Interested, and ID. I would like to:
1) If tblWholsaler.City can be found in tblCities.City then update
tblWholsaler.Interested = Yes, else no.
2) If Interested = yes then Update tblWholesaler.Priority to be the
tblCities.Priority.

So, in english, I want to check if the city in tblWholesaler is of
interested and, if so, mark tblWholesaler.Interest = yes and set the
priority
for that tblWholesaler.Priority as defined in tblCities.Priority.

Here is as far as I got:
UPDATE tblWholesaler INNER JOIN tblCities ON tblWholesaler.City =
tblCities.City SET tblWholesaler.Interest = True;

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Rod

I have a bit clearer picture, but I'm not sure how I would handle it.

No, I mean, I don't know what cities are closest, so I don't know how I'd
pick "priority1" (or 2 or 3). You may know that, but unless you can tell
Access how to tell that, you'll have to make that determination for each
city.

By the way, it sounds like the process you use is to identify the priority1
cities, then append, then the 2's, etc.
This is NOT a requirement for loading into an Access table. Access works
with sets ... if you could set the priorities somehow, then you could load
ALL the cities with a single query, provided you were also loading their
respective priorities.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Rod said:
What I get is about 90,000 contacts across two spreadsheets. They have area
code, number, name, city, etc.. I need to process all of the records and
appendthe ones of interest in the specified order to an existing
"tblCandidates" database. Those closest are called first. I would like to
automate doing this by the process outlined below. I do not get the
informationin any order; I need to order it as the incoming information is
appended to the tblCandidates, thus, append priority 1's then2's, then 3's,
etc.. The priority and interest is based in the city.

Jeff Boyce said:
I still don't have a very clear "map" of what you get, and what you do with
it...

I'll point out that stuffing Excel data straight into Access tables may not
allow you to make the best use of Access' relationally-oriented features and
functions.

Is your Access data structure well-normalized, or merely a copy of the Excel
data?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Rod said:
the data comes to me via a spreadsheet. I put it in a database in
Click to expand...
order
to
do away with excel.

Cities has an "Interested" field

The idea here is to be able to import tblWholesaler data based on theprority
of the city. Some cities in the data coming to me is of no interest. For
example, if a city is w/in 30 minutes away, then a priority 1 should be
assigned to that city; all of the priority 1's should be appended to the
tblCand before 2's, then 3's, then 4's. But the data coming to me is all
mixed up. So, if I could use tblCities to assign priorities and update
tblWholesaler I could do something like:
while priority 1
get record
append record to tblCand
end
While priority 2
get record...

Does this sound like a good approach given I am just a bit beyond a beginner
in Access?

:

Rod

Why? As in "why have two tables with identical structure?" This sounds
like a very spreadsheetly way to do something.

I don't have a very clear image of your overall business need, but
Click to expand...
it
seems
to me that you could add a single field to your tblCities table to
Click to expand...
hold
a
Yes/No value ... maybe you would name the field [InterestedWholesaler]...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello,
I have tblWholesaler and tblCities. Both tables have City, Priority,
Interested, and ID. I would like to:
1) If tblWholsaler.City can be found in tblCities.City then update
tblWholsaler.Interested = Yes, else no.
2) If Interested = yes then Update tblWholesaler.Priority to be the
tblCities.Priority.

So, in english, I want to check if the city in tblWholesaler is of
interested and, if so, mark tblWholesaler.Interest = yes and set the
priority
for that tblWholesaler.Priority as defined in tblCities.Priority.

Here is as far as I got:
UPDATE tblWholesaler INNER JOIN tblCities ON tblWholesaler.City =
tblCities.City SET tblWholesaler.Interest = True;

Thanks
Click to expand...
Click to expand...
Click to expand...
 
I have now manually prioritized the cities in tblCities, which has:
ID, City, State, Zip, Priority, Interested.
So, what I need help on is how cal I tell Access to open the table, get the
priority1s and append them to tblCandidates, then priority2s. The idea is I
append them to tblCandidates in priority order.

Jeff Boyce said:
Rod

I have a bit clearer picture, but I'm not sure how I would handle it.

No, I mean, I don't know what cities are closest, so I don't know how I'd
pick "priority1" (or 2 or 3). You may know that, but unless you can tell
Access how to tell that, you'll have to make that determination for each
city.

By the way, it sounds like the process you use is to identify the priority1
cities, then append, then the 2's, etc.
This is NOT a requirement for loading into an Access table. Access works
with sets ... if you could set the priorities somehow, then you could load
ALL the cities with a single query, provided you were also loading their
respective priorities.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Rod said:
What I get is about 90,000 contacts across two spreadsheets. They have area
code, number, name, city, etc.. I need to process all of the records and
appendthe ones of interest in the specified order to an existing
"tblCandidates" database. Those closest are called first. I would like to
automate doing this by the process outlined below. I do not get the
informationin any order; I need to order it as the incoming information is
appended to the tblCandidates, thus, append priority 1's then2's, then 3's,
etc.. The priority and interest is based in the city.

Jeff Boyce said:
I still don't have a very clear "map" of what you get, and what you do with
it...

I'll point out that stuffing Excel data straight into Access tables may not
allow you to make the best use of Access' relationally-oriented features and
functions.

Is your Access data structure well-normalized, or merely a copy of the Excel
data?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

the data comes to me via a spreadsheet. I put it in a database in order
to
do away with excel.

Cities has an "Interested" field

The idea here is to be able to import tblWholesaler data based on
theprority
of the city. Some cities in the data coming to me is of no interest. For
example, if a city is w/in 30 minutes away, then a priority 1 should be
assigned to that city; all of the priority 1's should be appended to the
tblCand before 2's, then 3's, then 4's. But the data coming to me is all
mixed up. So, if I could use tblCities to assign priorities and update
tblWholesaler I could do something like:
while priority 1
get record
append record to tblCand
end
While priority 2
get record...

Does this sound like a good approach given I am just a bit beyond a
beginner
in Access?

:

Rod

Why? As in "why have two tables with identical structure?" This sounds
like a very spreadsheetly way to do something.

I don't have a very clear image of your overall business need, but it
seems
to me that you could add a single field to your tblCities table to hold
a
Yes/No value ... maybe you would name the field
[InterestedWholesaler]...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello,
I have tblWholesaler and tblCities. Both tables have City, Priority,
Interested, and ID. I would like to:
1) If tblWholsaler.City can be found in tblCities.City then update
tblWholsaler.Interested = Yes, else no.
2) If Interested = yes then Update tblWholesaler.Priority to be the
tblCities.Priority.

So, in english, I want to check if the city in tblWholesaler is of
interested and, if so, mark tblWholesaler.Interest = yes and set the
priority
for that tblWholesaler.Priority as defined in tblCities.Priority.

Here is as far as I got:
UPDATE tblWholesaler INNER JOIN tblCities ON tblWholesaler.City =
tblCities.City SET tblWholesaler.Interest = True;

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Why? Since a table in Access is just a "bucket o' data", it doesn't matter
WHAT order you append the records. Since you will have the priority value
for each, you can use queries to retrieve all the priority1 records, and
another query (or even the same query using a parameter) for the priority2
records, and so on.

No need to segment the loading process. Do it all with one append query.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Rod said:
I have now manually prioritized the cities in tblCities, which has:
ID, City, State, Zip, Priority, Interested.
So, what I need help on is how cal I tell Access to open the table, get the
priority1s and append them to tblCandidates, then priority2s. The idea is I
append them to tblCandidates in priority order.

Jeff Boyce said:
Rod

I have a bit clearer picture, but I'm not sure how I would handle it.

No, I mean, I don't know what cities are closest, so I don't know how I'd
pick "priority1" (or 2 or 3). You may know that, but unless you can tell
Access how to tell that, you'll have to make that determination for each
city.

By the way, it sounds like the process you use is to identify the priority1
cities, then append, then the 2's, etc.
This is NOT a requirement for loading into an Access table. Access works
with sets ... if you could set the priorities somehow, then you could load
ALL the cities with a single query, provided you were also loading their
respective priorities.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Rod said:
What I get is about 90,000 contacts across two spreadsheets. They
Click to expand...
have
area
code, number, name, city, etc.. I need to process all of the records and
appendthe ones of interest in the specified order to an existing
"tblCandidates" database. Those closest are called first. I would
Click to expand...
like
to
automate doing this by the process outlined below. I do not get the
informationin any order; I need to order it as the incoming information is
appended to the tblCandidates, thus, append priority 1's then2's, then 3's,
etc.. The priority and interest is based in the city.

:

I still don't have a very clear "map" of what you get, and what you
Click to expand...
do
with
it...

I'll point out that stuffing Excel data straight into Access tables
Click to expand...
may
not
allow you to make the best use of Access' relationally-oriented
Click to expand...
features
and
functions.

Is your Access data structure well-normalized, or merely a copy of
Click to expand...
the
Excel
data?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

the data comes to me via a spreadsheet. I put it in a database in order
to
do away with excel.

Cities has an "Interested" field

The idea here is to be able to import tblWholesaler data based on
theprority
of the city. Some cities in the data coming to me is of no
Click to expand...
interest.
For
example, if a city is w/in 30 minutes away, then a priority 1
Click to expand...
should
be
assigned to that city; all of the priority 1's should be appended
Click to expand...
to
the
tblCand before 2's, then 3's, then 4's. But the data coming to me
Click to expand...
is
all
mixed up. So, if I could use tblCities to assign priorities and update
tblWholesaler I could do something like:
while priority 1
get record
append record to tblCand
end
While priority 2
get record...

Does this sound like a good approach given I am just a bit beyond a
beginner
in Access?

:

Rod

Why? As in "why have two tables with identical structure?"
Click to expand...
This
sounds
like a very spreadsheetly way to do something.

I don't have a very clear image of your overall business need,
Click to expand...
but
it
seems
to me that you could add a single field to your tblCities table
Click to expand...
to
hold
a
Yes/No value ... maybe you would name the field
[InterestedWholesaler]...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello,
I have tblWholesaler and tblCities. Both tables have City, Priority,
Interested, and ID. I would like to:
1) If tblWholsaler.City can be found in tblCities.City then update
tblWholsaler.Interested = Yes, else no.
2) If Interested = yes then Update tblWholesaler.Priority to
Click to expand...
be
the
tblCities.Priority.

So, in english, I want to check if the city in tblWholesaler is of
interested and, if so, mark tblWholesaler.Interest = yes and
Click to expand...
set
the
priority
for that tblWholesaler.Priority as defined in tblCities.Priority.

Here is as far as I got:
UPDATE tblWholesaler INNER JOIN tblCities ON tblWholesaler.City =
tblCities.City SET tblWholesaler.Interest = True;

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top