Query based on combo box response

[Gabriella777_2]

I am learnign Microsoft Access as I create a contact management database -
(from the template).
I would like to create a query the counts how many times a selection is made
within the main table from a combo box.
Let me put that more simply, I have a drop down box that I can select
whether or not a contact is also a contact for another company and stating
type of contact.
Selections are "Yes - Customer", "Yes - Vendor", "Yes - Both", "No", &
"Unsure".
All I want to do is create a table from a query that displays the number of
times each was selected in the database.
So far my wandering through has not provided that. Can you help me figure
this out. Please no SQL.

 

[Gabriella777_2]

Okay - so - I got it to work - not sure I can tell you how but it worked. .
..sort of.
The count is about twice the total possible number of times it could have
been used.
How do I redefine it's perameters to count it correctly?

 

[John W. Vinson]

Okay - so - I got it to work - not sure I can tell you how but it worked. .
.sort of.
The count is about twice the total possible number of times it could have
been used.
How do I redefine it's perameters to count it correctly?

Please, Gabriella, open the query in SQL view and post it here. There's no way
any of us volunteers can fix the problem if we can't see what you're doing.

You say "please, no SQL" - that's a bit like asking an automobile mechanic
"Please fix my car, but don't open the hood". If you wish, I'll try to phrase
my answer in terms of the query grid, but be aware that if you copy and paste
SQL into your SQL window you can then toggle to the design grid view; and that
the design grid is NOT the query, it's just a tool to help you construct SQL
queries - which *are* the real query.

John W. Vinson [MVP]

 

[Gabriella777_2]

Ooops! Sorry! : ) LOL
I don't know computer language/lingo so I try not to work in the screens
that use it.

Here is the what I copied and pasted from the SQL View (which I just found :
) yesterday):

SELECT DISTINCTROW [PAI Account Exists choices].[Field1],
Sum([Contacts].[PAI Account?]) AS [Sum Of PAI Account?]
FROM [PAI Account Exists choices] INNER JOIN Contacts ON [PAI Account Exists
choices].[ID]=[Contacts].[PAI Account?]
GROUP BY [PAI Account Exists choices].[Field1];

Thanks for your help.
--
Thanks and God bless you and yours,
Gabriella777_2

 

[John W. Vinson]

Ooops! Sorry! : ) LOL
I don't know computer language/lingo so I try not to work in the screens
that use it.

Here is the what I copied and pasted from the SQL View (which I just found :
) yesterday):

SELECT DISTINCTROW [PAI Account Exists choices].[Field1],
Sum([Contacts].[PAI Account?]) AS [Sum Of PAI Account?]
FROM [PAI Account Exists choices] INNER JOIN Contacts ON [PAI Account Exists
choices].[ID]=[Contacts].[PAI Account?]
GROUP BY [PAI Account Exists choices].[Field1];

Thanks for your help.

If a given record in [PAI Account Exists Choices] has two corresponding
records in [Contacts], the sum will be over two copies of each record. If a
record has ten contacts, you'll get ten times the sum.

The reverse also applies, if a contact has multiple [PAI Account Exists
Choices] records, you'll multiply the sum.

Not knowing the structure of your data, I can't really advise what to do -
what is [Field1]? What kind of data exists in the Contacts table and what
exists in the [PAI Account Exists Choices?] table or query? What is the
datatype of [PAI Account?] - if it's not a Number, why are you summing it?

John W. Vinson [MVP]

 

[Gabriella777_2]

Here is an updated SQL
SELECT DISTINCTROW [PAI Account Exists choices].Choices, Sum(Contacts.[PAI
Account?]) AS [Sum Of PAI Account?]
FROM [PAI Account Exists choices] INNER JOIN Contacts ON [PAI Account Exists
choices].ID = Contacts.[PAI Account?]
GROUP BY [PAI Account Exists choices].Choices;

The choices are
Select One
Yes - Customer
Yes - Vendor
Yes - Both
No
Unsure

This is the referenced table for a drop down in my contacts table.
If the contact/customer for SMMC is also a Customer/vendor for PAI, one of
the yes selections is made in the drop down of the form entry.

I am setting up a query to count how many contacts received Yes - Customer
as opposed to Yes - Vendor and so on and so on.

However the data is invalid if the count is inflated.

The operation is counting not summing - just to be clear because your
directions kind of threw me until I figured out what you were thinking.
--
Thanks and God bless you and yours,
Gabriella777_2

Ooops! Sorry! : ) LOL
I don't know computer language/lingo so I try not to work in the screens
that use it.

Here is the what I copied and pasted from the SQL View (which I just found :
) yesterday):

SELECT DISTINCTROW [PAI Account Exists choices].[Field1],
Sum([Contacts].[PAI Account?]) AS [Sum Of PAI Account?]
FROM [PAI Account Exists choices] INNER JOIN Contacts ON [PAI Account Exists
choices].[ID]=[Contacts].[PAI Account?]
GROUP BY [PAI Account Exists choices].[Field1];

Thanks for your help.

If a given record in [PAI Account Exists Choices] has two corresponding
records in [Contacts], the sum will be over two copies of each record. If a
record has ten contacts, you'll get ten times the sum.

The reverse also applies, if a contact has multiple [PAI Account Exists
Choices] records, you'll multiply the sum.

Not knowing the structure of your data, I can't really advise what to do -
what is [Field1]? What kind of data exists in the Contacts table and what
exists in the [PAI Account Exists Choices?] table or query? What is the
datatype of [PAI Account?] - if it's not a Number, why are you summing it?

John W. Vinson [MVP]