Query to look at certain fields

[Ms. S]

I have 2 databases in which they may contain similar records. However, one
of them has newly added columns with data in most of them. How can I write a
query to pull the information from the new columns so that I can import that
information in the other database?

Please help.

 

[Jerry Whittle]

My first question is about the new columns. Are they something like Nov08,
Dec08, Jan09, where the data is going across like a spreadsheet?

Is there a primary key field in both tables AND do the same records in both
have the same primary key?

Do you mean tables when you say databases? A table holds data. A database
can contain many tables.

What are the table names, the name of the primary key fields, and the names
of the new columns?

 

[John W. Vinson]

I have 2 databases in which they may contain similar records. However, one
of them has newly added columns with data in most of them. How can I write a
query to pull the information from the new columns so that I can import that
information in the other database?

Please help.

I'm not sure I understand the question. When you create a Query based on a
table, you are free to select any or all of the fields in the table.

If you're trying to add information from a table with 12 fields into a table
with 8, though, you'll probably need to open the smaller table in design view
and add four new fields to accommodate the data!

More info about your structure please... for one thing, why are you storing
"the same" data in two different places rather than linking to it and storing
it once?

 

[Ms. S]

First of all, bare with me as I have not used Access in a long time. Second
of all I didnt create the tables. With that said, there is currently no
primary key, however, if that would be easier, please tell me how to set one.
However, I don't want the search to overwrite the infomation in the 2 table;
just to pull in the added columns. There is a unit ID field that can be set
as a primary key.

The table names are Targeting and Universities with union transfer info.
The new columns added are called: Union 1, Local 1 Unit 1. For these
columns there are columns that goes up to 15 for each (i.g. Union 2, Local 2,
Unit 2, etc.)

I want to be able to pull this information from these columns and put in the
other table under these same columns so that the person will be able to use
the updated one and not keep creating another table.

I hope that makes sense. If you know a better/easier/quicker way, please
share with me.

 

[Ms. S]

John, I have explained more in detail under Jerry's posting. If you can read
that one, that would be appreciated. I am getting a little frustrated; so I
apologize.

 

[John W. Vinson]

First of all, bare with me as I have not used Access in a long time. Second
of all I didnt create the tables. With that said, there is currently no
primary key, however, if that would be easier, please tell me how to set one.
However, I don't want the search to overwrite the infomation in the 2 table;
just to pull in the added columns. There is a unit ID field that can be set
as a primary key.

The table names are Targeting and Universities with union transfer info.
The new columns added are called: Union 1, Local 1 Unit 1. For these
columns there are columns that goes up to 15 for each (i.g. Union 2, Local 2,
Unit 2, etc.)

Then your table design IS WRONG.

This is a good design for a spreadsheet, but Access is *not* a spreadsheet!

It appears that what you have is a "many to many" relationship: each
university is related to zero, one, or more (up to fifteen) Unions, and each
Union is at zero, one or more Universities. Is that correct?

If so the properly normalized table structure would be:

Universities
UniversityID <Primary Key, there's probably a public database of
universities somewhere, otherwise perhaps an Autonumber>
UniversityName
City
State
<other information about the university as an entity>

Unions
UnionID <same logic as UniversityID>
UnionName
<other info about the union as an entity>

UniversityUnions
UniversityID <link to Universities, Long Integer if that's an autonumber>
UnionID <link to Unions>
<any information about THIS university's association with THIS union, e.g.
Local, Unit>

This will let you add any needed number of unions for a university (you'll
need a lot more than 15 to handle UC Berkeley!!) without needing to redesign
your table every time you get more, and without having to search across 15
fields.

I want to be able to pull this information from these columns and put in the
other table under these same columns so that the person will be able to use
the updated one and not keep creating another table.

Ummm... why should they create a new table AT ALL!?

I hope that makes sense. If you know a better/easier/quicker way, please
share with me.

I hope the above does so.

 

[Ms. S]

I was able to get this done despite the negative responses and comments
posted. I thought this was a good idea but I see that some of the responders
does not have patience for people who is trying to learn these kinds of
things. Thanks anyway.

 

[tina]

Access is a complex tool, and takes a lot of work to learn to use
effectively. it takes a lot more work, to do *anything* with it, when you're
using it incorrectly. if you're open to correction from folks here who see
you going in the wrong direction and want to help you get moving in the
right direction, then posting here is a good idea; if you're not, then
probably you'll just wind up frustrated and disappointed, because not many
people here have the extra time and effort to put into helping you make a
flawed design work, rather than helping you fix it.

 

[John W. Vinson]

I was able to get this done despite the negative responses and comments
posted. I thought this was a good idea but I see that some of the responders
does not have patience for people who is trying to learn these kinds of
things. Thanks anyway.

I apologize if my response was too harsh, Ms. S. Just remember that we can
only see what you choose to post; I tried to give my best recommendations
based on what I saw. I'm glad you got it working.