ACCESS Query is not giving all info requested

keatscon
keatscon used Ask the Experts™
on
I have set up a query requesing "Prospect Names" and "Prospect Type"
If no Criteria is added I get all records
When I set a Criteria to show only 1 type of "Prospect Type" not all of that "Prospect Type" is listed.

My SQL is
SELECT ProspectMain.[Prospect Name], ProspectMain.[Prospect Type]
FROM ProspectMain
WHERE (((ProspectMain.[Prospect Type])="KCA"))
ORDER BY ProspectMain.[Prospect Name];

What am I doing incorrectly
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I would check the data to make sure there is not a space before or after any of the KCA's. If you could provide a sample it would be easier to troubleshoot.

Flyster

Author

Commented:
Sorry, new to this,
The information contains names that are confidential
I can tell you that the "Prospect Type" field is a drop down and therefore shouldn't have spaces
Does this make a difference on how I request?
Out of 28 possible names it omits 13
Top Expert 2010

Commented:
Does this return anything?


SELECT ProspectMain.[Prospect Name], ProspectMain.[Prospect Type]
FROM ProspectMain
WHERE ProspectMain.[Prospect Type] Like "*KCA*" And ProspectMain.[Prospect Type] <> "KCA"
ORDER BY ProspectMain.[Prospect Name];

Open in new window



That will show any records that contain, but do not exactly equal, "KCA".
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Are you sure there's no other criteria it's looking for? If all records are "KCA", then you should return 28 records. Try making a query with [Prospect Type] and the add this field: Test:Len([Prospect Type]). It should return 3 on all records.
If you suspect spaces in the data, you could try to change:
My SQL is
SELECT ProspectMain.[Prospect Name], ProspectMain.[Prospect Type]
FROM ProspectMain
WHERE (((ProspectMain.[Prospect Type])="KCA"))
ORDER BY ProspectMain.[Prospect Name];

To
My SQL is
SELECT ProspectMain.[Prospect Name], ProspectMain.[Prospect Type]
FROM ProspectMain
WHERE (((Trim(ProspectMain.[Prospect Type]))="KCA"))
ORDER BY ProspectMain.[Prospect Name];

Author

Commented:
None of the suggestions corrected the problem
In doing "process of elimination" detective work, I discovered that (for some unkown reason) the entries I made on the form created that tied directly to the table was not completely working.
Entries made on the form were adding records to the table, but the entries were not being pulled when a query was done for the prospect type.
I discovered this when I decided to remove all the KCA prospect types and redo the drop down.
No records were pulled when query [Prospect Type])="KCA" was then done.
Odd thing is I did this on the ProspectMain table

I did discover that in the Prospect Main table if I tried to do a filter on the Prospect Type column I got the following message. Syntex-error.docx

Am I correct in assuming that if I delete the Prospect Type table and remove it form the Prospect Main table, recreate and add the new Prospect Type in Prospect Main it may correct?
Top Expert 2010

Commented:
keatscon,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

The sample file should clearly illustrate both the input and the expected output given that input.  The sample file need not be very large, but it should have enough examples to cover the expected range of values/scenarios.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

Patrick

Author

Commented:
I have attached a condensed version of my files
If you run the query as is it will show all records
By requesting just:
KCA   non show
CBTA, CBPP CBTA, CBPP KCA or TRANS TAX not all show

All records should show in respective sections

 Access-Copy-file.accdb
Top Expert 2010

Commented:
keatscon,

In the file you attached, there are ZERO records where the Prospect Type is "KCA".  There are two where it is "CBPP KCA", and there are four where it is "10".

Looking at your other table, I see that that is the ID value corresponding to KCA, but absent any kind of join, how could you expect that value to translate?

Patrick
Top Expert 2010
Commented:
The following seems to be working based on your sample file:

SELECT m.ProspectID, m.[Prospect Name], Nz(t.[Prospect Type], m.[Prospect Type]) AS ProspectType
FROM [Copy Of ProspectMain] m LEFT JOIN [Copy Of ProspectType] t ON m.[Prospect Type] = CStr(t.ID)

Open in new window


It first tries to match to the ProspectType table, and defaults to the ProspectMain table if there is no match.

Note that a type conversion is needed.
Top Expert 2010

Commented:
To limit to just KCA:


SELECT m.ProspectID, m.[Prospect Name], Nz(t.[Prospect Type], m.[Prospect Type]) AS ProspectType
FROM [Copy Of ProspectMain] m LEFT JOIN [Copy Of ProspectType] t ON m.[Prospect Type] = CStr(t.ID)
WHERE Nz(t.[Prospect Type], m.[Prospect Type]) = "KCA"

Open in new window

You combo box stores the bound column and not the text.
To locate just the KCA, use the key value "10" for the criteria in the query.

See attached.
Access-Copy-file-2011-08-09.accdb

Author

Commented:
OK that is odd
As you can see form the screen shot attached, my Prospect Main shows the correct listing for examples shown.
The remaining ones also were correct on my Prospect Main
Why would it not be the same on the copy I produced for your viewing?
All that you show a number instead of the alpha are ones I am missing when I run a query
 Screen shot example of my original Prospect Main
Top Expert 2010

Commented:
keatscon,

In your original table, did you set this up as a "lookup" field?  If so, then DoDahD's explanation makes sense: you are confusing the actual bound value with the displayed value (and when you copied the tables that may have bollixed up the lookup).

If you did set this up as a lookup feld in the table design, my advice is: never, ever, ever do it again.  Great idea in theory, but in practice it causes no end of headaches.

Patrick
Yes, you have defined the table field as a lookup.
My sample works because I renamed the Copy of ... objects to drop the "Copy of ".  This let the combo box on the table field work as defined (and the change to the query to restrict to "10" to work as well)

Patrick:  Lookup fields are fine for an experienced coder that knows what to expect from the bound coulumn issues.  We can agree to disagree on the appropriate use of a lookup field.

Author

Commented:
But by asking for "10" OR 'KCA' I do get all that I need

Yes I did set it up as a look up field as I need to make it limited as to what can be entered
I did this so I could update across the board the listings as we are expecting a revamp of the Prospect Types
Unfortunately, I tested it and if I update now only a percentage changes not all.
Can you suggest how I can do this, limiting what can be entered, and allowing for accross the board updates
Such as KCA to KC Carte Blanc, etc
You can be okay with your current setup.  
Some of your test data in the example is not selected from the text box - you need to fix the old data.  Changing the data type to Number with a field property size = long integer will delete the text entries in your current database.

You need to understand the value that shows in a combo box is not the value stored.   The bound column is the value stored (the "10" and not the 'KCA').  The bound column data type must be the same as the key field in the supporting table (Autonumber = Number with a field size of Long Integer)

I have fixed the table definition and enhanced the query to permit searching on KCA versus searching on "10".
Access-Copy-file-2011-08-10.accdb
Top Expert 2010

Commented:
DoDahD,

My own opinion is that lookup fields in the table definition are bad practice, and that the better approach is to use a form for data entry/edits, and to have the form's combobox refer to the other table to get its members.

But of course, reasonable minds can differ.

:)

Patrick
Patrick:  I agree.  Reasonable minds can differ.  :-)

Also, from a SPAM email:   We don't know what we don't know!

Author

Commented:
Thanks Guys
Your info helped
Sorry, new to this and learning as I go
Thanks again for your patience

Author

Commented:
Using the sample that was corrected I was able to update my report and fix the problem

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial