consolidate tables with make table query?

[KrispyData]

i have two tables. Both tables have the same column headers but in different
order.

Sample headers are:
Rep number
Fiscal year
product
sales, etc...

I want to combine these two tables. Should I create a relationship with the
rep number field for both tables and then create a make-table query so that
all the columns are in the order i want?

 

[Jeff Boyce]

If your database has two identical tables ("same column headers ..."), you
have a spreadsheet, not a database!<g>

Let me guess, there's more than one table because there's more than one
Fiscal Year's data?

Do not create yet another table with the make table query. You may already
have too many.

Instead, consider considately all the data into a single table, with the
[Fiscal Year] appropriate to each row.

Or if you have to keep the redundant structure as two tables, consider using
a UNION query to gather records from each.

Now, if you're saying that the SAME Reps are in both tables, why not use a
query that joins on the Rep and displays both tables' data?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KrispyData]

Hi Jeff,

There are two tables (based from Excel spreadsheets). The reason for 2
tables is that each table comes from a different source which so happens to
have the same column headers with data for the same reps but different time,
product, etc.

For example Table 1 will have the following order:
Fiscal Year
Product
Sales
Rep Number

Table 2 may have:
Rep Number
Fiscal Year
Product
Sales

There are about 15 column headers for each table, but in a different order.
I want the column header order for Table 1 to match the order for Table 2. I
do not want to manually move around all 15 columns.

My objective is to have one table. I think I should join on the Rep and
display both tables, as you mentioned. This woud be a simple, select query,
right? Still new to Access and getting familiar with terminology.

 

[Jeff Boyce]

Just FYI, the order of the columns in the table definition need not
constrain how you display the data.

Use a query to return the columns you wish, in the order you wish.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

There are about 15 column headers for each table, but in a different order.
I want the column header order for Table 1 to match the order for Table 2. I
do not want to manually move around all 15 columns.

My objective is to have one table. I think I should join on the Rep and
display both tables, as you mentioned. This woud be a simple, select query,
right? Still new to Access and getting familiar with terminology.

The order of fields in a table is irrelevant.

Just create a new table with the desired fieldnames, datatypes and field sizes
(Access will have applied some probably-inappropriate defaults when importing
from Excel). Run two append queries from your imported tables, matching
fieldname to fieldname (it will do so automatically, most likely). Run the
queries and it will fill your new table. It would NOT be appropriate to join
the two tables - that would join them "side by side" giving you 30 fields,
whereas you want them joined "end to end", 15 columns with more records.

 

[KrispyData]

Thank you, John!
This is such a time-saver!