Using MS Query to run data into excel

[s3v3nty2]

Hi,

I would like to check if i want to run multiple query (eg. Jan 13 - Dec 13 different query for each month) but with different criteria (eg. Name) each time when run is it possible?

Thanks.

 

[Fazza]

should be routine

not sure if you have one query running multiple times (so you may like a parameterised query. can be set to refresh on change of a single cell. such as a data validation list of months/Names in one cell, each time that cell changes the query refreshes) or multiple queries, such as 12, one for each month. or one for each Name.

anyway, whatever you want should be OK. any issues?

 

[s3v3nty2]

I have multiple queries running once each time below I have the query examples. Presuming I need to have different Accno each time I run the query, how can i go about it? Once all query have been run, I will merge all excel sheets together to create a pivot. Sorry am very new to this MS Query.

Query 1:

XLODBC
1
DSN=MS Access Database;DBQ=D:\Gen\CBP Extract\2014\1 - Jan 14.accdb;DefaultDir=D:\Gen\CBP Extract\2014;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
SELECT `CBP-EX~1`.ACCNO, `CBP-EX~1`.OP, `CBP-EX~1`.`BILL NOS`, `CBP-EX~1`.`BILL DATE`, `CBP-EX~1`.AMOUNT, `CBP-EX~1`.`TAR DESC` FROM `D:\Gen\CBP Extract\2014\1 - Jan 14.accdb`.`CBP-EX~1` `CBP-EX~1` WHERE (`CBP-EX~1`.ACCNO='351122')




ACCNO OP BILL NOS BILL DATE AMOUNT TAR DESC

Query 2:

XLODBC
1
DSN=MS Access Database;DBQ=D:\Gen\CBP Extract\2014\1 - Jan 14.accdb;DefaultDir=D:\Gen\CBP Extract\2014;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
SELECT `CBP-EX~1`.ACCNO, `CBP-EX~1`.OP, `CBP-EX~1`.`BILL NOS`, `CBP-EX~1`.`BILL DATE`, `CBP-EX~1`.AMOUNT, `CBP-EX~1`.`TAR DESC` FROM `D:\Gen\CBP Extract\2014\1 - Jan 14.accdb`.`CBP-EX~1` `CBP-EX~1` WHERE (`CBP-EX~1`.ACCNO='351122')




ACCNO OP BILL NOS BILL DATE AMOUNT TAR DESC

 

[Fazza]

do you need all the data on individual sheets?

'cause you could just make the pivot table directly from the Access file (& not have data on multiple sheets)

ALT-D-P to start the pivot table wizard, choose external data source, get data, etc

If you want to limit the data to a list of account numbers, maybe you can do that within MS Query.
Long time since I've done this, but IIRC just have a list of account codes in Excel and have the query like

SELECT fields
FROM Excel_list_of_accounts, Access_db
WHERE Excel_list_of_account.Its_Acc_NO_field = Access_db.ACCNO

OK?

 

[s3v3nty2]

I do not need them on individual sheets but as the the files are too big I can't append multiple data into one Access file and I have 12 MS Query for each year. After querying for all 12 months I still need to merge them add in some other details before pivoting them.

Thanks.

 

[Fazza]

well, if the files are too big to append into one Access file I'd expect they're too big for one Excel file too. I'd expect it to be better to have the data in a single Access file & do any manipulations (the adding in of other details) there. that should be superior to working in Excel (which will be slow for that much [too big for Access] data)

regardless of that, let me focus on your immediate question. specifically what step are you wanting help with?

 

[s3v3nty2]

Ya I understand that the files are too big thus there is a criteria in the query itself before generating from MS Query. Anyway, I have another way to generate all 12 months in one go.

Thank you for your help.