Help with report query

  • Thread starter Thread starter Aria
  • Start date Start date
A

Aria

Hello,
I was wondering if someone could help me with my query. I am creating a db
to track keys and employee info for our school. I am trying to create a
report for summer school. While I will still be working during the first
session of summer school, all regular year employees will be on break when
the second session begins and will not return until after that session ends.
Therefore, the administrative team will be responsible for ensuring that all
of the keys are returned by the summer school personnel. Since the keys
belong to our regular staff, the summer school staff will need to be
contacted if they fail to return our inventory.
Since it is way too early for summer school, I am trying to create my report
using test data. This is the query I have come up with but I receive the
following error message:
Data type mismatch in criteria expression

I looked in the Help section and for similar issues in the forum. DateIssued
and DateRtrnd are date/time fields; AllowedtoRetain is a yes/no field (tried
changing "False" to "No" but has the same error message).

The SQL is:

SELECT tblEmployees.EmpID, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.HomePhone, tblEmployees.CellPhone, tblKeyAssignments.KeyID,
tblKeys.KeyCode, tblKeyAssignments.DateIssued, tblKeyAssignments.DateRtrnd,
tblTitles.TitleID, tblTitles.TitleDescription,
tblKeyAssignments.AllowedToRetain
FROM tblTitles, tblKeys INNER JOIN (tblEmployees INNER JOIN
tblKeyAssignments ON tblEmployees.EmpID = tblKeyAssignments.EmpID) ON
tblKeys.KeyID = tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateIssued)>#1/15/2009#) AND
((tblKeyAssignments.DateRtrnd)="Is Null") AND
((tblKeyAssignments.AllowedToRetain)=False));

Can someone please tell me what I'm overlooking?
 
Hello,
I was wondering if someone could help me with my query. I am creating a db
to track keys and employee info for our school. I am trying to create a
report for summer school. While I will still be working during the first
session of summer school, all regular year employees will be on break when
the second session begins and will not return until after that session ends.
Therefore, the administrative team will be responsible for ensuring that all
of the keys are returned by the summer school personnel. Since the keys
belong to our regular staff, the summer school staff will need to be
contacted if they fail to return our inventory.
Since it is way too early for summer school, I am trying to create my report
using test data. This is the query I have come up with but I receive the
following error message:
Data type mismatch in criteria expression

I looked in the Help section and for similar issues in the forum. DateIssued
and DateRtrnd are date/time fields; AllowedtoRetain is a yes/no field (tried
changing "False" to "No" but has the same error message).

The SQL is:

SELECT tblEmployees.EmpID, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.HomePhone, tblEmployees.CellPhone, tblKeyAssignments.KeyID,
tblKeys.KeyCode, tblKeyAssignments.DateIssued, tblKeyAssignments.DateRtrnd,
tblTitles.TitleID, tblTitles.TitleDescription,
tblKeyAssignments.AllowedToRetain
FROM tblTitles, tblKeys INNER JOIN (tblEmployees INNER JOIN
tblKeyAssignments ON tblEmployees.EmpID = tblKeyAssignments.EmpID) ON
tblKeys.KeyID = tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateIssued)>#1/15/2009#) AND
((tblKeyAssignments.DateRtrnd)="Is Null") AND
((tblKeyAssignments.AllowedToRetain)=False));

Can someone please tell me what I'm overlooking?
Click to expand...

What is this ?
((tblKeyAssignments.DateRtrnd)="Is Null") AND

something is not = "Is Null"
Is Null is not a string constant... they're SQL keywords.

use
(tblKeyAssignments.DateRtrnd Is Null) AND
 
I don't quite understand your question. I created the query in design view. I
posted the SQL that was created from the query. I did not add the = sign,
Access did. If you are asking why I want to know if the date returned field
is null, doesn't that mean there isn't a return date?
 
The correct SQL statement would be this:

SELECT tblEmployees.EmpID, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.HomePhone, tblEmployees.CellPhone, tblKeyAssignments.KeyID,
tblKeys.KeyCode, tblKeyAssignments.DateIssued, tblKeyAssignments.DateRtrnd,
tblTitles.TitleID, tblTitles.TitleDescription,
tblKeyAssignments.AllowedToRetain
FROM tblTitles, tblKeys INNER JOIN (tblEmployees INNER JOIN
tblKeyAssignments ON tblEmployees.EmpID = tblKeyAssignments.EmpID) ON
tblKeys.KeyID = tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateIssued)>#1/15/2009#) AND
((tblKeyAssignments.DateRtrnd) Is Null) AND
((tblKeyAssignments.AllowedToRetain)=False));


Copy the above statement and paste it into the SQL View for your query.
 
I don't quite understand your question. I created the query in design view. I
posted the SQL that was created from the query. I did not add the = sign,
Access did. If you are asking why I want to know if the date returned field
is null, doesn't that mean there isn't a return date?
Click to expand...

Try editing out the = sign and the quotes. Copy and paste this SQL back into
the SQL window of the query:

SELECT tblEmployees.EmpID, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.HomePhone, tblEmployees.CellPhone, tblKeyAssignments.KeyID,
tblKeys.KeyCode, tblKeyAssignments.DateIssued, tblKeyAssignments.DateRtrnd,
tblTitles.TitleID, tblTitles.TitleDescription,
tblKeyAssignments.AllowedToRetain
FROM tblTitles, tblKeys INNER JOIN (tblEmployees INNER JOIN
tblKeyAssignments ON tblEmployees.EmpID = tblKeyAssignments.EmpID) ON
tblKeys.KeyID = tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateIssued)>#1/15/2009#) AND
((tblKeyAssignments.DateRtrnd) Is Null) AND
((tblKeyAssignments.AllowedToRetain)=False));

See if it works, and see what it looks like in the query grid.
 
John,
Thank you, thank you!!! I have looked at that until my eyes turned red but I
have a question. I did as you suggested and pasted the SQL statement and then
looked at it in the query grid. That is what I originally input, Is Null,
without quotes. When I moved on to my next field with criteria, Access had
added quotes. I pretty new at this so I figured the program knew what it was
doing so I kept it. Do you know why it does that?
 
John,
Thank you, thank you!!! I have looked at that until my eyes turned red but I
have a question. I did as you suggested and pasted the SQL statement and then
looked at it in the query grid. That is what I originally input, Is Null,
without quotes. When I moved on to my next field with criteria, Access had
added quotes. I pretty new at this so I figured the program knew what it was
doing so I kept it. Do you know why it does that?
Click to expand...

Not at all sure, and I've never had it do so! I wonder if you typed a space
before the IS, or did something else to make Access think you were typing a
text criterion?
 
Hmmm...there seems to be an additional problem with my query. It's giving me
repeating data. I'm not sure exactly what I have done wrong. I'll try to
explain so maybe you or someone else can point me in the right direction. I
don't know what information you need to help me so forgive me if I give you
information that is not at all helpful. Like I mentioned previously, this is
a db for my school.
Some of the tables I used to pull data for the query were:
tblEmployees
tblKeyAssignments
tblKeys
tblTitles

KeyAssignments is a junction table (don't know if this is pertinent).
tblTitles also has a juction table, TitlesEmps, which I did not use in the
query but maybe I should.

I didn't know how to account for the summer school data that I would need so
I added the title, Summer School Teacher, to tblTitles via my employee form.
I already had Teacher listed. An employee can have multiple titles such as
teacher, department chair, athletic director, etc. I created the title summer
school teacher because I thought it would be easier to query that info and
some of our regular staff will also teach summer school. The start and end
dates of summer school and our regular school year vary. I thought a
specific start date would be unstable.

The problem I'm seeing is that two teachers I have designated as summer
school teachers so that I can test data for my report, are showing twice with
the same KeyID, KeyCode, DateIssued but different title descriptions
(teacher, summer school teacher).

When I go to tblKeyAssignments, datasheet view, one teacher is listed once
because I only assigned one key. The other teacher is listed twice but has
been assigned different keys. Both situations are as they should be. Can
someone tell me what I have done wrong and how I would fix it?
 
You have a CROSS JOIN (a Cartesian join) in your query for the tblTitles
table. This causes your query to return a record for each combination of a
record from tblTitles and the other tables.

Try this SQL Statement, assuming that tblTitles should be joined to
tblEmployees via the EmpID field (you'll need to change the line that begins
with ON just before the WHERE line if you're using a different field for
joining these two tables):

SELECT tblEmployees.EmpID, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.HomePhone, tblEmployees.CellPhone, tblKeyAssignments.KeyID,
tblKeys.KeyCode, tblKeyAssignments.DateIssued, tblKeyAssignments.DateRtrnd,
tblTitles.TitleID, tblTitles.TitleDescription,
tblKeyAssignments.AllowedToRetain
FROM tblTitles INNER JOIN (tblKeys INNER JOIN (tblEmployees INNER JOIN
tblKeyAssignments ON tblEmployees.EmpID = tblKeyAssignments.EmpID) ON
tblKeys.KeyID = tblKeyAssignments.KeyID)
ON tblTitles.EmpID = tblEmployees.EmpID
WHERE (((tblKeyAssignments.DateIssued)>#1/15/2009#) AND
((tblKeyAssignments.DateRtrnd) Is Null) AND
((tblKeyAssignments.AllowedToRetain)=False));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
Ken,
Thank you for your patience and continued assistance. There were a number of
items that I needed to research and investigate after reading your post.
Being relatively new to Access, I have never heard of a Cross join. The book
I have did not mention that particular join. I needed to do my homework first.

For others who may stumble across the thread, the information I found
(http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx) states,
"Cross joins produce results that consist of every combination of rows from 2
or more tables. There is no relationship established between the two tables -
you literally just produce every possible combination."

So, if I have this correct, it appears to be:
Rows in table A x Rows in table B = Product AB

It was the statement "no relationship between the tables" that sent me to
investigate my query once again. As I understand it, there *should* be a
realtionship between the tables. If not directly, then through the junction
tables (tblKeyAssignments and tblTitleEmps). TitleID links to tblEmployees
via tblTitlesEmps. It's a subform on the employee form, which I think I
failed to mention. KeyID links to EmpID via tblKeyAssignments. I did not
include tblTitlesEmps in my previous attempt so I changed it to include that
table. I think this is what you have asked me to do.
Try this SQL Statement, assuming that tblTitles should be joined to
tblEmployees via the EmpID field (you'll need to change the line that begins
with ON just before the WHERE line if you're using a different field for
joining these two tables):
Click to expand...


New SQL
******
SELECT tblEmployees.EmpID, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.HomePhone, tblEmployees.CellPhone, tblKeyAssignments.KeyID,
tblKeys.KeyCode, tblKeyAssignments.DateIssued, tblKeyAssignments.DateRtrnd,
tblTitlesEmps.TitleID, tblTitles.TitleDescription,
tblKeyAssignments.AllowedToRetain
FROM tblTitles INNER JOIN (tblKeys INNER JOIN ((tblEmployees INNER JOIN
tblKeyAssignments ON tblEmployees.EmpID = tblKeyAssignments.EmpID) INNER JOIN
tblTitlesEmps ON tblEmployees.EmpID = tblTitlesEmps.EmpID) ON tblKeys.KeyID =
tblKeyAssignments.KeyID) ON tblTitles.TitleID = tblTitlesEmps.TitleID
WHERE (((tblKeyAssignments.DateIssued)>#1/15/2009#) AND
((tblKeyAssignments.DateRtrnd) Is Null) AND
((tblKeyAssignments.AllowedToRetain)=False));

There is no change between the results produced with this SQL and the first.
I tried your updated SQL but received an error message stating that it
couldn't represent the join line; completely my fault for not giving you the
correct information to begin with. I'm open to any advice or suggestions you
may have.
 
Aria said:
Ken,
Thank you for your patience and continued assistance. There were a number
of
items that I needed to research and investigate after reading your post.
Being relatively new to Access, I have never heard of a Cross join. The
book
I have did not mention that particular join. I needed to do my homework
first.

For others who may stumble across the thread, the information I found
(http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx) states,
"Cross joins produce results that consist of every combination of rows
from 2
or more tables. There is no relationship established between the two
tables -
you literally just produce every possible combination."

So, if I have this correct, it appears to be:
Rows in table A x Rows in table B = Product AB

It was the statement "no relationship between the tables" that sent me to
investigate my query once again. As I understand it, there *should* be a
realtionship between the tables. If not directly, then through the
junction
tables (tblKeyAssignments and tblTitleEmps). TitleID links to tblEmployees
via tblTitlesEmps. It's a subform on the employee form, which I think I
failed to mention. KeyID links to EmpID via tblKeyAssignments. I did not
include tblTitlesEmps in my previous attempt so I changed it to include
that
table. I think this is what you have asked me to do.



New SQL
******
SELECT tblEmployees.EmpID, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.HomePhone, tblEmployees.CellPhone, tblKeyAssignments.KeyID,
tblKeys.KeyCode, tblKeyAssignments.DateIssued,
tblKeyAssignments.DateRtrnd,
tblTitlesEmps.TitleID, tblTitles.TitleDescription,
tblKeyAssignments.AllowedToRetain
FROM tblTitles INNER JOIN (tblKeys INNER JOIN ((tblEmployees INNER JOIN
tblKeyAssignments ON tblEmployees.EmpID = tblKeyAssignments.EmpID) INNER
JOIN
tblTitlesEmps ON tblEmployees.EmpID = tblTitlesEmps.EmpID) ON
tblKeys.KeyID =
tblKeyAssignments.KeyID) ON tblTitles.TitleID = tblTitlesEmps.TitleID
WHERE (((tblKeyAssignments.DateIssued)>#1/15/2009#) AND
((tblKeyAssignments.DateRtrnd) Is Null) AND
((tblKeyAssignments.AllowedToRetain)=False));

There is no change between the results produced with this SQL and the
first.
I tried your updated SQL but received an error message stating that it
couldn't represent the join line; completely my fault for not giving you
the
correct information to begin with. I'm open to any advice or suggestions
you
may have.
Click to expand...

Ok, at least the cartesian join has been fixed; that is good.

Now, the reason that you're seeing duplicate records is because one of your
child tables has more than one record in it for a specific master record.
I'm guessing it's the tblTitles table, based on what you'd posted earlier.
If you have two titles for a single employee, that employee will be
displayed in two records in your query, one for each title. So, if you just
want one record for a specific title, you'll need to add another criterion
to the WHERE clause that limits the tblTitles records to a specific title
value. For example:

SELECT tblEmployees.EmpID, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.HomePhone, tblEmployees.CellPhone, tblKeyAssignments.KeyID,
tblKeys.KeyCode, tblKeyAssignments.DateIssued, tblKeyAssignments.DateRtrnd,
tblTitlesEmps.TitleID, tblTitles.TitleDescription,
tblKeyAssignments.AllowedToRetain
FROM tblTitles INNER JOIN (tblKeys INNER JOIN ((tblEmployees INNER JOIN
tblKeyAssignments ON tblEmployees.EmpID = tblKeyAssignments.EmpID) INNER
JOIN
tblTitlesEmps ON tblEmployees.EmpID = tblTitlesEmps.EmpID) ON tblKeys.KeyID
=
tblKeyAssignments.KeyID) ON tblTitles.TitleID = tblTitlesEmps.TitleID
WHERE (((tblKeyAssignments.DateIssued)>#1/15/2009#) AND
((tblKeyAssignments.DateRtrnd) Is Null) AND
((tblKeyAssignments.AllowedToRetain)=False)) AND
tblTitles.TitleNameField = "Summer School Teacher";


Or perhaps it's the tblKeyAssignments table that is causing the duplication.
You'd handle this in a similar way as I note above for the tblTitles
situation.

If neither of these solutions fits what you need, post back with specific
examples of the data in your tables (where an employee has one record, and
where an employee has multiple records, in the outputted query's results).
And also state what you want the query to provide as the result for both
situations. We then can help you with the SQL statement.
 
Ken,
Thank you so much for helping me with this! Good news, it now returns the
results that I expect to see! I use a test db to try out new additions and
features. I had to dump it because it was giving me rather squirrelly
results. I made a copy of my current db and moved it to my test folder, then
updated the query following your advice. The last piece of the puzzle was the
criteria. I had added it in the other query but since I was missing
tblTitlesEmps, it didn't change the results.

I was trying to test for a number of conditions to see what would happen. I
assigned a key to a staff member who will act as Principal. I marked Allowed
to Retain as "True", leaving the date returned, null. His name still appeared
in datasheet view although I thought it wouldn't because of the Allowed to
Retain condition. I guess I can live with it though. Thanks again.

Latest SQL:
SELECT tblEmployees.EmpID, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.HomePhone, tblEmployees.CellPhone, tblKeyAssignments.KeyID,
tblKeys.KeyCode, tblKeyAssignments.DateIssued, tblKeyAssignments.DateRtrnd,
tblKeyAssignments.AllowedToRetain, tblTitles.TitleID,
tblTitles.TitleDescription
FROM tblTitles INNER JOIN (tblKeys INNER JOIN ((tblEmployees INNER JOIN
tblKeyAssignments ON tblEmployees.EmpID = tblKeyAssignments.EmpID) INNER JOIN
tblTitlesEmps ON tblEmployees.EmpID = tblTitlesEmps.EmpID) ON tblKeys.KeyID =
tblKeyAssignments.KeyID) ON tblTitles.TitleID = tblTitlesEmps.TitleID
WHERE (((tblKeyAssignments.DateIssued)>#1/15/2009#) AND
((tblKeyAssignments.DateRtrnd) Is Null) AND
((tblKeyAssignments.AllowedToRetain)=False) AND
((tblTitles.TitleDescription)="Summer School Teacher")) OR
(((tblTitles.TitleDescription)="Summer School Principal")) OR
(((tblTitles.TitleDescription)="Summer School Vice Principal"));
 
Logic error for your various WHERE conditions; that's why you're seeing that
teacher name that you don't expect. Try this SQL statement (note that I've
added and deleted some parentheses so that the three OR conditions are
evaluated as a group, and then AND'ed with the other conditions):

SELECT tblEmployees.EmpID, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.HomePhone, tblEmployees.CellPhone, tblKeyAssignments.KeyID,
tblKeys.KeyCode, tblKeyAssignments.DateIssued, tblKeyAssignments.DateRtrnd,
tblKeyAssignments.AllowedToRetain, tblTitles.TitleID,
tblTitles.TitleDescription
FROM tblTitles INNER JOIN (tblKeys INNER JOIN ((tblEmployees INNER JOIN
tblKeyAssignments ON tblEmployees.EmpID = tblKeyAssignments.EmpID) INNER
JOIN
tblTitlesEmps ON tblEmployees.EmpID = tblTitlesEmps.EmpID) ON tblKeys.KeyID
=
tblKeyAssignments.KeyID) ON tblTitles.TitleID = tblTitlesEmps.TitleID
WHERE (((tblKeyAssignments.DateIssued)>#1/15/2009#) AND
((tblKeyAssignments.DateRtrnd) Is Null) AND
((tblKeyAssignments.AllowedToRetain)=False) AND
(((tblTitles.TitleDescription)="Summer School Teacher") OR
((tblTitles.TitleDescription)="Summer School Principal") OR
((tblTitles.TitleDescription)="Summer School Vice Principal"));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
Ken,
Thank you for your continued support. I received a syntax error so I am
going to explain exactly what I did, hoping that you see something I
overlooked. After reading your post I copied your SQL and my latest SQL into
a word doc and began comparing the two; taking note of the places where
parentheses were either added or dropped. The SQL is the same until I get to
tblTitles.TitleDescription.

These are the differences that I noted and the changes I made:
1. Added 1 parenthesis to the beginning of tblTitles.TitleDescription and
dropped the parenthesis after "Summer School Teacher".

2. Dropped the parenthesis at the beginning of tblTitles.TitleDescription
and at the end of "Summer School Principal".

3. Dropped the parenthesis at the beginning of tblTitles.TitleDescription
and left unchanged the statement = "Summer School Vice Principal"));

I received a syntax error which says:

Syntax error in query expression
'(((tblKeyAssignments.DateIssued)>#1/15/2009#) AND
((tblKeyAssignments.DateRtrnd)Is Null) AND
((tblKeyAssignments.AllowedToRetain)= False) AND
(((tblTitles.TitleDescription)= "Summer School Teacher")
OR ((tblTitles.TitleDescription)= "Summer School Principal'.

I notice that in the error message Summer School Principal does not end in
the double quotes or parentheses as in the SQL statement. I don't know why
that would be or if it makes any difference. I still have your SQL in the
window and was comparing to see if I have forgotten something.
 
Ooops, looks like I overlooked an unneeded parenthesis. In fact, let's get
rid of all the unneded parentheses that ACCESS added.

Try this:

SELECT tblEmployees.EmpID, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.HomePhone, tblEmployees.CellPhone, tblKeyAssignments.KeyID,
tblKeys.KeyCode, tblKeyAssignments.DateIssued, tblKeyAssignments.DateRtrnd,
tblKeyAssignments.AllowedToRetain, tblTitles.TitleID,
tblTitles.TitleDescription
FROM tblTitles INNER JOIN (tblKeys INNER JOIN ((tblEmployees INNER JOIN
tblKeyAssignments ON tblEmployees.EmpID = tblKeyAssignments.EmpID) INNER
JOIN
tblTitlesEmps ON tblEmployees.EmpID = tblTitlesEmps.EmpID) ON tblKeys.KeyID
=
tblKeyAssignments.KeyID) ON tblTitles.TitleID = tblTitlesEmps.TitleID
WHERE tblKeyAssignments.DateIssued>#1/15/2009# AND
tblKeyAssignments.DateRtrnd Is Null AND
tblKeyAssignments.AllowedToRetain=False AND
(tblTitles.TitleDescription="Summer School Teacher" OR
tblTitles.TitleDescription="Summer School Principal" OR
tblTitles.TitleDescription="Summer School Vice Principal");

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
Ken,
Oh my gosh that was *rough*!!! I was ready to pull my hair out! I kept
getting an "extra ) in query expression" error message but I could not see
it. I checked it over and over and could not see anything. I don't know if
the problem was that I was trying to go from the SQL directly to datasheet
view or if that makes any difference. When I went to the grid then datasheet
view it *finally* gave the results I was looking for! Thank you *so* much for
your patience in teaching me. I can finally move forward. Thank you again!
 
Aria said:
Ken,
Oh my gosh that was *rough*!!! I was ready to pull my hair out! I kept
getting an "extra ) in query expression" error message but I could not see
it. I checked it over and over and could not see anything. I don't know if
the problem was that I was trying to go from the SQL directly to datasheet
view or if that makes any difference. When I went to the grid then
datasheet
view it *finally* gave the results I was looking for! Thank you *so* much
for
your patience in teaching me. I can finally move forward. Thank you again!
Click to expand...

You're welcome. Good luck with your database.
 
Back
Top