Complex (to me) filter/criteria for a report

[vavroom]

Hello,

Hope you can help me figure this one out.

First, I've inherited a database, and I can't change the table/
structure. This I'm sure complicates the situation, as the tables are
not normalised.

I need to run a report, but limit the entries based on several
criteria, and I have no idea how to write that.

The report uses a field called [Assistant]. I have the following
filter onopen:
Assistant<>"None"

This limits the report to the query results where Assistant is
comprised of a first name and a last name.

Next, and this is where I have a problem, I need to check if the first
name and last name combination has no email associated to it. In the
table, the three relevant fields are [firstname], [lastname], and
. So what I want to do for the report is to retrieve the email
field where [firstname] & " " & [lastname] = [Assistant].

I can't, for the life of me, figure out how to write that, nor what
the best approach is.

Any assistance appreciated

 

[Tom Wickerath]

You can likely eliminate the filter, incorporating it into the SQL statement
directly. Something like this should do the trick (I think):

SELECT [firstname] & " " & [lastname] AS [Assistant], Email
FROM [Your Table Name]
WHERE Email Is Not Null AND Assistant <> "None";


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

PS. I'm a little confused about your Assistant field. Is this a field in
your table that is *storing* a combined name, or a calculated field in your
query (I showed it as a calculated field in the query). But then you have
some values that apparently equal "None". Hmmm.....Is the first name in this
case "No" and the last name "ne" or some similar combination of the four
letters N - o - n - e?

If you provide the name of your table, the names of the fields, and some
sample data with records that should be included and other records that
should not, then we can help you with a more accurate SQL statement.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html

 

[vavroom]

PS. I'm a little confused about your Assistant field. Is this a field in
your table that is *storing* a combined name, or a calculated field in your
query (I showed it as a calculated field in the query). But then you have
some values that apparently equal "None". Hmmm.....Is the first name in this
case "No" and the last name "ne" or some similar combination of the four
letters N - o - n - e?

If you provide the name of your table, the names of the fields, and some
sample data with records that should be included and other records that
should not, then we can help you with a more accurate SQL statement.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html

Thanks for the responses Tom. I'm unable to provide exact details, as
I'm not at the office (and won't be 'till Monday). The assistant
field is a field in one table.

Basically, I have a table with supervisors, with first names, last
names, emails, and other details. I have another table with session
details, including the main supervisors, and assistant supervisor.
The main supervisor is linked from the supervisor table, but the
assistant field is one that is held in the session table, and filled
manualy. The names of the assistants are the same as the main
supervisors.

It's a bit of a mess, really, and as I said, I inherited the thing
that grew over several years, built by a few people who were learning
as they went (aren't we all? ).

I was hoping to use a filter on the report, rather than add code to
the query, as the query is used for another two different reports.

Thanks again.

 

[Tom Wickerath]

It's a bit of a mess, really, and as I said, I inherited the thing

that grew over several years, built by a few people who were learning
as they went (aren't we all? ).

Oh yes, I can certainly understand what a mess it likely is in. I've seen
plenty of examples of this. Someone, just the other day, mentioned an Access
application with over 500 tables! Arrrggghhh.

I was hoping to use a filter on the report, rather than add code to
the query, as the query is used for another two different reports.

Add code to a query?
Why not just create another query, and base your report off of it? That way,
you don't interfere with any existing reports.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Pieter Wijnen]

Correction it was over *5000* Tables <g>
And it was a commercial financial db (Not Jet)

Pieter

 

[Tom Wickerath]

Hi Pieter,

We must be thinking of different messages. I didn't see one that indicated
5000+ tables! If you have a link, I'd love to see it. This is the message
that I had in mind:

http://www.microsoft.com/office/com...rted&mid=607dd759-22bf-4864-9dc4-89b6684dd111


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[vavroom]

Oh yes, I can certainly understand what a mess it likely is in. I've seen
plenty of examples of this. Someone, just the other day, mentioned an Access
application with over 500 tables! Arrrggghhh.

What, you mean you're not supposed to have that many tables in an

Why not just create another query, and base your report off of it? That way,
you don't interfere with any existing reports.

I was hoping to not have "yet-another-object-in-the-application", but
that might be in the end the easiest way to go.

Thanks

nic

 

[Tom Wickerath]

In that case, set the recordsource for your report to a valid SQL statement;
in other words, instead of setting the recordsource to a saved query, set it
to the equivalent SQL statement that you see if you were to click on View >
SQL View, in query design.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[vavroom]

In that case, set the recordsource for your report to a valid SQL statement;
in other words, instead of setting the recordsource to a saved query, set it
to the equivalent SQL statement that you see if you were to click on View >
SQL View, in query design.

Tom, thank you! Such a simple answer, I should have thought of it!

I much appreciate your time on this.

Nic