How do you add new columns using queries?

[JuliusV]

I am pulling in source data and inputting it as a table in access. However, I would like to stop constantly adding in the column on excel and then importing the data into access.

Is there a way to use a Query to add a status column or another method on Access?



Thank you for the help.

[June7]

If you import to existing table, there is no need to 'constantly add' column (field).

Is status an imported value or do you need to calculate it?

[JuliusV]

What I mean is the following:

There are two tables:

Table 1 - Contains general customer information source data

Table 2 - Contains the status

When running queries I create Table 3.

For Table 3 - I want it to be all of Table 1 and include the Status column from Table 2. However, currently, Table 1 does not include a status column, so as a workaround before I import the data from excel into Access, I have been manually adding a status column at the end of the excel file and then importing it in as Table 1.

Now I want to try to save time on that step, by having an access query or another function add in the Status Column from Table 2 to Table 1 all in Access including adding the Status Column to Table 3.

[June7]

I don't understand why status needs to be in Table 1.

If a query can associate table 1 and table 2, why do you need table 3?

Provide diagram of table relationships. If you want to provide db, follow instructions at bottom of my post.

[JuliusV]

The status needs to be part of Table 3. I am creating a separate Table using queries so that I can easily export onto excel and send as a report.

I am using Access only as data storage and data updates with all the data manipulation performed on excel.

[June7]

A query can be exported as easily as a table.

When I inherited db it was using Excel as user interface and Access only to store data. After 3 months I said 'Nuts with that' and completely eliminated the Excel component.

What data manipulation are you doing that requires Excel?

Why not an Access report?

[JuliusV]

Is there a way to add a column to Table 3?

In essence, I want to know how to add a column to a table by using one table as a template and pulling one column for another template.

In terms of data manipulation, I am creating a table to send weekly statuses and show the relationship between employees and managers.

[June7]

So far you haven't given any real reason for using Excel for data manipulation. Everything you describe can be done within Access. Report can be exported to PDF for distribution.

Adding a field to table can be done with a CHANGE TABLE sql action. However, another table cannot be a 'template' except by copy/paste the entire table structure or some elaborate VBA code or a MAKE TABLE SQL action.

If you are really talking about just copying data from one table to another, that is another issue and can be done with an UPDATE sql action.

[Xarkath]

Not sure If I am getting this correctly but my understanding is that you want Table 1 data combined with Table 2 data regardless of where Excel comes in. If I have a reasonably clear understanding then I can think of 2 easy ways to do this.

1: Create a query that joins the 2 tables by the customer ID and select all the fields you want from each table. Once this is pulling all the data the way you want it to then you can export the queried data out to any format you want.

2: setup the query as explained aboved but instead of exporting the data set the query to Make Table to create table 3 complete with the status field. Each time you run the query it will delete and recreate table 3. If you want to keep a running list versus recreating the table each time then after the first run change the query to an Append Query maintaining table 3 as your destination. I actually use this option a lot for various reasons and I can say that it is fast and easy to setup and once setup it pretty much maintains itself.