Data Dump

[youngstah024]

Hi guys,



I have very limited knowledge with MS Access but was asked by my boss to create a dump for the raw data that we get from one of our tools (CMS) since it only keeps historical data for 3 months, so what I did was save all these data in different excel sheets then used UNION to link them all together. My questions are :

• How can I add another column to the query that i just made?
• Is there any other way to make it look presentable, for better viewing and for data export maybe?

Any suggestion would be greatly appreciated.

Thanks in advance!

[CJ_London]

How can I add another column to the query that i just made?

you edit the sql of the union query

Is there any other way to make it look presentable, for better viewing and for data export maybe?

A better way would be to store the data in access tables - one table per structure with perhaps an additional identifier field to indicate which download the data came from, then you would not need to use union queries.

If the data is one big wide spreadsheet, consider breaking it up into different data 'objects' e.g. customer detail, contact detail etc - with the objective of removing duplicate data (e.g. customer details appearing on multiple rows. This process is called 'Normalisation', google it to find out more

[youngstah024]

Thanks for the reply Ajax ..

you edit the sql of the union query

I know I have to edit the query lol, but unfortunately, that's one of the reasons I created this thread.
Posted below is the Union query I saw online, not really sure where I would insert the Query to add a column .. hope it helps! thanks!

SELECT * from [ExcelSheet1]
UNION
SELECT * from [ExcelSheet2]
UNION
SELECT * from [ExcelSheet3]
UNION
SELECT * from [ExcelSheet4]
UNION
SELECT * from [ExcelSheet5]
UNION
SELECT * from [ExcelSheet6]
UNION
SELECT * from [ExcelSheet7]
UNION
SELECT * from [ExcelSheet8]
UNION SELECT * from [ExcelSheet];

[youngstah024]

Thanks for the reply Ajax ..

you edit the sql of the union query

I know I have to edit the query lol, but unfortunately, that's one of the reasons I created this thread.
Posted below is the Union query I saw online, not really sure where I would insert the Query to add a column .. hope it helps! thanks!

SELECT * from [ExcelSheet1]
UNION
SELECT * from [ExcelSheet2]
UNION
SELECT * from [ExcelSheet3]
UNION
SELECT * from [ExcelSheet4]
UNION
SELECT * from [ExcelSheet5]
UNION
SELECT * from [ExcelSheet6]
UNION
SELECT * from [ExcelSheet7]
UNION
SELECT * from [ExcelSheet8]
UNION SELECT * from [ExcelSheet];

Thanks for the Normalization tip .. I'll google that and read on it ..

[CJ_London]

that union query will select all the columns from your various spreadsheets - I assume you have a linked table called ExcelSheet1, ExcelSheet2 etc to each spreadsheet

Note that Excel is not indexed (another benefit of bringing all the data into an access table) so assuming there is a lot of data, it will be slow.

not really sure where I would insert the Query to add a column

If you mean you want to calculate something from the data in the spreadsheet then something like

SELECT *, ([Field1]*[Field43]/25*[Field9]) AS Calc from [ExcelSheet1]
UNION
SELECT *, ([Field1]*[Field43]/25*[Field9]) AS Calc from [ExcelSheet2]
UNION
...
...

[orange]

Do you have a good description in plain English of WHAT you have and WHAT you are trying to create?
Moving spreadsheets to database or importing Excel into an Access table is rarely the full picture.

What does the business want with the data that they are not getting now?
Do you have a plan?

Good luck.

[youngstah024]

Hi Orange ..

What I basically have are like 9 Excel files where I save all the raw data and what they wanted me to do was consolidate it into one file or table in access. Saving it all in excel would make it really slow specially when the months of data piles up. The reason for using UNION is because I have to update the file once a week with the raw data for the previous week, and updating an excel file to update the consolidated Access file would be easy for me, but just as Ajax said, it would be slow.

- If I convert the linked Excel files to tables, would it still update automatically when I add data in the Excel files?

My main concern is adding data every week, so if anyone can provide examples on how to do that on the tables or maybe walk me through it would really be appreciated.

[orange]

What is the difference in each of the 9 excel files? What is the ultimate use of the table//database???
Is someone going to run report, or queries/statistic... from the "consolidated raw data"?
What is the data you ar working with --in business terms?
What are the fields/columns in the spreadsheet?

[youngstah024]

What is the difference in each of the 9 excel files? What is the ultimate use of the table//database???

The only difference of each file is that the data comes from different companies.

Is someone going to run report, or queries/statistic... from the "consolidated raw data"?

I think they plan to keep historical data using access then export the data to excel if they need it, or possibly run a report in the future

What is the data you ar working with --in business terms?

It's all call data from our tool (Avaya CMS) .. Number of calls handled, AHt etc ..

What are the fields/columns in the spreadsheet?

ACD

DATE

START

Skill No

Split/Skill

CALLS OFFERED

ACD CALLS

ABN CALLS

% ABN

ASA

% SL

AHT w/o Hold

AHT w/ Hold

Ave ACW Time

Ave ACD Time

Ave Hold Time

Staffed Time

MAX STAFFED

AUX TIME

[orange]

I'm surprised that you (your org) is not using this data for statistics and marketing, but there may be other parts of the company doing this. For your specific task, if nobody has told you why you re doing this, or you're not sure of why and the potential use, then it would seem (if I don't have a plan or goal, then I don't know or care about the outcome) anything is acceptable.
If it were me, I'd be asking lots of questions. I don't know Avaya, but I just can not understand a viable commercial company that isn't doing analysis and monitoring of its customer interactions.