Queries returning with missing info (plus asks query question twic

[spark]

Hi -
I am hoping someone out there can help me. I am at wits end trying to figure
this one out. I am using Access 2002 on Vista computer. Possibly that in
itself is the problem

My database has been evolving into a series of problematic queries. I
monitor 16 shops with over 10,000 names. When running queries it has all of a
sudden decided to ask some of the query questions twice. When it returns
information it leaves people or shops out that I know should be included in
the reports.

I finally rebuilt this database last month. Testing it each step of the way,
after each query was recreated. It seemed perfectly fine. As soon as it was
completely rebuilt the queries started asking their questions twice again!
No some of queries which would leave some of teh shops out of reports has
decided to include the ones previously missing but now leaves out other shops
it used to include.

I just finished inutting a new member. She shows up in the form page, shows
up in the complete workers list (include current and resigned employees) but
will not show up on the payment tracking or list of current employees.

I am baffled. No research online has provided me with answers. I am hoping
it is just a stupid, tiny little oversight but I am without any expereienced
help here.

I tried upgreding to office 2007 trial version to see if the database would
work better but it had the same issues. I really don't want to have to
recreate this again for testing it in 2007 from scratch unless absolutely
necessary.

I like using offcie products but the frustration level is growing as I find
it more difficult to complete my job on a daily basis. I sdon't know any
longer if the reports I am giving out are completely accurate anymore and I
don't have time to verify every ingle one I do (hell, then I should just do
it all by hand and dump the computer). If I knew of another Databse program
that I could create queries in I test it out right now.

Any help, suggestions, morale support would be appreciated.

 

[Jerry Whittle]

Do your queries contain more that one table? If so, it's probably a join
problem. While in query design view, double click on the line between the two
tables until a dialog box appears. Select Option 2 and see if that fixes
things.

 

[spark]

At least one of the problems had two tables. I tried what you said and it
didn't change the outcome. I also tried removing the second table all
together (it only links shop code with actual shop name for reports) and that
had no affect accept now the query asks me which shop I want to look at 5
times instead of twice.

 

[Jerry Whittle]

Show us the SQL for the query that you just changed and the way it was before
you removed reference to the second table. That might tell us something.

Open the query in design view. Next go to View, SQL View and copy and past
it here.

 

[spark]

orignal:

SELECT [T Master].[Site/Dept], [T Master].Initiation, [T Master].Seniority,
[T Master].First, [T Master].Last, [T Master].Dues, [T Master].Title, [T
Master].Signedcard, [T Master].paying, [T Master].Steward, [T Master].[Shop
Contact], [T Master].Address, [T Master].City, [T Master].State, [T
Master].ZIP, [T Master].[Phone #], [T Master].[Alternate #], [T Master].[Cell
#], [T Master].[E-mail], [T Master].[Umass Form], [T shop_list].[Shop Name],
[T Master].[Employee #], [T Master].NOTES
FROM [T Master] LEFT JOIN [T shop_list] ON [T Master].Plant = [T
shop_list].Code
WHERE ((([T Master].Plant)=[?shop]) AND (([T Master].Termed)<"1/1/1800"));

changed to:

SELECT [T Master].[Site/Dept], [T Master].Initiation, [T Master].Seniority,
[T Master].First, [T Master].Last, [T Master].Dues, [T Master].Title, [T
Master].Signedcard, [T Master].paying, [T Master].Steward, [T Master].[Shop
Contact], [T Master].Address, [T Master].City, [T Master].State, [T
Master].ZIP, [T Master].[Phone #], [T Master].[Alternate #], [T Master].[Cell
#], [T Master].[E-mail], [T Master].[Umass Form], [T Master].[Employee #], [T
Master].NOTES
FROM [T Master]
WHERE ((([T Master].Plant)=[?shop]) AND (([T Master].Termed)<"1/1/1800"));

 

[John Spencer]

First, if [T Master].Termed is a date field then you need to delimit the value
"1/1/1800" with # characters not " characters. If [T Master].Termed is a text
field containing a date string then you will get unexpected results with your
current search criteria.

SELECT [T Master].[Site/Dept]
, [T Master].Initiation
, [T Master].Seniority
, [T Master].First
, [T Master].Last
, [T Master].Dues, [T Master].Title
, [T Master].Signedcard, [T Master].paying
, [T Master].Steward, [T Master].[Shop Contact], [T Master].Address
, [T Master].City, [T Master].State
, [T Master].ZIP, [T Master].[Phone #], [T Master].[Alternate #]
, [T Master].[Cell #], [T Master].[E-mail], [T Master].[Umass Form]
, [T shop_list].[Shop Name]
, [T Master].[Employee #], [T Master].NOTES
FROM [T Master] LEFT JOIN [T shop_list]
ON [T Master].Plant = [T shop_list].Code
WHERE [T Master].Plant=[?shop] AND [T Master].Termed < #1/1/1800#

I'm not at all sure why you are trying to find records that have a termed
value before 1 January 1800.

If you are being asked for the parameter ?Shop multiple times, then the query
is probably executing multiple times. Try copying the SQL statement into a
new query and then naming the new query with the old query's name (replace the
old query).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

orignal:

SELECT [T Master].[Site/Dept], [T Master].Initiation, [T Master].Seniority,
[T Master].First, [T Master].Last, [T Master].Dues, [T Master].Title, [T
Master].Signedcard, [T Master].paying, [T Master].Steward, [T Master].[Shop
Contact], [T Master].Address, [T Master].City, [T Master].State, [T
Master].ZIP, [T Master].[Phone #], [T Master].[Alternate #], [T Master].[Cell
#], [T Master].[E-mail], [T Master].[Umass Form], [T shop_list].[Shop Name],
[T Master].[Employee #], [T Master].NOTES
FROM [T Master] LEFT JOIN [T shop_list] ON [T Master].Plant = [T
shop_list].Code
WHERE ((([T Master].Plant)=[?shop]) AND (([T Master].Termed)<"1/1/1800"));

changed to:

SELECT [T Master].[Site/Dept], [T Master].Initiation, [T Master].Seniority,
[T Master].First, [T Master].Last, [T Master].Dues, [T Master].Title, [T
Master].Signedcard, [T Master].paying, [T Master].Steward, [T Master].[Shop
Contact], [T Master].Address, [T Master].City, [T Master].State, [T
Master].ZIP, [T Master].[Phone #], [T Master].[Alternate #], [T Master].[Cell
#], [T Master].[E-mail], [T Master].[Umass Form], [T Master].[Employee #], [T
Master].NOTES
FROM [T Master]
WHERE ((([T Master].Plant)=[?shop]) AND (([T Master].Termed)<"1/1/1800"));

 

[Jerry Whittle]

Let’s start with the simple query first. Try the below and see what happens.

PARAMETERS [Enter Shop] Text;
SELECT [T Master].[Site/Dept],
[T Master].Initiation,
[T Master].Seniority,
[T Master].[First],
[T Master].[Last],
[T Master].Dues,
[T Master].Title,
[T Master].Signedcard,
[T Master].paying,
[T Master].Steward,
[T Master].[Shop Contact],
[T Master].Address,
[T Master].City,
[T Master].State,
[T Master].ZIP,
[T Master].[Phone #],
[T Master].[Alternate #],
[T Master].[Cell #],
[T Master].[E-mail],
[T Master].[Umass Form],
[T Master].[Employee #],
[T Master].[NOTES]
FROM [T Master]
WHERE [T Master].Plant)=[Enter Shop]
AND [T Master].Termed < #1/1/1800#;

At first I thought that the First, Last, and NOTES field names could be a
reserved word problem. Note is a reserved work, but not NOTES. First and Last
are aggregate functions, but not reserved words. Just in case, I put square
brackets around them []. For more about reserved words see:
http://support.microsoft.com/kb/286335/

I’m assuming that [?shop] is a parameter. If so, it’s not declared in the
SQL statement. Plus I’m worried about the question mark being a problem. See
what is at the front of the SQL statement. If the Plant field is a number
instead of text, change it.

Last is the "1/1/1800". If Termed is a proper Date/Time field, it should be
#1/1/1800#. If Termed is a text field, you won’t get the results that you
wanted as "2/1/1799" is greater than "1/1/1800" in a text field.

If the SQL gives you problems still, remove the entire Where clause and the
Parameter clause at the top. Next hard code in a Plant in a Where clause.
Last put back in the Termed in the Where clause. Start from simple and build
back up until you find the problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

orignal:

SELECT [T Master].[Site/Dept], [T Master].Initiation, [T Master].Seniority,
[T Master].First, [T Master].Last, [T Master].Dues, [T Master].Title, [T
Master].Signedcard, [T Master].paying, [T Master].Steward, [T Master].[Shop
Contact], [T Master].Address, [T Master].City, [T Master].State, [T
Master].ZIP, [T Master].[Phone #], [T Master].[Alternate #], [T Master].[Cell
#], [T Master].[E-mail], [T Master].[Umass Form], [T shop_list].[Shop Name],
[T Master].[Employee #], [T Master].NOTES
FROM [T Master] LEFT JOIN [T shop_list] ON [T Master].Plant = [T
shop_list].Code
WHERE ((([T Master].Plant)=[?shop]) AND (([T Master].Termed)<"1/1/1800"));

changed to:

SELECT [T Master].[Site/Dept], [T Master].Initiation, [T Master].Seniority,
[T Master].First, [T Master].Last, [T Master].Dues, [T Master].Title, [T
Master].Signedcard, [T Master].paying, [T Master].Steward, [T Master].[Shop
Contact], [T Master].Address, [T Master].City, [T Master].State, [T
Master].ZIP, [T Master].[Phone #], [T Master].[Alternate #], [T Master].[Cell
#], [T Master].[E-mail], [T Master].[Umass Form], [T Master].[Employee #], [T
Master].NOTES
FROM [T Master]
WHERE ((([T Master].Plant)=[?shop]) AND (([T Master].Termed)<"1/1/1800"));

 

[spark]

Well I am glad you both mentioned the termed field. I wasn't thinking the as
text it was looking at the date as anything but a date. That occured because
this database would no longer allow me to use "is null" which is how the old
database was set up to read that field. I need it to llok at that feild and
see that it is blank and therefore pull those records. Most of the time that
field is the date the worker left but it was left as a text field because
other info is sometimes added (termed, resigned, part-time, etc)

I wll relook at how that field SHOULD be used and adjust the query
appropriately.

I will also look at your suggestions and see what will occur.

Thanks for all your help. My training in Access was a three day event some
10+ years ago and I have only had to use it for very specific work so some of
what I know Access can do is not always as easy to make happen at this point
in time


Thanks,
-spark

 

[Jerry Whittle]

If you are stuck using the field as is, there are two functions that might
help. One is the IsDate. You use it to evaluate whether the text could be a
valid date. You could use it to eliminate those records that are not dates.
The next is CDate. It will convert a string into an actual date.

 

[spark]

Well I ended up creating a new termed field that was a checkbox only, then
converted the old term field to be note about the termination. All my queries
and reports now run based on the checkbox and seems to be working.

Thanks for all the input. Now I'll have to find is what's causing the duo
questions when I need to run a query and I 'll be sitting pretty