SQL query help in MS ACCESS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL query help in MS ACCESS

Author

Topic

rajeshhariharan
Starting Member

2 Posts

Posted - 2004-02-27 : 18:33:06

have two table

Table 1 Forecast

Fields
Accounting Period (pull up from another table Accounting period Master)
Vendor Name (Pull up from vendor Master table)
Resource Name (Pull up from Resource master table)
Sub Code (Pull up from Sub code master
Forecast Amount

Table 2 Invoice

Fields

Accounting Period (pull up from another table Accounting period Master)
Vendor Name (Pull up from vendor Master table)
Resource Name (Pull up from Resource master table)
Sub Code (Pull up from Sub code master
Invoice Amount

There are other fields also in those tables but will not concern the present Purpose

My objective is to get a query that throws up the Forecas & Actuals data in columnar fashion by accouting period. That is
Column 1 Accoting period 1 Forecast, Column 2 accouting period 1 actuals, Column 3 Accting period 2 Forecats, Column 4 Accounting period 2 Actausl and so on. Grouped by sub code, vandor and resource name.

This list i need for all resources who are either forecasted for or for whom there are actuals.

The current query i have written is:

TRANSFORM Sum([Invoice].[Amount]) AS SumOfAmount
SELECT [invoice].[Sub Code], [invoice].[Vendor Name], [invoice].[Resource Name], [invoice].[Accounting Period]
FROM invoice
GROUP BY [invoice].[Sub Code], [invoice].[Vendor Name], [invoice].[Resource Name], [invoice].[Accounting Period], [invoice].[Sub Code], [invoice].[Resource Name]
ORDER BY [invoice].[Accounting Period], [invoice].[Vendor Name]
PIVOT [invoice].[Accounting Period];

This outputs the actuals data the way I want it. Now I need to get the forecast data in columnar form within this query. I have treid some option but have been unsuccessful.

Pls help

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-02-27 : 20:11:54

First of all, it looks like you're grouping by invoice.SubCode twice.

Next (and you might want to put this in a separate query) JOIN the forecast table to the invoice table on the four matching fields. Something like

SELECT *
FROM Invoice i
INNER JOIN Forecast f
ON i.[Accounting Period] = f.[Accounting Period]
AND i.[Vendor Name] = f.[Vendor Name]
AND i.[Resource Name] = f.[Resource Name]
AND i.[Sub Code] = f.[Sub Code]

Now that you have a query with all the fields, build your TRANSFORM query on that first query.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources