Greenhorn needs some major help!!!

[TEllison]

Ok so first off Im extremely new to access. Everything I know I've taught myself. I know nothing about code, SQL, or anything else of this nature. I know very little of the lingo so Im praying that I can interpret the responses, as well as communicate the problem without too much headache to you all. With that said I apologize in advance!!!

So heres my problem, I've created a database to store and track the quality controls that the internal auditors at our facility use. Ive come to a point where ive created a query for each department to track control completion and compliance for each month that a referencing my index table. These work fine. Im now trying to create a query to reference these queries to calculate completion and compliance percentages for each department, business unit, and as a whole. The first query is referencing the same field in 13 other queries. The problem is that when data is entered into one of these 13 queries, the new query will not display the records unless there is a record for each query being referenced. How do I make it display the records as they are entered, before there is actually a record for each referenced query?

Any help is greatly appreciated

 

[jdraw]

I suggest you take a screen capture of your tables and relationships (jpg), then zip that file and attach to a post. You need to use a zip because of your post count.

 

[plog]

If you've set up your tables properly (of which I'm skeptical), you would need a LEFT JOIN (http://en.wikipedia.org/wiki/Left_join#Left_outer_join). Its a type of join between datasources (query or table) that shows all the records from one data source regardless of if there is matching data in the other data source.

However, I'm not certain you're tables or the underlying queries are set up properly. Can you post your database? I believe if you zip it you should be able to attach it.

Edit--Scratch posting your database, post a screenshot of relationships like jdraw asks for.

 

[TEllison]

Ive tried a LEFT JOIN already and it did not yield any different results. The first queries are referencing my INDEX table which are fed from the data entered on the forms. All of this works correctly.

With the new query which is referencing the first 13 queries will on display the records if there is an actual record for each of the 13 referenced queries. I might add that multiple of the 13 queries being referenced, have me referencing the same field more than once with a different criteria (Machine #) which Im using the IIF function in the "field name" expression.

as far as zipping a copy of the database to you guys im afraid I cant do that because of some of the info in the database forms not being open for public display.

 

[TEllison]

If I enter a record in all of the 13 referenced queries it will work as intended. It only doesn't work if there is a "NULL" value in any one of the referenced queries.

 

[TEllison]

Here is an example of the expression im using in the "field name", in this particular instance there are 6 machines so I have 6 columns with same expression just different #.


BEC 105: Max(IIf([BEC's Feb Completion Query]![Machine #]="BEC 105",[BEC's Feb Completion Query]![Completed Percent],Null))

 

[plog]

I'm growing more leery of your underlying tables and queries the more you post. Having a query named after a month is a sign. Names of objects (Tables, Queries, Fields) that deliniate data ('Components1', 'Components2', 'ResultsFeb', 'ResultsApril') are always a tipoff.

Then when you use that month named query as a datasource in another query makes me question the efficiency of your system.

Can you post your database?

 

[jdraw]

You do not have to show us real data. You can use names like Donald Duck etc. to remove anything private/confidential.
A table names INDEX is falling in line with plog's concerns of your table structure.

 

[TEllison]

zipped database and tried to upload but it wouldnt let me because its too large of a file.

 

[TEllison]

I have a seperate query for each month. Which references a column in the original query name "Month". one of the required fields in the form is a combo box with months listed so it has to be entered.

 

[jdraw]

Do a compact and repair on your database, then do a zip.

 

[TEllison]

here you go guys. the query im having an issue with is Stock Prep Completion

 

[plog]

You've got so much stuff in there (and undocumented to boot), I don't know where we are at. Walk me through what you are trying to accomplish again. Be specific, use exact table and field names for where data is coming from.

 

[TEllison]

Plog: Everything is working as needed up to this point, to be honest I'm not sure if I've created everything in the right order, or in the correct manner. Again I've self taught myself everything to this point, and have far exceeded the level of anybody else at my location. Needless to say I'm pretty much on my own with this one! So before we even get started I appreciate the help! Thanks

Ok, so all of my forms are linked and feeding info to the "INDEX" table. All forms are functioning as needed/expected.

The 1st set of queries (ex. AC Repair Query) are pulling info from the "INDEX" table, w/ some internal calculations going on as well ("Field Names" multiplied by a predetermined weighted number) and in seperate columns (actual points and possible points). Again this is working as needed and as expected.

2nd set of Queries (ex. AC Repair Jan Completion Query) is pulling data from the first set of Queries. Im calculating the "Completed Percent" for each control on a monthly basis. This too is working as expected.

3rd set of Queries is where the initial problem lies. There will be 3 Queries in this group. (Stock Prep "month" Completion Query) is the first set I've created so far. It is being fed from 13 Queries in the 2nd set which you can see if you pull it up in design view. Certain Queries are being references on multiple occasions in separate columns to pull data for different machines #'s (AAM 101 and AAM 102). In the final column Im calculating the "Stock Prep Completed Percent" which for this department.

The problem is The "Stock Prep "month" Completion Query" will only display the records from the 2nd set of queries if there is a record for each referenced query for the specified month. If I enter a record for each query referenced it will show exactly as it should in the 3rd query, if any one of these records is "NULL" then the query shows no records whatsoever. If you run "Stock Prep Jan Completion Query" you can see it works great because a record exists for each referenced query, but if you run "Stock Prep Feb Completion Query" there is one referenced query with no records for the month so the query provides no feedback.

Again thank you in advance, any and all help is greatly appreciated

 

[TEllison]

Also just curious, when you say "undocumented" what are you referring to?

again im so green to access its not even funny!!! I know almost nothing when it comes to the lingo!

 

[plog]

Undocumented means I have no idea what the field [NCID- Doc L/O 2-2] contains, nor any other fields because in Table design view you've not put any description data in for that field. You know what this system does (or more accurately suppose to do), but when you come here for help we have no frame of reference. More importantly, the guy who comes after you to maintain or extend this system will have no clue what each field is for. When you create something this complex you really need to document it the best you can.

As for your situation, I really think your table structure isn't correct. First, you need an autonumber primary key in Index, something that makes each row unique. Let's call that field IndexID. From there I am fairly certain that every field that goes into the calculated field [points] should have their value into a new table, let's call this new table [Milestones]. I choose that name as a shot in the dark using the fact you are working toward a completion percentage, you may have a better name for this table.

Now, in Milestones you would have 3 fields: [IndexID] which links a row back to Index based on that new autonumber field you created; [Milestone] which would hold what is now the field name that comprise points (e.g. [Saf], [ID&], etc.)--these would no longer be fields, but values you insert into the Milestones table; [MilestoneValue] which would be what is currently the value under the correspondingly named field in Index.

For example, if you had [ID&]=7 for IndexID=9 in Index, that would now look like this in Milestones:

Milestones
[IndexID], [Milestone], [MilestoneValue]
9, "ID&", 7

Milestones would have a ton of records, which is fine. After that you need a new table which holds the value you are currently multiplying each milestone by to arrive at the points. For example you have this:

Nz([Mis-Gen]*3,0)+Nz([Mis-Met]*8,0)

So in this new table we will call MilestoneMultipliers we would put these records:

[MilestoneMultipliers]
[Milestone], [Multiplier]
"Mis-Gen", 3
"Mis-Met", 8

And so on for every value in your points calculation. Now, to calculate points we bring Milestones and MilestoneMultipliers into a query, link by [Milestone], multiply [Multiplier] by [MilestoneValue] and sum that field and Group By IndexID.

That's the way that should be done. Ultimately you need to compress done the number of queries you have--building set upon set of queries is biting you hard. You need to build Set 3 queries directly off the Index, Milestone and MilestonMultiplier tables without going through those however many queries that lead up to it in Set 1 & 2.

 

[TEllison]

Ok the field names are pretty easy to interpret on my end for me or whoever follows but I see you point. They are actually just the label descriptions from the forms for each field. So anybody on my end that will follow me will be able to reference the forms and know exactly which field is what. The reason they are so abbreviated is because of sheer amount of fields I had to reference in the query expressions and having a limit on the # of characters Im allowed to use. I originally had descriptions that were way easier to interpret.

Ok so I see what your saying so I added the IndexID and autonumber to the "Index" table. Now the way I need to separate the date is by 1st "Month" , 2nd "Business Unit", 3rd "Machine #", and then come up with a summary for each and as a whole for each month to report results. With what you said as far as the "Milestone" table it seems that this will be difficult or maybe im just overlooking something?

The mulitpliers that are used are solely for the form and the score for a particular audit. They are a weighted score to come up with a total based on a possible score which is the multiplier being multiplied by 0,1, or 3. (8 can either give you a score of 0, 8, or 24 for each "Results" field on the forms). Once the form is filled out the multipliers are obsolete other than a record of that particular audit. It then turns into "Score" per audit for each machine, per department as a whole, and per plant as a whole. There are 2 total scores. 1 being a compliance score of the shops compliance to the controls, and 2 being a completion score which is the amount of audits needed to be completed versus the obvious perfect world of 100% completed each month.

 

[plog]

The effort investment made in creating the MilestoneMultiplier table will pay for itself in spades over the long term. Suppose they decide to change the multiplier on Mis-Gen to 1. Or maybe add a new milestone in the calculation. Or remove one. With your current system what kind of effort is that going to take? How many places do you have that hard coded?

Not even that, I know I can't ensure that I would do it accurately in all those places, the tedium would make me lose focus and I know I would mistype it somewhere, somehow. With the MilestoneMultiplier you change/add/delete it just in the table and your done.

Honestly though, looking at it again, you have two many queries. You shouldn't be breaking them out by month, then trying to bring them back together. Your 3rd level queries need to be built not on all those monthly queries but upon the underlying data they are built upon. 'Stock Prep Apr Completion Query' shouldn't be built on all the April queries, it should be built upon Index. That's the real issue you are having.

The data in Stock Prep Apr Completion Query ultimately comes from Index, correct? You need to get it directly built on Index then.

 

[TEllison]

You are absolutely correct on both accounts!

Let me see what I can do.

one question, how do you get a table to pull data from another table? With the multipliers table I would need it to pull data from "Index" right?

 

[plog]

That requires a join. Read through this page: http://www.w3schools.com/sql/sql_join.asp

Then google 'MS Access Query Join example" for specific examples of how to do it in design view of Access.