Keeping field names in table after query

SMatthews

New Member
Joined
Aug 5, 2002
Messages
18
I have a table with 30 fields, but not all records have the same field names. For instance, Type A record has 30 fields, Type B record has only 15 fields, but only a few of the fields are the same as Type A. As I have the information regarding whether it is a Type A or a Type B record, I run queries to separate the types of records into their respective tables, where I can then apply meaningful field names. I can think of a lot of complex ways to do this (i.e. query to a temp table, cut and paste the records into a table with predefined field names and then before I run it the next time, do a delete query to clear the records from the table with predefined field names), but I am wondering what the cleanest way to do this would be. Due to the data source, the data has to pull in this way, and I am trying to sort it out in the database. However, I do not want to store the data and want to sort the data into tables so that I can work with it while I need to and then override it the next time I pull data. Hope this makes sense! Any ideas are appreciated!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

bat17

Well-known Member
Joined
Aug 15, 2003
Messages
1,470
I am not sure that I fully understood the question but here goes.
When you create a query you can set aliases for the field names.
NewFildName:[OldFieldName]
So rather than using tables you could work directly from a set of queries. If the data follows set patterns you should be able to use a combination of "Is Null" and "Not Is Null" in the criteria to just select the records that you need for each query.

HTH

Peter
 

SMatthews

New Member
Joined
Aug 5, 2002
Messages
18
That is the perfect solution! For my purposes, I would rather work with tables than queries, and using the alias actually updates the table header in a Make Table query. Thanks so much!
 

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
Time for another round of *pushing* queries.
I do agree that sometimes making a table is the best option, but...frequently all you need are queries.

Fact is, just about anything you think that you must have a table for, you can use a query to do the exact same thing.

Try it out sometime. Pick *any* vba function that opens a table and swap in an appropriate queryname. it'll work. The only thing you really need to do is make sure that you've adopted a 'universal' naming syntax that helps you identify your objects (tables/queries/etc)

Mike
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,133
Members
425,263
Latest member
alcat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top