Okay - I will try to explain this as best I can. I am a moderate Access user. The guy who was in my position before me built a very over-complicated and poorly written (i.e. no best practices in place here) database. It works, for the most part, but the structure and relationships are not set right. However, we are getting ready to get a new complete custom system built (not in access). However, in the mean time I need to attend to user needs as it concerns this database. There is no documentation at all and I have had to dig into every part of this thing to learn what is going on in the database.

Here is the problem:

We have a table of work orders from different regions - Q1,Q2,SZ,TZ,etc.

We have a form that captures information to help with projections 3,4,5,6-8 days out. This form is fed by query that pulls the workorders in a certain region on a certain date. All works well for the most part.

However, there are some times that there are no work orders in a particular region. Because of this, the query fails and does not display the form at all.

What I am looking to be able to do is have a "fail-safe" in such a way that it will ignore (or simply add null) for that value. I'm not sure if I can do this though..

Here are the queries and corresponding images to hopefully help in figuring this out. (sorry about the horrible SQL, but again - this is how it was handed to me and there is probably 500 queries (if not more) that are like this and I have not had time to fix them.

First, here is the first query that pulls filtered data from our main work order table:

SELECT [TBL_ACTIVE WO].[SCHEDULED DATE], [TBL_ACTIVE WO].[AM/PM], [TBL_CUSTOMER ACCOUNT INFO].[MGT AREA], [TBL_ACTIVE WO].UNITS, [TBL_ACTIVE WO].[CSG STATUS], [TBL_ACTIVE WO].CATEGORY, [TBL_CUSTOMER ACCOUNT INFO].[CUSTOMER NAME], [TBL_CUSTOMER ACCOUNT INFO].[CUSTOMER ADDRESS], [TBL_CUSTOMER ACCOUNT INFO].[CUSTOMER CITY], [TBL_CUSTOMER ACCOUNT INFO].[CUSTOMER ZIP], [TBL_CUSTOMER ACCOUNT INFO].[CUSTOMER STATE]
FROM [TBL_ACTIVE WO] INNER JOIN [TBL_CUSTOMER ACCOUNT INFO] ON [TBL_ACTIVE WO].[CSG ACCT NUMBER] = [TBL_CUSTOMER ACCOUNT INFO].[CSG ACCT NUMBER]
WHERE ((([TBL_ACTIVE WO].[SCHEDULED DATE])=Date()+3) AND (([TBL_ACTIVE WO].[CSG STATUS])<>"X"));


Which results in this:

http://i44.tinypic.com/t5i2qq.png


Here is where the problem starts, as you can see there are 6 regions displayed here because each of these at least had ONE record for that date. However, there is one missing in this particular example - region Q2. The reason that it is not shown is because there is not a Q2 job scheduled for that date yet.

The next query is a crosstab query that adds the totals up essentially:

TRANSFORM Sum([QRY_MAP POINT/POINT COUNTER FOR 3DAY].UNITS) AS SumOfUNITS
SELECT [QRY_MAP POINT/POINT COUNTER FOR 3DAY].[SCHEDULED DATE], Sum([QRY_MAP POINT/POINT COUNTER FOR 3DAY].UNITS) AS TOTAL
FROM [QRY_MAP POINT/POINT COUNTER FOR 3DAY]
GROUP BY [QRY_MAP POINT/POINT COUNTER FOR 3DAY].[SCHEDULED DATE]
PIVOT [QRY_MAP POINT/POINT COUNTER FOR 3DAY].[MGT AREA];


Which in the above example, gives us this:

http://i44.tinypic.com/krl1f.png

Then the next query, which is this (BIG ONE):

SELECT [QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab].[SCHEDULED DATE], [QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab].TOTAL, [QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab].Q1 AS FILLQ1, [QRY_MAX POINT 14 DAYS].Q1 AS MAXQ1, [QRY_VACATION POINT 14 DAYS].Q1 AS VACQ1, [MAXQ1]-[VACQ1] AS HANDPTSQ1, [FILLQ1]/[HANDPTSQ1] AS [FILL&#37;Q1], [HANDPTSQ1]-[FILLQ1] AS NEEDQ1, [QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab].Q2 AS FILLQ2, [QRY_MAX POINT 14 DAYS].Q2 AS MAXQ2, [QRY_VACATION POINT 14 DAYS].Q2 AS VACQ2, [MAXQ2]-[VACQ2] AS HANDPTSQ2, IIf([HANDPTSQ2]=0,Null,[FILLQ2]/[HANDPTSQ2]) AS [FILL%Q2], [HANDPTSQ2]-[FILLQ2] AS NEEDQ2, [QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab].QZ AS FILLQZ, [QRY_MAX POINT 14 DAYS].QZ AS MAXQZ, [QRY_VACATION POINT 14 DAYS].QZ AS VACQZ, [MAXQZ]-[VACQZ] AS HANDPTSQZ, [FILLQZ]/[HANDPTSQZ] AS [FILL%QZ], [HANDPTSQZ]-[FILLQZ] AS NEEDQZ, [QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab].S3 AS FILLS3, [QRY_MAX POINT 14 DAYS].S3 AS MAXS3, [QRY_VACATION POINT 14 DAYS].S3 AS VACS3, [MAXS3]-[VACS3] AS HANDPTSS3, [FILLS3]/[HANDPTSS3] AS [FILL%S3], [HANDPTSS3]-[FILLS3] AS NEEDS3, [QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab].S4 AS FILLS4, [QRY_MAX POINT 14 DAYS].S4 AS MAXS4, [QRY_VACATION POINT 14 DAYS].S4 AS VACS4, [MAXS4]-[VACS4] AS HANDPTSS4, [FILLS4]/[HANDPTSS4] AS [FILL%S4], [HANDPTSS4]-[FILLS4] AS NEEDS4, [QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab].S7 AS FILLS7, [QRY_MAX POINT 14 DAYS].S7 AS MAXS7, [QRY_VACATION POINT 14 DAYS].S7 AS VACS7, [MAXS7]-[VACS7] AS HANDPTSS7, [FILLS7]/[HANDPTSS7] AS [FILL%S7], [HANDPTSS7]-[FILLS7] AS NEEDS7, [QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab].TZ AS FILLTZ, [QRY_MAX POINT 14 DAYS].TZ AS MAXTZ, [QRY_VACATION POINT 14 DAYS].TZ AS VACTZ, [MAXTZ]-[VACTZ] AS HANDPTSTZ, [FILLTZ]/[HANDPTSTZ] AS [FILL%TZ], [HANDPTSTZ]-[FILLTZ] AS NEEDTZ, [FILLQ1]+[FILLQ2]+[FILLQZ]+[FILLS3]+[FILLS4]+[FILLS7]+[FILLTZ] AS TOTALFILL, [HANDPTSQ1]+[HANDPTSQ2]+[HANDPTSQZ]+[HANDPTSS3]+[HANDPTSS4]+[HANDPTSS7]+[HANDPTSTZ] AS TOTALHANDPTS, [TOTALHANDPTS]-[TOTALFILL] AS TOTALNEEDED, [TOTALFILL]/[TOTALHANDPTS] AS [TOTAL%]
FROM ([QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab] INNER JOIN [QRY_MAX POINT 14 DAYS] ON [QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab].[SCHEDULED DATE] = [QRY_MAX POINT 14 DAYS].[SCHEDULE DATE]) INNER JOIN [QRY_VACATION POINT 14 DAYS] ON [QRY_MAP POINT/POINT COUNTER FOR 3DAY_Crosstab].[SCHEDULED DATE] = [QRY_VACATION POINT 14 DAYS].[SCHEDULE DATE];

I know it seems a bit complicated (and I am sure that it is), but really what it is doing is pulling the data from the previous query and assigning them to variables that will then fill the aforementioned form.

Notice that it is trying to pull Q2 data here and this is why this and the form fails (this is the query that fails).

Is there any way to reconfigure this so that if there is no data for Q2 (or any of the regions) then it will not include it, or at least replace it wil NULL or something to that effect.

Am I out of luck here? Thanks!

Mike


EDIT: Is there any way to accomplish this with a default value. This way there is a value at all times?

Firstly if you do not have a tabel with all your regions in it then create one. UJust a code and description should suffice. Use the Code as the primary key. Now in your first query bring this table into the design pane and create a link between the region code and the field in the main table. Set the join so that all regions are listed even if there are none in the main table.

That way you should get zero records for each region with no orders. You may need to use Nz() to show zero instead of Null in your counting/summing column(s).

Thank you for the advice..I will see what I can do. I know I can create the table no problem, but may need a little bit more help with the query modification. I will play around with it first though to see what I can work out.

Thanks again!

Mike

I created the table and added it as you suggested to the first query (that pulls all the records from the main data table) and created a join (third option on the join options) that forces the new table rows to be displayed even if they are not present in the main data table.

Alas, I am getting an error stating that. The SQL statement could not be executed because it contains ambiguous outer joins.

Here is the SQL:
SELECT [TBL_ACTIVE WO].[SCHEDULED DATE], [TBL_ACTIVE WO].[AM/PM], [TBL_CUSTOMER ACCOUNT INFO].[MGT AREA], [TBL_ACTIVE WO].UNITS, [TBL_ACTIVE WO].[CSG STATUS], [TBL_ACTIVE WO].CATEGORY, [TBL_CUSTOMER ACCOUNT INFO].[CUSTOMER NAME], [TBL_CUSTOMER ACCOUNT INFO].[CUSTOMER ADDRESS], [TBL_CUSTOMER ACCOUNT INFO].[CUSTOMER CITY], [TBL_CUSTOMER ACCOUNT INFO].[CUSTOMER ZIP], [TBL_CUSTOMER ACCOUNT INFO].[CUSTOMER STATE]
FROM ([TBL_ACTIVE WO] INNER JOIN [TBL_CUSTOMER ACCOUNT INFO] ON [TBL_ACTIVE WO].[CSG ACCT NUMBER] = [TBL_CUSTOMER ACCOUNT INFO].[CSG ACCT NUMBER]) RIGHT JOIN TBL_MGMT_AREAS_FOR_PROJECTION ON [TBL_CUSTOMER ACCOUNT INFO].[MGT AREA] = TBL_MGMT_AREAS_FOR_PROJECTION.[MGT AREA]
WHERE ((([TBL_ACTIVE WO].[SCHEDULED DATE])=Date()+2) AND (([TBL_ACTIVE WO].[CSG STATUS])<>"X"));

Does this mean that I have to create a separate query to join this new table with the results from the original first query? If so, I tried and it would not work. I could get this new query to show in datasheet view, but there was one row that was blank all the way across. When I tried to run the next query (which was the crosstab query in my original post) I got an error stating that there was a cicular reference caused by the new query.

Am I making this too complicated or am I just not gettig it. You have gotten me hopeful that this can be fixed which would solve a 5 month long headache. I really appreciate your help!

Mike