Join two tables into one Query with expressions

[Sassy7]

I have been working on merging two tables and writing expressions for calculations of columns connecting them through a Service Number that are the same in both tables. When I create the query, I am receiving data for one of the tables where data does not exist - one table has data dating back to 2004 and the other has data for only 2012. I am trying to group by date then by month. I am writing expressions to total the rows in certain columns to create graphs. How do I tell Access not to populate rows with data that does not exist in the one table?

Maybe if you can see the SQL, you might be able to help me. I am not a programmer and not used to working in SQL; I'm more visual, hands on instead of writing the program. Can you help?:

SELECT Format$([Water - Master Database - APS Electrical].[Start Date],'yyyy') AS [Start Date By Year], Format$([Water - Master Database - APS Electrical].[Start Date],'mm') AS [Start Date By Month], Sum([Solar - Master Database - ISH Solar - Electrical].[Solar Total kWh]) AS [SumOfSolar Total kWh], Sum([Solar - Master Database - ISH Solar - Electrical].[Solar Billed Amount]) AS [SumOfSolar Billed Amount], Sum([Water - Master Database - APS Electrical].[Total kWh]) AS [SumOfTotal kWh], Sum([Water - Master Database - APS Electrical].[Billed Amount]) AS [SumOfBilled Amount], Sum([Solar total kwh])+Sum([total kwh]) AS Expr1, Sum([Solar billed amount])+Sum([billed amount]) AS Expr2, (Sum([solar billed amount])+Sum([billed amount]))/(Sum([solar total kwh])+Sum([total kwh])) AS expr3
FROM [Water - Master Database - APS Electrical] RIGHT JOIN [Solar - Master Database - ISH Solar - Electrical] ON [Water - Master Database - APS Electrical].[Service Number] = [Solar - Master Database - ISH Solar - Electrical].[Related APS Service Number]
WHERE ((([Solar - Master Database - ISH Solar - Electrical].[Related APS Service Number]) Like "2286s01285" Or ([Solar - Master Database - ISH Solar - Electrical].[Related APS Service Number]) Like "3646s51287") AND (([Water - Master Database - APS Electrical].[Service Number]) Like "2286s01285" Or ([Water - Master Database - APS Electrical].[Service Number]) Like "3646s51287"))
GROUP BY Format$([Water - Master Database - APS Electrical].[Start Date],'yyyy'), Format$([Water - Master Database - APS Electrical].[Start Date],'mm');

[June7]

Instead of Format function, use Year() and Month() to extract the date parts.

LIKE operator without wildcard is not useful, just use = sign.

Don't understand your issue with the data output. Show sample source data and desired output. Or provide db for analysis. Follow instructions at bottom of my post.

[Sassy7]

There is data pulling in the rows for 2004 from the Solar table where they do not exist for that year. Solar project was implemented in 2012. Existing records for 2004 on is from the second table. When I run the query, it returns rows of data for the Solar table for 2004 as well?? But, it only has records for 2012 on. Hope the sample below helps you understand what I am trying to do. I only want to pull records from the Solar table that exist for 2012 on; it appears the query wants to populate rows wherever data does not exist for Solar to match the rows of the second table? There shouldn't be any data for rows 2004 through 2012 for the Solar table?

Start Date By Year	Start Date By Month	SumOfSolar Total kWh	SumOfSolar Billed Amount	SumOfTotal kWh	SumOfBilled Amount	Total kWh Combined	Billed Amount Combined	Total Billed Amt/Total kWh Combined
2004	02	870220	$81,128.89	1166400	$94,897.35	2,036,620.00	$176,026.24	0.09
2004	03	739518	$68,943.80	108000	$13,544.55	847,518.00	$82,488.35	0.10
2004	04	870220	$81,128.89	1341360	$99,755.46	2,211,580.00	$180,884.35	0.08
2004	05	870220	$81,128.89	1848240	$138,129.21	2,718,460.00	$219,258.10	0.08
2004	06	870220	$81,128.89	1612080	$127,317.60	2,482,300.00	$208,446.49	0.08
2004	12	870220	$81,128.89	752400	$74,981.88	1,622,620.00	$156,110.77	0.10
2005	01	870220	$81,128.89	581040	$61,659.81	1,451,260.00	$142,788.70	0.10
2005	03	1740440	$162,257.78	1326240	$145,526.22	3,066,680.00	$307,784.00	0.10
2005	04	870220	$81,128.89	1035360	$128,570.40	1,905,580.00	$209,699.29	0.11
2005	05	870220	$81,128.89	1429920	$137,109.33	2,300,140.00	$218,238.22	0.09
2005	12	870220	$81,128.89	812160	$89,707.68	1,682,380.00	$170,836.57	0.10
2006	01	870220	$81,128.89	790560	$90,588.78	1,660,780.00	$171,717.67	0.10
2006	03	1740440	$162,257.78	1562400	$180,601.56	3,302,840.00	$342,859.34	0.10
2006	07	870220	$81,128.89	1146960	$138,201.93	2,017,180.00	$219,330.82	0.11
2006	08	870220	$81,128.89	1128960	$132,094.53	1,999,180.00	$213,223.42	0.11
2006	09	870220	$81,128.89	959040	$121,377.15	1,829,260.00	$202,506.04	0.11
2006	10	870220	$81,128.89	1026000	$115,024.68	1,896,220.00	$196,153.57	0.10
2006	11	870220	$81,128.89	837360	$102,351.33	1,707,580.00	$183,480.22	0.11
2006	12	870220	$81,128.89	791280	$97,822.44	1,661,500.00	$178,951.33	0.11
2007	01	870220	$81,128.89	821520	$100,308.78	1,691,740.00	$181,437.67	0.11
2007	03	1740440	$162,257.78	2125440	$228,156.48	3,865,880.00	$390,414.26	0.10
2007	04	870220	$81,128.89	1381680	$146,654.37	2,251,900.00	$227,783.26	0.10
2007	05	870220	$81,128.89	1392480	$147,156.66	2,262,700.00	$228,285.55	0.10
2007	06	870220	$81,128.89	1620000	$177,248.61	2,490,220.00	$258,377.50	0.10
2007	11	870220	$81,128.89	1604160	$140,273.73	2,474,380.00	$221,402.62	0.09
2007	12	870220	$81,128.89	907200	$104,872.68	1,777,420.00	$186,001.57	0.10
2008	01	870220	$81,128.89	1026000	$119,731.50	1,896,220.00	$200,860.39	0.11
2008	02	870220	$81,128.89	894240	$110,398.59	1,764,460.00	$191,527.48	0.11
2008	03	870220	$81,128.89	1046160	$118,004.49	1,916,380.00	$199,133.38	0.10
2008	04	870220	$81,128.89	1129680	$146,882.43	1,999,900.00	$228,011.32	0.11
2008	05	870220	$81,128.89	1290960	$158,727.51	2,161,180.00	$239,856.40	0.11
2008	06	870220	$81,128.89	1298160	$156,644.73	2,168,380.00	$237,773.62	0.11
2008	12	870220	$81,128.89	1245600	$128,125.80	2,115,820.00	$209,254.69	0.10
2009	01	870220	$81,128.89	1227600	$129,971.61	2,097,820.00	$211,100.50	0.10
2009	02	870220	$81,128.89	1308960	$134,140.14	2,179,180.00	$215,269.03	0.10
2009	04	870220	$81,128.89	1903680	$169,065.54	2,773,900.00	$250,194.43	0.09
2009	05	1740440	$162,257.78	3592080	$388,395.63	5,332,520.00	$550,653.41	0.10
2009	06	870220	$81,128.89	1789200	$198,583.65	2,659,420.00	$279,712.54	0.11
2009	07	870220	$81,128.89	1739520	$198,084.06	2,609,740.00	$279,212.95	0.11
2009	12	870220	$81,128.89	1428480	$144,574.56	2,298,700.00	$225,703.45	0.10
2010	01	870220	$81,128.89	1638000	$162,588.06	2,508,220.00	$243,716.95	0.10
2010	03	1740440	$162,257.78	3111120	$309,492.63	4,851,560.00	$471,750.41	0.10
2010	04	870220	$81,128.89	1668960	$186,910.38	2,539,180.00	$268,039.27	0.11
2010	05	870220	$81,128.89	2117520	$219,214.62	2,987,740.00	$300,343.51	0.10
2010	06	870220	$81,128.89	1926000	$214,867.89	2,796,220.00	$295,996.78	0.11
2010	12	1740440	$162,257.78	2905200	$306,109.89	4,645,640.00	$468,367.67	0.10
2011	01	870220	$81,128.89	1728720	$158,470.20	2,598,940.00	$239,599.09	0.09
2011	03	870220	$81,128.89	1536480	$160,837.29	2,406,700.00	$241,966.18	0.10
2011	04	870220	$81,128.89	2082960	$212,662.53	2,953,180.00	$293,791.42	0.10
2011	05	870220	$81,128.89	2154240	$218,426.94	3,024,460.00	$299,555.83	0.10
2011	12	870220	$81,128.89	1851840	$162,374.40	2,722,060.00	$243,503.29	0.09
2012	01	870220	$81,128.89	1694160	$158,840.28	2,564,380.00	$239,969.17	0.09
2012	03	1740440	$162,257.78	2126160	$125,460.00	3,866,600.00	$287,717.78	0.07
2012	04	870220	$81,128.89	1709280	$172,435.59	2,579,500.00	$253,564.48	0.10
2012	05	870220	$81,128.89	0	$0.00	870,220.00	$81,128.89	0.09
2012	09	870220	$81,128.89	935280	$68,053.59	1,805,500.00	$149,182.48	0.08
2012	10	870220	$81,128.89	869040	$65,589.21	1,739,260.00	$146,718.10	0.08

[June7]

I can only think it is because you are joining on Service Number alone. Maybe also include year in the join clause. Or maybe what you really need is a UNION query then do the GROUP BY on that dataset. Since I don't know what the original raw data is like, hard to say.

[Sassy7]

Thanks. I tried that but it would not join the expressions. I was trying to join two expressions from two different queries. I ended up using two separate queries and created two separate charts for the expressions. I could not use two different queries in one chart either. Thanks anyway.