Query ignores lines that are missing one data set

[pmatush]

Hello,

I have been tasked by my company to create a database to compare our actual costs on projects to what was budgeted for. Prior to this, I had never opened Access, and have had no internal guidance, so it has been an adventure to say the least.

We have 3 sets of data I've created a query to compare: The budget, the costs reported in the field, and the actual costs per our accounting system. Additionally, for each project, the costs are broken down into 45 different subcodes describing the classification of the work done.

I have built 3 queries, one for each data set, that sums all costs for a each subcode. Then I have another query that compares the sums for each subcode of each data set. That query works fine as long as there is data from each set for a given subcode. If there is no data for one of the three data sets, the query ignores that subcode entirely. I would prefer it to show a zero where there is no data in the comparison.

Here is my SQL code for the main query(All generated with the design view of Access. I have little understanding the coding):

SELECT Wells.WellCode, Wells.WellName, Prospects.[Prospect Name], Wells.[Frac Date], [Codes - Intangible].Sub, [Codes - Intangible].Description, AFEIntangSubCode.SumOfAFECost, WellEZIntangSubCode.SumOfWellEZCum, CostsIntangSubCode.[SumOfGross Amount], [SumOfGross Amount]-[SumOfAFECost] AS Overage, [Overage]/[SumOfAFECost] AS Percentage, Wells.Spacing
FROM (((Prospects INNER JOIN Wells ON Prospects.[Prospect Code] = Wells.Prospect) INNER JOIN AFEIntangSubCode ON Wells.WellCode = AFEIntangSubCode.WellCode) INNER JOIN WellEZIntangSubCode ON Wells.WellCode = WellEZIntangSubCode.WellCode) INNER JOIN (CostsIntangSubCode INNER JOIN [Codes - Intangible] ON CostsIntangSubCode.Sub = [Codes - Intangible].Sub) ON (AFEIntangSubCode.Sub = [Codes - Intangible].Sub) AND (WellEZIntangSubCode.Sub = [Codes - Intangible].Sub) AND (Wells.WellCode = CostsIntangSubCode.[Property #])
GROUP BY Wells.WellCode, Wells.WellName, Prospects.[Prospect Name], Wells.[Frac Date], [Codes - Intangible].Sub, [Codes - Intangible].Description, AFEIntangSubCode.SumOfAFECost, WellEZIntangSubCode.SumOfWellEZCum, CostsIntangSubCode.[SumOfGross Amount], Wells.Spacing, Prospects.[Prospect Code]


HAVING (((Wells.WellCode) Like IIf(IsNull([Forms]![Subcode Analysis]![WellCode]),"*",[Forms]![Subcode Analysis]![WellCode])) AND ((Prospects.[Prospect Name]) Like IIf(IsNull([Forms]![Subcode Analysis]![ProspectName]),"*",[Forms]![Subcode Analysis]![ProspectName])) AND ((Wells.[Frac Date]) Between [Forms]![Subcode Analysis]![StartDate] And [Forms]![Subcode Analysis]![EndDate]) AND (([Codes - Intangible].Sub) Like IIf(IsNull([Forms]![Subcode Analysis]![SubCode]),"*",[Forms]![Subcode Analysis]![SubCode])) AND ((Wells.Spacing) Like IIf(IsNull([Forms]![Subcode Analysis]![DSUSize]),"*",[Forms]![Subcode Analysis]![DSUSize])))
ORDER BY Wells.[Frac Date];

I think I can solve the problem by going through and entering zeros for each subcode that currently don't have costs, but I expect there's a much more elegant, and obvious solution.

Thanks for your help,

-Patrick

[June7]

To get records for the subcodes that don't have data, would need a 'master' data source that is a list of all possible subcodes. Join this table to other tables that also have the subcode values, linking on the PK/FK subcode fields. Set the jointype as "Include all records from MasterSubcodes and only those from ..."

[pmatush]

I had created a master subcode list previously, and after changing the jointype, I'm getting an error citing ambiguous joins. I've found a solution that recommends creating one query for the first data set, then another that uses the first query to compare it to the second data set. For my purposes, would I have to create a third query?

Thanks,
Patrick

[June7]

By 'compare' you mean budget vs. field vs. actual?

I thought you already had 3 queries - one for each category (budget, field, actual) - that summarize the data? Now take each of those along with the master subcode table and create another query. Each of the 3 queries will join to the master, jointype "Include all records from master and ...". This query will not be an aggregate, just a SELECT with joins.

[pmatush]

I thought your solution was gonna do it, but after playing with it, I think I'm misunderstanding still. Am I creating 3 separate queries, one for each join of the summary queries to the master subcode table, or one that joins all three summaries to the mast subcode table at once?

I think the problem I'm having is that the subcodes are all related to the project numbers in the summary queries, so in the query where I'm joining the summaries to the master code table, I need to display which project each subcodes cost is associated with. I have constructed the summary queries such that they show all projects and all subcodes at once. ie, Each project has a different row for each subcode that has costs entered. It looks like this.

Project1 1 $1000
Project1 3 $2000
Project1 5 $600
Project2 1 $1100
Project2 2 $1500
Project2 3 $2100

Problem is, since Project1 does not have costs for 2, if I compare it to the budgeted summary, which does have a costs for subcode 2, the query will ignore that row entirely. I would prefer my summary table to look like:

Project1 1 $1000
Project1 2 $0
Project1 3 $2000
Project1 4 $0
Project1 5 $600
Project2 1 $1100
Project2 2 $1500
Project2 3 $2100
Project2 4 $0
Project2 5 $0

I went with this design this initially because I want the comparison query to be able to compare all subcodes on one project, or compare one subcode across different projects, depending on the filters that the user enters in a form I created.

I first attempted implementing your suggestion by joining the master code list with one summary table, but the only change is a list of the codes without project numbers from the master list on above of what it has been outputting, I need it to be telling the summary query to have a row for each subcode for every project number. Even if its a zero.

I'm assuming there is a simple solution for this, but I think the main problem is my inability to explain the issue in terms that real dba types understand. I really appreciate your help.

Thank You,
Patrick

[June7]

Then I think you need a 'master' table that has all possible combinations of projects and subcodes. Then join the 3 summary queries to the one master by linking on both the project and subcode fields (compound key).

If you don't have a table of this 'master' it can probably be created with a query. You have a table of projects and a table of subcodes? Create the master by pulling both tables into a query WITHOUT a join. This will cause every record in each table to join with every record in the other table, resulting in every possible pairing of project and subcode.