sql problem 1

[taopeng]

I have a question about a query setup.

I have a table called tblPersonality with 16 fields. One of the them is "Personality types". The rest of them are personality indicators which are all yes/no fields. There are altogether 10 records in the table.

I want users to select indicators from a form, then base on their selection, I will report to them what kind of personality thet have. I will say something like "It's possible that you have the following types of personalities: ..."

My problem is that I cannot get the result I want.

Here is how I do it:

I created a user table called tblInput that's linked to the input form. My SQL statement is
select personlity from tblPersonlity where
((tblePersonality.indicator1=Yes)and(tblInput.indicator1=Yes))OR ((tblePersonality.indicator2=Yes)and(tblInput.indicator2=Yes))OR etc all the way to indicator 10. What I got is all of the records. If I change OR to AND, it will be too stringent on users meaning unless they choose all the indicators for a personality they won't get anything.

How should I change it? Thank you.

 

[GingerR]

ok, what i think you want is sort of a decending list of hits, i.e.

You most match Personality Type
Then the Type with the second highest matches
Then the Type with the third highest matches
etc, right?

to do this you have to first find out which Personality types hit each user check, i.e.

Which Types match what the user input for Indicator 1
Which Types match what the user input for Indicator 2
and so on, right? then see which Type matched the most times, then the second-most times, etc.
you may know better sql but this is what i got-make a query called TypeResultList and put in this sql. you will have to add more for all of your 16 fields (i only went up to 5) and make sure the table names are correct (i used what you had written above):

SELECT 1 AS Indicator, tblePersonality.Type
FROM tblInput INNER JOIN tblePersonality ON tblInput.Indicator1 = tblePersonality.Indicator1 UNION 
SELECT 2 AS Indicator, tblePersonality.Type
FROM tblInput INNER JOIN tblePersonality ON tblInput.Indicator2 = tblePersonality.Indicator2 UNION
SELECT 3 AS Indicator, tblePersonality.Type
FROM tblInput INNER JOIN tblePersonality ON tblInput.Indicator3 = tblePersonality.Indicator3 UNION
SELECT 4 AS Indicator, tblePersonality.Type
FROM tblInput INNER JOIN tblePersonality ON tblInput.Indicator4 = tblePersonality.Indicator4 UNION 
SELECT 5 AS Indicator, tblePersonality.Type
FROM tblInput INNER JOIN tblePersonality ON tblInput.Indicator5 = tblePersonality.Indicator5;

then you can write a final query ( i dont know how to do this within the previous query) that is a Totals query with a count - sorted descending so you have the type with the most hits listed first:

SELECT TypeResultList.Type, Count(TypeResultList.Type) AS Count
FROM TypeResultList
GROUP BY TypeResultList.Type
ORDER BY Count(TypeResultList.Type) DESC;

so your result is:
Type Count
OK 5
Lame 4
Lamer 2
Lamest 1
ok? let me know how it goes.

g

 

[taopeng]

Hi g,

Thank you so much for your patient explanation. I deeply

appreciate it.

I tried it out. The Totals worked for a user input. I

still have two questions in mind though. One is that I

expect the query TypeResultList shows those types that

match what the user input for Indicator 1 and those

types that match what the user input for indicator 2 and

so on. But it seems to me it showed results only based

on the tblPersonality no matter what user the input is.

I may have some misunderstanding on this otherwise how

can the Totals query worked fine?

Second question: when I tried to change the user input,

the query still remembered what was there the first

time, so it didn't work. How do I erase the former input

in tblInput everytime I want to have a new user input?

My ultimate goal is to link form to user input and link

query Totals to a report so I do need to show different

report for different users.

Can you help me on this? Thanks!

Tao

 

[GingerR]

maybe *I* don't understand?

1) when i put into the INPUT FORM these things:
Indicator1: NO
Indicator2: NO
Indicator3: YES
Indicator4: YES
Indicator5: NO
TypeResultsList query returns:
Indicator Type
1 Lamer
1 Lamest
2 OK
3 Lame
3 Lamer
3 OK
4 Lamer
4 Lamest
5 Lame
5 Lamer
5 Lamest
5 OK

when I change my input to
Indicator1: NO
Indicator2: NO
Indicator3: NO
Indicator4: YES
Indicator5: YES
TypeResultsList returns:
Indicator Type
1 Lamer
1 Lamest
2 OK
3 Lamest
4 Lamer
4 Lamest

obviously these are diff results for diff inputs. What it is showing is the personality type that matches what the user input, as you requested. For both examples, Indicator1 is NO, and that matches "Lamer" and "Lamest" personality types. #2 is NO and that matches personality type "OK". for the first example, #3 is YES and that matches three diff personality types, but in the second example when the user input for #3 is NO, it only matches one "Lamest" personality type. If you try different inputs and run this query, I don't know why you would not get different results each time like I do, unless you have every personality type checked both yes and no for every indicator which I doubt you do. perhaps it just appears to you that the results are the same but please look closely.

2) The query doesn't 'remember' what is in the user input, It goes off of what is currently there. Do not add a new record to the user input table, just click off and on the boxes you want checked or not in the single first record. close the table before running the query to make sure the checks you just made are saved to memory. so re-try this, making sure you close the INPUT table first. later when you put this on a form, when the user clicks a button, your first command will be to SAVE.

so try again, ok? let me know how it goes.

g

 

[taopeng]

Hi g,

It worked! Thank you.

I know why I was confused now. Somehow, I thought I

would get only those personality types when indicators

in both tables are YES. Of course that requires a where

clause that says that.

Your detailed explanation really helped a lot.

One more question: right now I have a command button

called "show" in the form. when clicked, it will trigger

openReport event and the report opened is attached to

query Totals. You mentioned the "save" command, can you

specify what code that needs to be associated with it?

Thank you,

Tao

 

[GingerR]

put in this code first in your OnClick event so it will save the check marks you just made. the same thing would happen if you went to a new or previous record (which you wont do because you aren't entering new records into the INPUT table) or if you close the form, for instance. but you wont be doing those things, so you force a save. otherwise like you experienced before, it won't have sucked in the new choices.

DoCmd.RunCommand acCmdSaveRecord

a question: are mulitple people are using this at the same time? unless you are somehow saving each user's input it's funky that you are typing the check marks into a table. it won't work if you have multiple users, unless they each have this on their own computers. usually you would just have unbound controls on the form, push the button, get the results, and go away. no saving of the check marks. let me know your setup so we can make sure it will be ok.

g

 

[taopeng]

Hi g,

Thank you for pointing out the set-up issue. It is supposed to be a utility program on a website. So anyone can go in to check off some indicators and get their personality type and then go away. Indeed, it's probably not necessary to save a record. But then how do I make all the indicators in the tblInput unchecked everytime that a new user comes in? I mean that I should't let a user uncheck previous choices on the form and then make his/her own choice. Closing the form doesn't seem to solve the problem. When I come back in, I will still see the choices I made the first time. There got to be a way to have some code in the form or the report that clears the check marks after the report is open. But I don't know how.

Also, user does not type directly into the tblInput. They check the checkmarks in the frmInput which is directly linked to tblInput. If I don't have tblInput, how do I do querys on both tblInput and tblPersonality?

I'm relatively new to access. What's unbound control? Of course I know what a control is, but unbound?

Thank you again for being patient with my long questions

Tao

 

[GingerR]

unbound control means it doesnt have a table or query in the form's record source. it means you don't have a table tblInput. there's just i.e. check box like you want, where for example the default is set to NO (not checked) and every time you open the form it is no (not checked).

then in your queries, instead of referencing tblInput in your queries, you'd use

Forms!FormName!CheckBoxName

as a criteria in a query instead. it would be a different set up that what i've already given you, but it's the way to go.

how are you making this part of a web site? are you using ASP or something? how is a user getting to your database?

as for leaving it with tblInput and making all the fields not checked.....in your form's OnOpen event, put this

Dim ctl As Control
For Each ctl In Me.Controls
ctl.value = No
Next ctl

i think that will do it...it will set each check box to NO (not checked) whenever the form opens.

g

 

[taopeng]

Hi g,

It does make sense to make unbound controls in a form instead of a table.
As for query, before we had
SELECT 1 AS Indicator, tblPersonality.Type
FROM tblInput INNER JOIN tblPersonality ON tblInput.Indicator1 = tblPersonality.Indicator1 UNION
SELECT 2 AS Indicator, tblPersonality.Type
FROM tblInput INNER JOIN tblPersonality ON tblInput.Indicator2 = tblPersonality.Indicator2 UNION etc etc.

so now we change it to

SELECT 1 AS Indicator, tblPersonality.Type
FROM tblPersonality where tblPersonality.indicator1 = Forms!FrmInput!Checkbox1 UNION
SELECT 2 AS Indicator, tblPersonality.Type
FROM tblPersonality where tblPersonality.indicator2 = Forms!FrmInput!Checkbox2 UNION etc etc?

I want to make sure I can do this before I add 16 some fields to a form by hand.

ASP is the only thing I know that can connect user to a database in server. Maybe there are other ways? ColdFusion? Java?

When I put in the following code

Private Sub Form_Open(Cancel As Integer)

Dim ctl As Control
For Each ctl In Me.Controls
ctl.value = No
Next ctl

End sub

Access just doesn't recognize the No, it keeps saying variable no not defined. How come?

Thanks,

Tao

 

[GingerR]

try 0 instead of No then. all means the same thing. if it doesnt for some reason try writing the thing in a macro first, then converting the macro to a module and see what the module code says.

i'll re-read the beginning of your note and think about it. i usually write stuff in queries then look at the sql. i'm not good at sql itself, well i guess i am just not tweeking it without screwing around in the queries first.

g

 

[GingerR]

asp is good. so you are using asp? are you making a form/buttons, etc in asp?

 

[GingerR]

ok--put it in the On Load event instad of On Open.

also--i think that if you have controls other than the check boxes, it's best to do something like this too (i'm making every check box start with the letters IND to differentiate them from any other controls you might have on the form, like other text boxes or whatever. you can ignore the IF...END IF part if you dont have this going on)


Dim ctl As Control
For Each ctl In Me.Controls
If Left(ctl.Name, 3) = "IND" Then
ctl.Value = 0
End If
Next ctl

 

[taopeng]

Hi g,
I haven't tried your new post yet. Since whatever I put in the ctl.value = No/0/false did't work, I finally used the unbound form as frmInput and based query on form and tblPersonality. It seemed working! Thank you so much for all your help. You've been so patient even though I asked a lot of elementary questions. This forum is so great I learned a lot more from real person than from books. Best wishes to you.
As for ASP stuff, I will worry much later. Right now, I just need to get the access database to work.
Once again, thank you.
Tao