Find unmatched query

[sillysod]

Ive been struggling with this problem fora while now

The wizard wont work, and ive tried fiddling around with it for wages.

I have two tables

tblAnalysis
tblAnalysisTypes

These tables are used to manage custom analysis fields that a user can store and define

ie
I wanted to be able to define an unlimited number of custom fields against any customer / supplier / order / part

so in the tblAnalysisTypes i have 4 fields
AnalysisValueID - auto number pk
AnalysisType
AnalysisCat

AnalysisType stores the Name of the custom field
AnalysisCat stores what the Analysis field is stored against
eg customers / suppliers etc

The tblAnalysis stores the values which the user selects for this analysis field

so it has fields
AnalysisValueID - auto number pk
ParentCode = long int - this stores the autonumber pk of the parent record, eg CustomerID in this case
Category - Links to the tblAnalysisType autonumber PK ie what the field is.
Value - the value of the field.


now i want to list what AnalysisTypes a customer currently doesnt have stored against it. But it just doesnt seem to work and i cant see why, its not an especially complicated setup or much different from the example in the access wizard, ie Customers and orders.

Does anyone have any suggestions

 

[1003]

Hi,
I am not totally sure that I know what you are trying to achieve but here is something I figure could work:

SELECT tblAnalysisTypes.AnalysisValueID, tblAnalysisTypes.AnalysisType
FROM tblAnalysis RIGHT JOIN tblAnalysisTypes ON (tblAnalysis.Category = tblAnalysisTypes.AnalysisValueID AND tblAnalysis.ParentCode = [Enter Customer Number])
WHERE tblAnalysis.AnalysisValueID is null AND tblAnalysisTypes.AnalysisCat = 'C';



This query assumes that a) the value stored in the AnalysisCat field of the tblAnalysisTypes table for the type used with the customers table is 'C'
b) you enter the primary key for the customer record which you want to examine as a parameter.
There is one thing which is a bit confusing: the names of the autonumber fields in both the tblAnalysisTypes and the tblAnalysis tables are the same - AnalysisValueID.
I would change the name in the tblAnalysisTypes table to something else, say AnalysisTypeID.

 

[sillysod]

Hmm yea, i dont know why i named them the same...

Getting it to work was alot of fiddling and trial and error.

In total there are 3 tables one to store the actual values stored against the customer

one to list the values that can be stored

and one to list the values that a field can be (look up list)

But thanks, i will try that whe i get home

 

[sillysod]

OK i have redesigned the table structure on this to hopefull be a little better.

the tables are setup as follows
Table: tblAnalysisCategories
Columns

Table: tblAnalysisCategories	

Fields:
Name	    Type                Size
CategoryID  Long Integer	4
Type	    Text	        50
Name	    Text	        50
LimitToList Text	        1

Table: tblAnalysisValues

Fields:
Name	            Type	   Size
AnalysisValueID     Long Integer   4
RecordID-FK         Long Integer   4
CategoryID-FK       Long Integer   4
Value               Text           50

tblAnalysisCategories               tblAnalysisValues
CategoryID      1 -------> Many     CategoryID-FK
	
Enforced
One-To-Many

But i still cant get the darn query to work

Ideally i want to show a list of all the Analysis categories names where type = "Customers"

then show the values stored in analysis values table for any given customer eg recordID = 101

where a customer doesnt have a value stored for a particular category the category still needs to show
its getting the category name to show where a customer doesnt have a value stored which is causing me the headache
Then the user can insert records into analysis values table by typing a value against each category which doesnt have a value stored for that customer

if this happened then i wouldnt need the unmatched query since all the categories would just be there. and i wouldnt need to have the user manually add them from the list of unmatched items

 

[1003]

Hi,

I think that it shouldn't be too difficult now to construct the desired queries.

Here is the one listing the values which have been entered for a given customer:

SELECT tblAnalysisCategories.CategoryID, tblAnalysisCategories.Name
FROM tblAnalysisValues INNER JOIN tblAnalysisCategories ON (tblAnalysisValues.CategoryID  = tblAnalysisCategories.CategoryID AND  tblAnalysisValues.RecordID  = [Enter Customer Number])
WHERE tblAnalysisCategories.Type = 'Customers';

The following query will list only the values which a given customer hasn't entered yet:

SELECT tblAnalysisCategories.CategoryID, tblAnalysisCategories.Name
FROM tblAnalysisValues RIGHT JOIN tblAnalysisCategories ON ( tblAnalysisValues.CategoryID  = tblAnalysisCategories.CategoryID AND  tblAnalysisValues.RecordID  = [Enter Customer Number])
WHERE tblAnalysisValues.AnalysisValueID is null AND tblAnalysisCategories.Type = 'Customers';

Please note the brackets around the ON expression in the right join. I have noticed that Access tends to remove the brackets (when you switch to design view for example) but then when the brackets are missing it won't run the query saying that it uses an unsupported join.

Also the position of the part which filters out the records for a given customer (tblAnalysisValues.RecordID = [Enter Customer Number]) is important - it needs to be part of the ON expression for the missing values query, while for the entered values query it could be part of the WHERE condition. The query would then look like this:

SELECT tblAnalysisCategories.CategoryID, tblAnalysisCategories.Name
FROM tblAnalysisValues INNER JOIN tblAnalysisCategories ON (tblAnalysisValues.CategoryID  = tblAnalysisCategories.CategoryID)
WHERE tblAnalysisCategories.Type = 'Customers'  AND  tblAnalysisValues.RecordID  = [Enter Customer Number]

I don't know how what you are planning for the user interface but what I would probably do is present all available categories of the Customers type in a combo/list box and place a text box next to this control which I would fill in response to the On_change event on the combo/list box. The code in the event handler would lookup the value with the selected categoryID for the current customer ID and put it in the text box. If the value didn't exist, then the code would clear the text box.
Then I would have two buttons, one with the Update caption and one with the Set caption. The first button would be used to update an existing value which the user modifies in the text box and the second button would be used to insert a new value for a category which hasn't been used yet.
The code in the on_change event handler would make either the Update or Set button visible, depending whether there would be or wouldn't be an existing value for the category selected in the combo/list box.

 

[sillysod]

The UI will simply be a datasheet,
listing the categories, which are locked for edit, and then having a second column which is a combo box.

Then a column just containing an asterisk (*) where that analysis field is "limit to list"
(theres also a third table containing predefined values for each category, and the category can be limited to just those value)

As i mentioned in my previous post my ideas / requirements have changed slightly

Instead of just showing the list of fields ( i know they arnt database fields, but itsmuch easier to call them this) which do have values stored against them, and then a list of those that dont on the "add analysis value" i would like to just have one list

so the query lists ALL analysis categories of a given type (ie Customers) and shows a value, if any

where a value is not present the user can simply add one, and a record is created.

I have done this before, but only on one - to - one relationships

however this is a one to many, even though its kind of a one to one (one category can only have one value per customer)


This code wont goto waste though, since i can use it to enforce mandatory fields

ie when a user tries to exit the form a recordset is created from a SQL query tweaked from the above, if the record count > 0, on fields that are mandatory then notify user/ dont let them exit.


Thanks for your help
SS

 

[1003]

Hi,

OK, I have tried it myself and I think that you won't be able to do what you are to trying achieve without using some code.
After putting in some code, I managed to get it all working the way you describe. If you want, you can download my test db here:
[URL unfurl="true"]http://www.geocities.com/drixor2004/CustomersValues.zip[/url]

Here is what you need to look at:
The values are entered in a subform called [Customer values subform] which is part the main form called [Customers]. There is no Master/Child link between the main form and the subform. The reason why the subform lists only the records related to the correct record displayed in the parent form is the query which is the record source for the subform.
This is the query called [Customers values]. This query is basically the same query as I put it in my previous post, except three modifications.
1) The RecordID criteria is now the value Forms![Customers]![CustomerID]
Beware again of the disappearing brackets!
2) I removed the is null part of the WHERE clause, meaning that the query now lists all categories defined in the tblAnalysisCategories for the Customers type
3) I added the fields from the tblAnalysisValues table because that's what would want to store here. I also added the field CategoryID from the tblAnalysisCategories table. It seems to be a duplicate here but we will need it when writing the code later.


Now, the subform wouldn't normally be updatable, so you have to change the recordset type to 'Dynaset (Inconsistent Updates)'. I also disabled additions (only existing categories should be displayed) and deletions (I think that you said that all values must be entered for a customer anyway) for the subform.
The subform would work OK for modifying existing values entered for a customer. However, it wouldn't work when adding new values. Here is the first place where you need to enter some code. It is in the Before Update event handler for the subform. The code simply sets the fields which don't have a prepolulated value and therefore would be blank in case of the inconsistent update - when adding new values for the customer:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.RecordID = Forms![Customers]![CustomerID]
    Me.CategoryID = MissingCategoryID
End Sub

The recordID is taken from the parent form and CategoryID uses the MissingCategoryID field added to the query earlier.

We are almost done. The only remaining problem is that the subform keeps showing the same values when you switch to a different customer record in the main form. This is because there is no child/master relationship defined. So we need to refresh the records manually, by putting some code in the On Current event handler for the parent form:

Private Sub Form_Current()
    subformValues.Requery
End Sub

Note that subformValues is the name of the subform control in the main form.

I didn't try to implement the limitation to a list of predefined values. I hope that it won't be to modify the example to achieve that as well.

 

[sillysod]

Hi there,
sorry it took so long to reply but i have only just got round to looking at this again.

Thanks alot, that is EXACTLY what i wanted, and it works perfectly now.

Although i did fall foul of those brackets for a few hours


Thanks again!!