Combine and Insert query

R

Ranjith Kurian

I have linked three csv files to MS Access Table, the table names are as
follows MON.csv, Tue.csv and Wed.csv
I have combined all the three tables using UnionAll function in query (qry
Combine).
I would like to have a query/macro funtion which can insert a column to my
'qry combine' identifing the table names example: when the 'qry combine'
combines the first table Mon.csv along with the data the query must insert a
new column and enter a value as "weekMon" to all the data of Monfile.
 
J

John Spencer

In the union query you would need to add a calculated field

SELECT "Monday" as TheDay, *
FROM [MONDAY TABLE}
UNION ALL
SELECT "Tuesday" as TheDay, *
FROM [Tuesday TABLE}
UNION ALL
SELECT "Wednesday" as TheDay, *
FROM [Wednesday TABLE}

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
K

KARL DEWEY

In your append query add a field like this --
"weekMon" AS File_Date
then for Tuesday --
"weekTue" AS File_Date
... etc.
 
R

Ranjith Kurian

Hi Karl,

Thanks for you query, could you please let me know where should i add it in
my below query

SELECT Mon.[Customer Name (DFF)], Mon.Amount
FROM Mon
Union All
SELECT Tue.[Customer Name (DFF)], Tue.Amount
FROM Tue;
 
K

KARL DEWEY

Like this --
SELECT Mon.[Customer Name (DFF)], Mon.Amount, "weekMon" AS File_Date
FROM Mon
Union All
SELECT Tue.[Customer Name (DFF)], Tue.Amount, "weekTue" AS File_Date
FROM Tue;

--
Build a little, test a little.


Ranjith Kurian said:
Hi Karl,

Thanks for you query, could you please let me know where should i add it in
my below query

SELECT Mon.[Customer Name (DFF)], Mon.Amount
FROM Mon
Union All
SELECT Tue.[Customer Name (DFF)], Tue.Amount
FROM Tue;

KARL DEWEY said:
In your append query add a field like this --
"weekMon" AS File_Date
then for Tuesday --
"weekTue" AS File_Date
... etc.
Click to expand...
Click to expand...
 
R

Ranjith Kurian

Thanks a lot......

KARL DEWEY said:
Like this --
SELECT Mon.[Customer Name (DFF)], Mon.Amount, "weekMon" AS File_Date
FROM Mon
Union All
SELECT Tue.[Customer Name (DFF)], Tue.Amount, "weekTue" AS File_Date
FROM Tue;

--
Build a little, test a little.


Ranjith Kurian said:
Hi Karl,

Thanks for you query, could you please let me know where should i add it in
my below query

SELECT Mon.[Customer Name (DFF)], Mon.Amount
FROM Mon
Union All
SELECT Tue.[Customer Name (DFF)], Tue.Amount
FROM Tue;

KARL DEWEY said:
In your append query add a field like this --
"weekMon" AS File_Date
then for Tuesday --
"weekTue" AS File_Date
... etc.

--
Build a little, test a little.


:

I have linked three csv files to MS Access Table, the table names are as
follows MON.csv, Tue.csv and Wed.csv
I have combined all the three tables using UnionAll function in query (qry
Combine).
I would like to have a query/macro funtion which can insert a column to my
'qry combine' identifing the table names example: when the 'qry combine'
combines the first table Mon.csv along with the data the query must insert a
new column and enter a value as "weekMon" to all the data of Monfile.
Click to expand...
Click to expand...
Click to expand...
 
R

Ranjith Kurian

thanks a lot........

John Spencer said:
In the union query you would need to add a calculated field

SELECT "Monday" as TheDay, *
FROM [MONDAY TABLE}
UNION ALL
SELECT "Tuesday" as TheDay, *
FROM [Tuesday TABLE}
UNION ALL
SELECT "Wednesday" as TheDay, *
FROM [Wednesday TABLE}

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ranjith said:
I have linked three csv files to MS Access Table, the table names are as
follows MON.csv, Tue.csv and Wed.csv
I have combined all the three tables using UnionAll function in query (qry
Combine).
I would like to have a query/macro funtion which can insert a column to my
'qry combine' identifing the table names example: when the 'qry combine'
combines the first table Mon.csv along with the data the query must insert a
new column and enter a value as "weekMon" to all the data of Monfile.
Click to expand...
.
Click to expand...
 
R

Ranjith Kurian

Hi Karl,

I tried the below query using 23 tables, i got a error "Duplicate output
alias File_Date, could you please advise me.

KARL DEWEY said:
Like this --
SELECT Mon.[Customer Name (DFF)], Mon.Amount, "weekMon" AS File_Date
FROM Mon
Union All
SELECT Tue.[Customer Name (DFF)], Tue.Amount, "weekTue" AS File_Date
FROM Tue;

--
Build a little, test a little.


Ranjith Kurian said:
Hi Karl,

Thanks for you query, could you please let me know where should i add it in
my below query

SELECT Mon.[Customer Name (DFF)], Mon.Amount
FROM Mon
Union All
SELECT Tue.[Customer Name (DFF)], Tue.Amount
FROM Tue;

KARL DEWEY said:
In your append query add a field like this --
"weekMon" AS File_Date
then for Tuesday --
"weekTue" AS File_Date
... etc.

--
Build a little, test a little.


:

I have linked three csv files to MS Access Table, the table names are as
follows MON.csv, Tue.csv and Wed.csv
I have combined all the three tables using UnionAll function in query (qry
Combine).
I would like to have a query/macro funtion which can insert a column to my
'qry combine' identifing the table names example: when the 'qry combine'
combines the first table Mon.csv along with the data the query must insert a
new column and enter a value as "weekMon" to all the data of Monfile.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Linked Tables - adding field and getting overflow error 1
Combine results using query 4
Query to combine columns.... 6
Create Update Query 4
Update Query with multiple criteria 10
Combine two fields in a query to get a unique third one. 4
UNION query question 6
Merge 2-5 Tables into 1 4

Top