what kind of query is needed?

[Guest]

I have two tables

tableone
name# emptyfield field3 field4 field5

tabletwo
name1 somestatea
name2 somestateb
name3 somestatea
name4 somestatec
name5 somestatea

tabletwo has every possible name. Many names live in the same state.

How do I write a query so that emptyfield in tableone shows the correct
state for each name? Do I make a new table query?

Thanks very much for any help. It seems to be an easy problem, but I have
been stuck for hours.

 

[Allen Browne]

Answered when you posted this question previously.

 

[Guest]

Answered when you posted this question previously.

thanks anyway.......since I have never posted a question before???

I did a search before asking and did not find an answer to my question.

Nevermind, I will go buy a book tomorrow and figure out the answer myself

great help, microsoft, not

 

[Allen Browne]

Okay: my apology if it was someone else who asked the same question 5 hours
earlier in this same group.

Subject: fields in a select query
Thread: Alias: (e-mail address removed)
Body:

Can the target fields of a select query be specified by the values in
a table or the results of another query?

Here's the essential sql statement I want to execute:

SELECT field1, field2, field6, field8
FROM table1;

And table2 looks like this:

fieldNum fieldName
1 field1
2 field2
3 field6
4 field8

So can I use the values in fieldName from table2 to select the fields
from table1 in the select query?

If so, how?

My reply:
No. You would have to generate the SQL statement dynamically,
using OpenRecordSet on Table2, and looping through the records
to build up the SELECT clause of the SQL string.

 

[John W. Vinson]

I have two tables

tableone
name# emptyfield field3 field4 field5

tabletwo
name1 somestatea
name2 somestateb
name3 somestatea
name4 somestatec
name5 somestatea

tabletwo has every possible name. Many names live in the same state.

How do I write a query so that emptyfield in tableone shows the correct
state for each name? Do I make a new table query?

Thanks very much for any help. It seems to be an easy problem, but I have
been stuck for hours.

Is Name# the Primary Key of tableone? That is, given a name value,
does it uniquely identify one and only one row? If these are people's
names, that is most unlikely: names are NOT unique. Suppose you have
twelve listings in your table for Mike Smith, and there are Mike
Smiths listed in twelve different states?

ASSUMING that you do have some unique link between the tables, create
a Query joinint tableone to tabletwo by that unique field. Change it
to an update query using the query type icon. on the "Update To" line
under emptyfield put

[Tabletwo].[statefield]

using the actual name of the field (which you didn't post). The
brackets are required (otherwise it will update the emptyfield to the
text string "tabletwo.statefield" - which is pretty useless!)

Run the query by clicking the ! icon.

If this doesn't work or isn't clear, please post the actual table and
field names of your tables, indicate the fields' datatypes
(particularly any Lookup fields should be noted - and ideally
*eliminated*), and the Primary Key of each table.

John W. Vinson [MVP]

 

[Guest]

I have two tables

tableone
name# emptyfield field3 field4 field5

tabletwo
name1 somestatea
name2 somestateb
name3 somestatea
name4 somestatec
name5 somestatea

tabletwo has every possible name. Many names live in the same state.

How do I write a query so that emptyfield in tableone shows the correct
state for each name? Do I make a new table query?

Thanks very much for any help. It seems to be an easy problem, but I have
been stuck for hours.

Is Name# the Primary Key of tableone? That is, given a name value,
does it uniquely identify one and only one row? If these are people's
names, that is most unlikely: names are NOT unique. Suppose you have
twelve listings in your table for Mike Smith, and there are Mike
Smiths listed in twelve different states?

ASSUMING that you do have some unique link between the tables, create
a Query joinint tableone to tabletwo by that unique field. Change it
to an update query using the query type icon. on the "Update To" line
under emptyfield put

[Tabletwo].[statefield]

using the actual name of the field (which you didn't post). The
brackets are required (otherwise it will update the emptyfield to the
text string "tabletwo.statefield" - which is pretty useless!)

Run the query by clicking the ! icon.

If this doesn't work or isn't clear, please post the actual table and
field names of your tables, indicate the fields' datatypes
(particularly any Lookup fields should be noted - and ideally
*eliminated*), and the Primary Key of each table.

John W. Vinson [MVP]

No, name# is not a primary key. The primary keys for both tables was
generated by access and are the first field in both tables (as Id).

In tableone, names appear many times in name# column. In tabletwo, names
appear only once in name column. Second column is somestate where states a
through z. To use your listings, Mike Smith occurs many times in
tableone:name#. All Mike Smiths would live in the same state.

Whenever I try to add [tabletwo]:[somestate] to "update to" line under
emptyfield, access does not allow this, instead puts it in a new column???

I tried looking at the other example you say was posted at the same time. I
cannot see where it is the same as mine. Not saying it isn't, but it just
confuses me even more.

All fields are text with unlimited characters for my purpose. It seems like
an easy problem, but I have spent days and purchased two books on access and
still cannot solve the problem. Could it be because there is no relationship
between the two tables? From my point of view, the relationship is that
tableone:name# column contains the names listed in tabletwo:names. In
tabletwo:names all person's names are listed only once. All people with the
same number live in the same state, but many people can live in one state?

several years ago, I converted both tables to excel and worked out a way to
do what I am trying to do. I then converted the corrected table back to
access. However I have forgotten what I did? Somehow I used Lookup from
tabletwo to fill in tableone? Seems easy, but I am definitely missing
something?