Need best method for creating query using same fields from multiple linked csv files?

[jasonr704]

Currently I have created a new database with multiple linked tables. These are all individual csv files with identical headers.
Here's the files:
2013-jan-web.csv
2013-jan-retail.csv
2013-feb-web.csv


2013-feb-retail.csv

this continues for each month. Each day I export an update from our POS system that includes yesterdays sales. This just overwrites the current month 2013-sept-web.csv & 2013-sept-retail.csv.
The files do not have exactly the same order of fields, but the field names are identical. The newer months have some additional fields added, so field order does not line up. All fields needed for this query are present in all months.

GOAL:
Create a query that is linked to all months and will refresh each time I run it, which is daily. I will only be pulling 8 of 25 fields for the query. Date, Amount, ShippingTotal, SKU, State, DiscountAmount, Coupon, NETRevenue
I'd like to be able to see daily sales, monthly sales and then I'll be setting up YoY monthly reports.

Any suggestions would be greatly appreciated!!!

[June7]

You are linking to the csv files, not importing and you are not retaining historical data? What is YoY?

Maybe you need a UNION query. This will manipulate the data into a normalized, single dataset that can be used as source for other queries and manipulation. There is no designer for UNION, must build in the SQL View of query builder. Limit of 50 lines. Like:

SELECT "Jan" AS Mo, "web" AS Source, field1, field2, field3, ... FROM 2013-jan-web.csv
UNION SELECT "Feb", "retail", field1, field2, field3, ... FROM 2013-jan-retail.csv
...;

[jasonr704]

Yes, I like the idea of creating a Union Query, however I'm not quite sure why it's not working. Here's what I have so far, but it's giving me a syntax error.

SELECT "Jan" AS Mo, "web" AS Source, Order Date, Type, Component Total, Payment Method, Coupon Code, Discount Amount, Quantity, SKU, Brand Name, IP Holder, Device Size, Device Type, Manufacturer, Ship to State, Ship to Postal Code, Ship to Country, Affiliate, Partner Site, Skin, Device, Net Revenue, Design Code, Product Code, Product Name FROM [2013-jan-web]
UNION SELECT "Feb" AS Mo, "retail" AS Source, Order Date, Type, Component Total, Payment Method, Coupon Code, Discount Amount, Quantity, SKU, Brand Name, IP Holder, Device Size, Device Type, Manufacturer, Ship to State, Ship to Postal Code, Ship to Country, Affiliate, Partner Site, Skin, Device, Net Revenue, Design Code, Product Code, Product Name FROM [2013-jan-retail];

I get this message when I run it: "Syntax error in ORDER BY clause". I've also tried putting the linked tables in [ ] and that doesn't help.

[June7]

Any names with spaces or special characters/punctuation (underscore is exception) must be enclosed in []. This includes field names. This is why it is advised to avoid those features in naming as well as reserved words as names.

[jasonr704]

I went in and changed all my source file field names, so there's no spaces. Just trying the first month and a few fields to see if I can get that to work.
Now I'm running into this issue where I get the "Enter Parameter Value Box for Order_Date" box

SELECT Order_Date, Type, Component_Total
FROM [2013-jan-web]
UNION ALL
SELECT Order_Date, Type, Component_Total
FROM [2013-jan-retail];

By the way these tables are all linked csv files. Not sure if my syntax is fine as I have it.

[June7]

Can't just change the names in the query. The names must be the actual names in the object. So unless you can change the header names in the csv file, use []s in the query.

[jasonr704]

Yes, I did that. I changed the field names in each of the source files.