Combine and Insert query

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.
0
Utf
12/9/2009 1:40:11 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
1270 Views

Similar Articles

[PageSpeed] 19

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 Kurian wrote:
> 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.
0
John
12/9/2009 2:35:24 PM
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.


"Ranjith Kurian" wrote:

> 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.
0
Utf
12/9/2009 3:11:01 PM
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" wrote:

> 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.
> 
> 
> "Ranjith Kurian" wrote:
> 
> > 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.
0
Utf
12/9/2009 4:10:01 PM
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" wrote:

> 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" wrote:
> 
> > 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.
> > 
> > 
> > "Ranjith Kurian" wrote:
> > 
> > > 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.
0
Utf
12/9/2009 7:38:01 PM
Thanks a lot......

"KARL DEWEY" wrote:

> 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" wrote:
> 
> > 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" wrote:
> > 
> > > 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.
> > > 
> > > 
> > > "Ranjith Kurian" wrote:
> > > 
> > > > 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.
0
Utf
12/10/2009 9:08:02 AM
thanks a lot........

"John Spencer" wrote:

> 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 Kurian wrote:
> > 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.
> .
> 
0
Utf
12/10/2009 9:08:02 AM
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" wrote:

> 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" wrote:
> 
> > 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" wrote:
> > 
> > > 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.
> > > 
> > > 
> > > "Ranjith Kurian" wrote:
> > > 
> > > > 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.
0
Utf
12/16/2009 5:14:02 PM
Reply:

Similar Artilces:

Access Query Not Found criteria
Hello - - I have a query in Access that searches in part a table that list the 'events' of employees. These events can be many things, name change, hire, term etc.... What I'm trying to do is search for data entry errors. In this case, for employees that were term'd (with a term date) but no term event was entered. The field name is CODE. I've done things like '<> TERM', Not In ("TERM"), NOT LIKE TERM...etc. But I getting a ton of records that will list all term'd employees it just excludes this CODE. Is there a criteria that would show on...

Allow record inserts in subform
I have a form and subform for entering expense header and expense details respectively. When creating a new expense, the form opens for new record and allows input OK of header info and then n detail lines (continuous form in subform). I then want to be able to open this form to allow updates at the form level (i.e. edit existing data) and allow updates/inserts at the subform level. When I open the form with a match on the expense ID (field that links the form and subform) the form behaves exactly as I want it to but the subform does not allow new detail records to be added. What am I ...

query for Dates between today and 30 days back
Hi, I am trying to create a list from a the query that will be ran frequently, it is supposed to show employees on the list that are have dates between now and 30 days ago. I think that I am close with the where clause but no quite. Please will you help me? Thanks, Misty Ex. for today, 12/4/07, all dates between 12/4/07 and 11/5/07. Select Date, Employee From EmpRec Where Date >=DateSerial(Year(Date()),Day(Date()),1) And <DateSerial(Year(Date ()),Day(Date())+1,30) -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200712/1 WHERE [...

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

How to insert JPG's into Access database using CDatabase?
I have a bunch of JPG images that I would like to insert into my Access Database. I have a table in my database with a field called Photo that is an OLE Object type. How do I use CDatabase to insert the JPG's into the table? I am using CXImage (http://www.xdp.it/cximage.htm) to do all my image manipulation within Visual C++ -- _____________________ www.maduckgames.com www.marek-knows.com ...

Query crashes Access with no errors
Hi, I'm still quite new to Access. I've built a query to pull course details from the database. Unfortunately it seems to be causing problems and a part of it is crashing access giving no errors. I've identified that the following is the problem, but I can't see why. Can you suggest any improvements to stop this happening? This checks 2 fields and depending on the content assigns a duration, which may or may not contain one of those fields. Duration: IIf([Full Course Details]![Weeks_in_Qual]=1,([Full Course Details]![Hours_per_Week] & " Hours"),IIf([Full...

Insert same value across many cells
I have 2 values that are taken from an Inputbox that I need to copy across a range of cells what would be the best and most efficient way to do this? I need the values to be inserted into column A & B from the next Blank Cell in Column A Thanks Peter On Aug 7, 4:09=A0pm, Pete <fell-wal...@hotmail.co.uk> wrote: > I have 2 values that are taken from an Inputbox that I need to copy > across a range of cells what would be the best and most efficient way > to do this? I need the values to be inserted into column A & B from > the next Blank Cell in Column A > > Tha...

Access 2003 query help please
Hello, I am in need of some advice for a table create query. I have to work with a database that is imported using XML from an online database. Unfortunately the online database is a single (flat) table of membership data. I need to generate a mailing list from that data. The problem is that each record may contain one of two addresses for the mailing to a member. One is the organization address they work at the other is an optional mailing address. If the optional mailing address is present those fields must be used, otherwise the fields containing the organization address should be us...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

How can I insert a music clip to play in the background?
I have a word presentation that I would like to be able to play Eye of the Tiger in the background without having to open windows media. Is this possible? Word is not presentation software. You can have background music in PowerPoint. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Robbie MacKeen" <Robbie MacKeen@discussions.microsoft.com> wrote in message news:156A57EE-101D-42E0-BB8D-3C39974A8E80@microsoft.com... >I have a word presentation that I would like to be able to play Eye of the > T...

Inserting absolutes
Is there a one or two button punch method of adding/removing absolutes ($) to all cell references in a lengthy formulas? Hi Alex hit F4 to toggle between the different options Frank Alex wrote: > Is there a one or two button punch method of adding/removing > absolutes ($) to all cell references in a lengthy formulas? select the cell with the formula (F5 and type in the address), press F2, press Shift and Home to highlight the formula, press F4, for every press of F4 you can see the difference, first is absolute all addresses, second is absolute rows, third absolute columns and four...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

Make Table Query
How do you add (in run-time) primary key and indexing to a table (during creation or after creation) when it has been created through a make-table query? Either from a designed query or VBA SQL statement. ...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

How to Combine Data in Different Columns
Hi Everyone I need help in this issue. I have tried many ways though some wer successful but they were not efficient. I have say 5 columns of data and the data could be on the same rows o all 5 columns or on different rows hence if I need to combine all th data of these 5 columns, I would have to sort or filter each of the manually and copy and paste to a different worksheet or column. This i very tedious and time consuming. I also tried to write a macro to loo for data in one column then put them into another column but i was no successful. However, experts like you guys shouldn't have ...

Troubleshoot A Query
I am using these 2 queries: CrossTab1 SELECT UnderlyingSymbol, Date, Sum(TradeVolume) AS [Total Volume] FROM BTA_Trade_20100326143051 GROUP BY UnderlyingSymbol, Date; TRANSFORM Sum([TradeVolume]/[Total Volume]) AS Expr1 SELECT BTA_Trade_20100326143051.FirmId, BTA_Trade_20100326143051.UnderlyingSymbol FROM BTA_Trade_20100326143051 INNER JOIN CrossTab1 ON BTA_Trade_20100326143051.UnderlyingSymbol = CrossTab1.UnderlyingSymbol GROUP BY BTA_Trade_20100326143051.FirmId, BTA_Trade_20100326143051.UnderlyingSymbol PIVOT BTA_Trade_20100326143051.Date; I am trying to get the resul...

How to protect the query use for a Pivot that uses 'Get External D
I'm geting external data for a Pivot Table using the 'Get External Data' option, this works fine and retrieves the information from a SQL 2000 database view. In order to don't retrieve "all" the records, I use a filter (ie. where Company equal to Microsoft), that way the User only sees that amount of data that he is supposed to see. The problem I have is that a power User that knows excel will be able to edit/remove that filter and see "all" data. Of course I can create different database views (ie. one for each filter) and apply proper access right...

Cannot get autotext to insert field information
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hello, <br> I cannot get the autotext to insert page numbers, filename and path etc properly. The template inserts but does not update with the appropriate properties. i.e. insert {filename and path} remains as such. Is there something I need to do to get the information to fill the autotext field?? It sounds like you've accidentally toggled display of field codes. Try Option+F9 to toggle them off. HTH |:>) Bob Jones [MVP] Office:Mac On 2/14/10 11:59 PM, in article 59bb2d7b.-1@webcross...

Adding columns in a query
Hi, this is very strange. I am trying to add 2 columns in a query but it does not come out right. Column1 Column2 Column3 $15.00 $30.00 $15.00$30.00 Column3 should equal $45.00. When I subtract, divide or multiply it's fine. It does not work when i tried to add. this is the code behind Column3: Format(nz([Column1])+nz(Column2), "Currency") Thanks in advance -- Message posted via http://www.accessmonster.com It is interpreting the + as a concatenation operator. Try this instead: Format(Val(nz([Column1]))+Val(nz(Column2)), "Currency") On Jan 18, 8:2...

how to delete and insert in a two cells merge using Access 2003?
I want to merge a column of names to a column of 10 digit mobile numbers; but in doing so I need to delete "0" (the first digit) before adding "44" to the front ot the remaining 9. I know of the "=A1& " "&B1" formula, but it does not do the necessary deletion and insertion I need to do. Any help? Hi, Try this in cell C1 =A1&SUBSTITUTE(B1,0,99,1) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dubois2U" <Dubois2U@discussions.microsoft.com> wrote in message news:F954F2E7-44DD-49F0...

combinations
I have a finite number of values that represent length (right now I have less than 20 values). Can Excel take these values and list all possible combinations of the values? Any pointers appreciated. Stephen R. Stephen, No. There are 2,432,902,008,176,640,000 possible ways to combine 20 different values. That is, unless you have another limitation, like only using 4 at a time.... HTH, Bernie MS Excel MVP "Stephen R" <nospam@nospam.com> wrote in message news:u9ZiYhOeFHA.1456@TK2MSFTNGP15.phx.gbl... > I have a finite number of values that represent length (right...

Combine 2 Charts 2007 Excel
I have a line chart (looks like an S curve graph) with two rows of data below & this chart looks exactly like I want it to. Period % Complete Dec-04 7% Jan-05 9% Feb-05 10% Mar-05 12% Apr-05 14% May-05 19% Jun-05 22% Jul-05 23% Aug-05 24% Sep-05 26% Oct-05 28% Nov-05 32% Dec-05 36% Jan-06 40% Feb-06 45% Mar-06 50% Apr-06 55% May-06 59% Jun-06 63% Jul-06 66% Aug-06 71% Sep-06 75% Oct-06 78% Nov-06 81% Dec-06 85% Jan-07 87% Feb-07 89% Mar-07 91% Apr-07 93% May-07 94% Jun-07 94% Jul-07 95% Aug-07 95% Sep-07 95% Oct-07 95% Nov-07 95% Dec-07 95% Jan-08...

insert into
I have an insert into query which is as follows; strgoaldescription = InputBox("Type new goal.") strservicetype = InputBox("This goal is for what service?") intprGroup = InputBox("Indicate the pr_group number if any") intprGroup = IIf(intprGroup = "", Null, intprGroup) strsql = "insert into goal (goal_description,los_id,pr_group_id) values('" & strgoaldescription & "', '" & strservicetype & "', " & IIf(intprGroup = "", Null, intprGroup) & ")" ...

insert a sheet between sheets hidden
Hi, In my workbook, 3 sheets : Premier, Modele, Total. I create and insert one sheet for each day of a month between Premier and Modele with this code : ************************* For A = 1 To LastDay Step 1 Sheets("Mod�le").Select Sheets("Mod�le").Copy Before:=Sheets("Mod�le") Set Sh = Worksheets(Worksheets.Count - 2) With Sh .Visible = True .Name = Format(DateSerial(Annee, LeMois, A), "ddd dd-mm-yy") If Err <> 0 Then Err = 0 End If End With ...