Creating Query using Criteria from another table 1

[JMullin04]

I am working on a query in which I have two tables. The first table has financial data and the second has list of territories.

What I'm trying to accomplish is to export financial data for each territory. The data would be either in text or Excel format.

Example:

Table A - Financial data (One field is the territory)
Table B - List of Territories

Result would be files for each territory that include the financial data for that territory.

I need immediate help on this. I'm working on a project that my employer requires ASAP.

Thanks for any assistance.

 

[lespaul]

Go into Excel, go to Data -> Get External Data -> Run New Database Query.

find your database, select the tables that you are interested in. Select the fields that you are interested in (So get the territory description from table B and any fields you want from table A, make sure there is a relationship line between the TerritoryID field in both tables). Order by the Territory. Excel will display the query results and then you can transfer that information into Excel for easy manipulation.

HTH

Leslie

 

[JMullin04]

Thanks for the suggestion. However, I need to have this done within Access. There is to much data in the financial table to be read into Excel.

 

[lespaul]

Same process, you just build your query in Access instead of Excel.

SELECT TerritoryDesc, TableA* FROM TABLEA INNER JOIN TABLEB ON TABLEA.TerritoryID = TABLEB.TerritoryID
WHERE TABLEA.TerritoryID = 1
GROUP BY TerritoryDesc

So, this query returns all the territory 1 information, you export it to excel. Then you do a query for territory 2 information and you export to excel.

If you want to run the once query and gather all the information at once and then have it output a single export for each territory ID you will have to code that.



Leslie

 

[JMullin04]

Thanks for the information. This does work but I want to have the system automated. There are over 116 territories and new territories are created often. What I'm trying to accomplish is that instead of having to put the territory ID in manually, I want the system to read the territories in the territory table and do this automatically for each territory.

I've been looking at creating WHILE or IF..THEN statements but not sure how to go about it. I've only begun to get into programming.

I do have the system create the territory table each time a new data file is loaded. It will therefore only have territories that are in the full financial data table.

 

[lespaul]

Ok, here's something to try:

create a recordset:

rs1 = SELECT * FROM TERRITORIES

then loop through that record set:

For each territory in rs1
Run another query
rs2 = Select * from Financial Data where TerritoryID = rs1.Territory ID
once you get the results from this query, you can export it to your text file or excel and save it.
next rs1 Record

you can search Forum705 (Access VBA) for examples on how to do all of the above.

HTH

leslie