Combine and Insert query

  • Thread starter Thread starter Ranjith Kurian
  • Start date Start date
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.
 
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
 
In your append query add a field like this --
"weekMon" AS File_Date
then for Tuesday --
"weekTue" AS File_Date
... etc.
 
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;
 
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.
 
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.
 
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.
.
 
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.
 
Back
Top