Nulls in Access 2007 Fields

G

Guest

Here is my issue:

I have to generate a report which breaks down specific information,
specifically a count derived from when the information was created. I have
accomplished this. My problem is when I run a specific query for one piece
of information it returns what I am looking for. When I run a different
query with the same information looking for the other data I need - it does
not give me anything. Now the problem is there is nothing for the database
to pull up because the answer is zero, but I can not get Access to give me a
zero in the field to show there were no new enrollies for this specific month.

I used this statement:

Source: IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S")

to get my first bit of information and it worked because I had 2 new Adult
enrollies. It gives me nothing in the other query when I run for the kids,
because like I said there were no new kids enrolled. Now I fear that the
adult query may not work down the road if there were no new adult enrollies.

Please help, I have been on this specific issue for a day and a half now and
I am no closer to the answer. Everything I have tried is not working. Or
maybe I am putting it in the wrong query, not sure. Help! Thanks.
 
G

Guest

If you want to return a row, or rows, for a month which is not included in
the data then you'll need to introduce it by creating another 'calendar'
table which covers at least the whole of the time range you are interested
in. This table can then be LEFT OUTER JOINed to your existing table so that
rows are returned for all months regardless.

I can't really be more specific than that on the basis of the information
you've given, but if you post the SQL here I can probably say more.

Ken Sheridan
Stafford, England
 
G

Guest

Here is a copy of the SQL. When I pull data from this query into another
query is when I am getting nothing.

SELECT ClientsByInsuranceAndServiceDate2.Name, Person.BirthDateTime,
Person.CreateDateTime, ClntsInsSvcDatesByAgegroup.Expr1, Person.LastName,
Person.FirstName, Person.MiddleInitial, Person.PersonID,
IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S") AS Source
FROM (Person INNER JOIN ClientsByInsuranceAndServiceDate2 ON Person.PersonID
= ClientsByInsuranceAndServiceDate2.PersonID) INNER JOIN
ClntsInsSvcDatesByAgegroup ON Person.PersonID =
ClntsInsSvcDatesByAgegroup.PersonID
GROUP BY ClientsByInsuranceAndServiceDate2.Name, Person.BirthDateTime,
Person.CreateDateTime, ClntsInsSvcDatesByAgegroup.Expr1, Person.LastName,
Person.FirstName, Person.MiddleInitial, Person.PersonID,
IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S")
HAVING (((Person.CreateDateTime) Between #2/1/2007# And #2/28/2007#))
ORDER BY Person.BirthDateTime, Person.CreateDateTime, Person.LastName,
Person.FirstName, Person.MiddleInitial;

Here is the SQL from the table that I am getting nothing.

SELECT Count(*) AS Kids, EnrolliesQuery.Source, [Source]=IsNull("") AS IFF
FROM EnrolliesQuery
WHERE (((EnrolliesQuery.Expr1)="K"))
GROUP BY EnrolliesQuery.Source, [Source]=IsNull("");

I need there to be a zero under the Kids heading, and I just get nothing. I
hope this helps, and thank you very much for your assistance.



Ken Sheridan said:
If you want to return a row, or rows, for a month which is not included in
the data then you'll need to introduce it by creating another 'calendar'
table which covers at least the whole of the time range you are interested
in. This table can then be LEFT OUTER JOINed to your existing table so that
rows are returned for all months regardless.

I can't really be more specific than that on the basis of the information
you've given, but if you post the SQL here I can probably say more.

Ken Sheridan
Stafford, England

memcmorris said:
Here is my issue:

I have to generate a report which breaks down specific information,
specifically a count derived from when the information was created. I have
accomplished this. My problem is when I run a specific query for one piece
of information it returns what I am looking for. When I run a different
query with the same information looking for the other data I need - it does
not give me anything. Now the problem is there is nothing for the database
to pull up because the answer is zero, but I can not get Access to give me a
zero in the field to show there were no new enrollies for this specific month.

I used this statement:

Source: IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S")

to get my first bit of information and it worked because I had 2 new Adult
enrollies. It gives me nothing in the other query when I run for the kids,
because like I said there were no new kids enrolled. Now I fear that the
adult query may not work down the road if there were no new adult enrollies.

Please help, I have been on this specific issue for a day and a half now and
I am no closer to the answer. Everything I have tried is not working. Or
maybe I am putting it in the wrong query, not sure. Help! Thanks.
Click to expand...
Click to expand...
 
G

Guest

Firstly, the first query does not include any aggregation so doesn't need a
GROUP BY clause:

SELECT
ClientsByInsuranceAndServiceDate2.Name,
Person.BirthDateTime,
Person.CreateDateTime,
ClntsInsSvcDatesByAgegroup.Expr1,
Person.LastName,
Person.FirstName,
Person.MiddleInitial,
Person.PersonID,
IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S") AS Source
FROM (Person INNER JOIN ClientsByInsuranceAndServiceDate2
ON Person.PersonID = ClientsByInsuranceAndServiceDate2.PersonID)
INNER JOIN ClntsInsSvcDatesByAgegroup
ON Person.PersonID = ClntsInsSvcDatesByAgegroup.PersonID
WHERE Person.CreateDateTime BETWEEN #2/1/2007# And #2/28/2007#
ORDER BY Person.BirthDateTime, Person.CreateDateTime, Person.LastName,
Person.FirstName, Person.MiddleInitial;

You seem to be mixing parameters (the references to the controls on the form
in the IIf function call) and date literals (in the WHERE clause) here. Is
this the same date range in each case? If so then the IIf function call will
always return "N". If they both represent the same range then use the same
parameters in the WHERE clause too.

As regards the second query:

SELECT
COUNT(*) AS Kids,
EnrolliesQuery.Source,
[Source]=IsNull("") AS IFF
FROM EnrolliesQuery
WHERE Expr1 ="K"
GROUP BY Source, [Source]=IsNull("");

I don't see the purpose of the expression [Source]=IsNull("") AS IFF.
IsNull("") will always return FALSE, so Source = FALSE is also going to
evaluate to FALSE as Source is a string expression. If the date literals and
parameters in the first query do represent the same range and I'm
consequently right about the expression in the first query always returning
Source as "N" you can simply include it as a constant in the second query's
SELECT clause if you really need it retuned as a column:

SELECT "N" AS Source,
COUNT(*) AS Kids
FROM EnrolliesQuery
WHERE Expr1 ="K";

Ken Sheridan
Stafford, England

memcmorris said:
Here is a copy of the SQL. When I pull data from this query into another
query is when I am getting nothing.

SELECT ClientsByInsuranceAndServiceDate2.Name, Person.BirthDateTime,
Person.CreateDateTime, ClntsInsSvcDatesByAgegroup.Expr1, Person.LastName,
Person.FirstName, Person.MiddleInitial, Person.PersonID,
IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S") AS Source
FROM (Person INNER JOIN ClientsByInsuranceAndServiceDate2 ON Person.PersonID
= ClientsByInsuranceAndServiceDate2.PersonID) INNER JOIN
ClntsInsSvcDatesByAgegroup ON Person.PersonID =
ClntsInsSvcDatesByAgegroup.PersonID
GROUP BY ClientsByInsuranceAndServiceDate2.Name, Person.BirthDateTime,
Person.CreateDateTime, ClntsInsSvcDatesByAgegroup.Expr1, Person.LastName,
Person.FirstName, Person.MiddleInitial, Person.PersonID,
IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S")
HAVING (((Person.CreateDateTime) Between #2/1/2007# And #2/28/2007#))
ORDER BY Person.BirthDateTime, Person.CreateDateTime, Person.LastName,
Person.FirstName, Person.MiddleInitial;

Here is the SQL from the table that I am getting nothing.

SELECT Count(*) AS Kids, EnrolliesQuery.Source, [Source]=IsNull("") AS IFF
FROM EnrolliesQuery
WHERE (((EnrolliesQuery.Expr1)="K"))
GROUP BY EnrolliesQuery.Source, [Source]=IsNull("");

I need there to be a zero under the Kids heading, and I just get nothing. I
hope this helps, and thank you very much for your assistance.



Ken Sheridan said:
If you want to return a row, or rows, for a month which is not included in
the data then you'll need to introduce it by creating another 'calendar'
table which covers at least the whole of the time range you are interested
in. This table can then be LEFT OUTER JOINed to your existing table so that
rows are returned for all months regardless.

I can't really be more specific than that on the basis of the information
you've given, but if you post the SQL here I can probably say more.

Ken Sheridan
Stafford, England

memcmorris said:
Here is my issue:

I have to generate a report which breaks down specific information,
specifically a count derived from when the information was created. I have
accomplished this. My problem is when I run a specific query for one piece
of information it returns what I am looking for. When I run a different
query with the same information looking for the other data I need - it does
not give me anything. Now the problem is there is nothing for the database
to pull up because the answer is zero, but I can not get Access to give me a
zero in the field to show there were no new enrollies for this specific month.

I used this statement:

Source: IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S")

to get my first bit of information and it worked because I had 2 new Adult
enrollies. It gives me nothing in the other query when I run for the kids,
because like I said there were no new kids enrolled. Now I fear that the
adult query may not work down the road if there were no new adult enrollies.

Please help, I have been on this specific issue for a day and a half now and
I am no closer to the answer. Everything I have tried is not working. Or
maybe I am putting it in the wrong query, not sure. Help! Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Oh my gosh, thank you soooo much. It worked perfectly! You are truly a god
send! I have been working on this for a day and a half. Thank you! Thank
you!

Ken Sheridan said:
Firstly, the first query does not include any aggregation so doesn't need a
GROUP BY clause:

SELECT
ClientsByInsuranceAndServiceDate2.Name,
Person.BirthDateTime,
Person.CreateDateTime,
ClntsInsSvcDatesByAgegroup.Expr1,
Person.LastName,
Person.FirstName,
Person.MiddleInitial,
Person.PersonID,
IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S") AS Source
FROM (Person INNER JOIN ClientsByInsuranceAndServiceDate2
ON Person.PersonID = ClientsByInsuranceAndServiceDate2.PersonID)
INNER JOIN ClntsInsSvcDatesByAgegroup
ON Person.PersonID = ClntsInsSvcDatesByAgegroup.PersonID
WHERE Person.CreateDateTime BETWEEN #2/1/2007# And #2/28/2007#
ORDER BY Person.BirthDateTime, Person.CreateDateTime, Person.LastName,
Person.FirstName, Person.MiddleInitial;

You seem to be mixing parameters (the references to the controls on the form
in the IIf function call) and date literals (in the WHERE clause) here. Is
this the same date range in each case? If so then the IIf function call will
always return "N". If they both represent the same range then use the same
parameters in the WHERE clause too.

As regards the second query:

SELECT
COUNT(*) AS Kids,
EnrolliesQuery.Source,
[Source]=IsNull("") AS IFF
FROM EnrolliesQuery
WHERE Expr1 ="K"
GROUP BY Source, [Source]=IsNull("");

I don't see the purpose of the expression [Source]=IsNull("") AS IFF.
IsNull("") will always return FALSE, so Source = FALSE is also going to
evaluate to FALSE as Source is a string expression. If the date literals and
parameters in the first query do represent the same range and I'm
consequently right about the expression in the first query always returning
Source as "N" you can simply include it as a constant in the second query's
SELECT clause if you really need it retuned as a column:

SELECT "N" AS Source,
COUNT(*) AS Kids
FROM EnrolliesQuery
WHERE Expr1 ="K";

Ken Sheridan
Stafford, England

memcmorris said:
Here is a copy of the SQL. When I pull data from this query into another
query is when I am getting nothing.

SELECT ClientsByInsuranceAndServiceDate2.Name, Person.BirthDateTime,
Person.CreateDateTime, ClntsInsSvcDatesByAgegroup.Expr1, Person.LastName,
Person.FirstName, Person.MiddleInitial, Person.PersonID,
IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S") AS Source
FROM (Person INNER JOIN ClientsByInsuranceAndServiceDate2 ON Person.PersonID
= ClientsByInsuranceAndServiceDate2.PersonID) INNER JOIN
ClntsInsSvcDatesByAgegroup ON Person.PersonID =
ClntsInsSvcDatesByAgegroup.PersonID
GROUP BY ClientsByInsuranceAndServiceDate2.Name, Person.BirthDateTime,
Person.CreateDateTime, ClntsInsSvcDatesByAgegroup.Expr1, Person.LastName,
Person.FirstName, Person.MiddleInitial, Person.PersonID,
IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S")
HAVING (((Person.CreateDateTime) Between #2/1/2007# And #2/28/2007#))
ORDER BY Person.BirthDateTime, Person.CreateDateTime, Person.LastName,
Person.FirstName, Person.MiddleInitial;

Here is the SQL from the table that I am getting nothing.

SELECT Count(*) AS Kids, EnrolliesQuery.Source, [Source]=IsNull("") AS IFF
FROM EnrolliesQuery
WHERE (((EnrolliesQuery.Expr1)="K"))
GROUP BY EnrolliesQuery.Source, [Source]=IsNull("");

I need there to be a zero under the Kids heading, and I just get nothing. I
hope this helps, and thank you very much for your assistance.



Ken Sheridan said:
If you want to return a row, or rows, for a month which is not included in
the data then you'll need to introduce it by creating another 'calendar'
table which covers at least the whole of the time range you are interested
in. This table can then be LEFT OUTER JOINed to your existing table so that
rows are returned for all months regardless.

I can't really be more specific than that on the basis of the information
you've given, but if you post the SQL here I can probably say more.

Ken Sheridan
Stafford, England

:

Here is my issue:

I have to generate a report which breaks down specific information,
specifically a count derived from when the information was created. I have
accomplished this. My problem is when I run a specific query for one piece
of information it returns what I am looking for. When I run a different
query with the same information looking for the other data I need - it does
not give me anything. Now the problem is there is nothing for the database
to pull up because the answer is zero, but I can not get Access to give me a
zero in the field to show there were no new enrollies for this specific month.

I used this statement:

Source: IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S")

to get my first bit of information and it worked because I had 2 new Adult
enrollies. It gives me nothing in the other query when I run for the kids,
because like I said there were no new kids enrolled. Now I fear that the
adult query may not work down the road if there were no new adult enrollies.

Please help, I have been on this specific issue for a day and a half now and
I am no closer to the answer. Everything I have tried is not working. Or
maybe I am putting it in the wrong query, not sure. Help! Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ok, now both queries are functioning perfectly, here is my issue. I need to
combine them in a Union Query and then combine the Union Query with another
table in a separate Query to utilitize one specific field from the table and
my Union Query is not working. I need to pull the information from both
queries listed below to get the right information on a report, I don't
understand why it is not working, any additional assistance?

Union Query:

SELECT TotalsQuery1.Adults, TotalsQuery1.Kids, TotalsQuery1.Source
FROM TotalsQuery1
UNION ALL
SELECT TotalNewEnrolliesQuery.Adults, TotalNewEnrolliesQuery.Kids,
TotalNewEnrolliesQuery.Source
FROM TotalNewEnrolliesQuery;

Report Query:

SELECT ClientsByInsuranceAndServiceDate.Name, UnionQuery.Adults,
UnionQuery.Kids, UnionQuery.Source
FROM UnionQuery, ClientsByInsuranceAndServiceDate
GROUP BY ClientsByInsuranceAndServiceDate.Name, UnionQuery.Adults,
UnionQuery.Kids, UnionQuery.Source
HAVING (((ClientsByInsuranceAndServiceDate.Name)="Access Alliance Of
Michigan"));

For some reason I cannot get my Union to work properly and I am hoping that
the SQL above for the Report Query will function properly.

Anything else you can do to help would be greatly appreciated. Thank you!



Ken Sheridan said:
Firstly, the first query does not include any aggregation so doesn't need a
GROUP BY clause:

SELECT
ClientsByInsuranceAndServiceDate2.Name,
Person.BirthDateTime,
Person.CreateDateTime,
ClntsInsSvcDatesByAgegroup.Expr1,
Person.LastName,
Person.FirstName,
Person.MiddleInitial,
Person.PersonID,
IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S") AS Source
FROM (Person INNER JOIN ClientsByInsuranceAndServiceDate2
ON Person.PersonID = ClientsByInsuranceAndServiceDate2.PersonID)
INNER JOIN ClntsInsSvcDatesByAgegroup
ON Person.PersonID = ClntsInsSvcDatesByAgegroup.PersonID
WHERE Person.CreateDateTime BETWEEN #2/1/2007# And #2/28/2007#
ORDER BY Person.BirthDateTime, Person.CreateDateTime, Person.LastName,
Person.FirstName, Person.MiddleInitial;

You seem to be mixing parameters (the references to the controls on the form
in the IIf function call) and date literals (in the WHERE clause) here. Is
this the same date range in each case? If so then the IIf function call will
always return "N". If they both represent the same range then use the same
parameters in the WHERE clause too.

As regards the second query:

SELECT
COUNT(*) AS Kids,
EnrolliesQuery.Source,
[Source]=IsNull("") AS IFF
FROM EnrolliesQuery
WHERE Expr1 ="K"
GROUP BY Source, [Source]=IsNull("");

I don't see the purpose of the expression [Source]=IsNull("") AS IFF.
IsNull("") will always return FALSE, so Source = FALSE is also going to
evaluate to FALSE as Source is a string expression. If the date literals and
parameters in the first query do represent the same range and I'm
consequently right about the expression in the first query always returning
Source as "N" you can simply include it as a constant in the second query's
SELECT clause if you really need it retuned as a column:

SELECT "N" AS Source,
COUNT(*) AS Kids
FROM EnrolliesQuery
WHERE Expr1 ="K";

Ken Sheridan
Stafford, England

memcmorris said:
Here is a copy of the SQL. When I pull data from this query into another
query is when I am getting nothing.

SELECT ClientsByInsuranceAndServiceDate2.Name, Person.BirthDateTime,
Person.CreateDateTime, ClntsInsSvcDatesByAgegroup.Expr1, Person.LastName,
Person.FirstName, Person.MiddleInitial, Person.PersonID,
IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S") AS Source
FROM (Person INNER JOIN ClientsByInsuranceAndServiceDate2 ON Person.PersonID
= ClientsByInsuranceAndServiceDate2.PersonID) INNER JOIN
ClntsInsSvcDatesByAgegroup ON Person.PersonID =
ClntsInsSvcDatesByAgegroup.PersonID
GROUP BY ClientsByInsuranceAndServiceDate2.Name, Person.BirthDateTime,
Person.CreateDateTime, ClntsInsSvcDatesByAgegroup.Expr1, Person.LastName,
Person.FirstName, Person.MiddleInitial, Person.PersonID,
IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S")
HAVING (((Person.CreateDateTime) Between #2/1/2007# And #2/28/2007#))
ORDER BY Person.BirthDateTime, Person.CreateDateTime, Person.LastName,
Person.FirstName, Person.MiddleInitial;

Here is the SQL from the table that I am getting nothing.

SELECT Count(*) AS Kids, EnrolliesQuery.Source, [Source]=IsNull("") AS IFF
FROM EnrolliesQuery
WHERE (((EnrolliesQuery.Expr1)="K"))
GROUP BY EnrolliesQuery.Source, [Source]=IsNull("");

I need there to be a zero under the Kids heading, and I just get nothing. I
hope this helps, and thank you very much for your assistance.



Ken Sheridan said:
If you want to return a row, or rows, for a month which is not included in
the data then you'll need to introduce it by creating another 'calendar'
table which covers at least the whole of the time range you are interested
in. This table can then be LEFT OUTER JOINed to your existing table so that
rows are returned for all months regardless.

I can't really be more specific than that on the basis of the information
you've given, but if you post the SQL here I can probably say more.

Ken Sheridan
Stafford, England

:

Here is my issue:

I have to generate a report which breaks down specific information,
specifically a count derived from when the information was created. I have
accomplished this. My problem is when I run a specific query for one piece
of information it returns what I am looking for. When I run a different
query with the same information looking for the other data I need - it does
not give me anything. Now the problem is there is nothing for the database
to pull up because the answer is zero, but I can not get Access to give me a
zero in the field to show there were no new enrollies for this specific month.

I used this statement:

Source: IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S")

to get my first bit of information and it worked because I had 2 new Adult
enrollies. It gives me nothing in the other query when I run for the kids,
because like I said there were no new kids enrolled. Now I fear that the
adult query may not work down the road if there were no new adult enrollies.

Please help, I have been on this specific issue for a day and a half now and
I am no closer to the answer. Everything I have tried is not working. Or
maybe I am putting it in the wrong query, not sure. Help! Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

This is the error message I keep receiving when I try to run my Union Query:

"The specific field 'Source' could refer to more than one table listed in
the FROM clause of my SQL statement."

Now this does not make much sense to me since I know you have to have the
same number of fields in a Union Query, and I have the same names in both
tables so I don't understand why it is giving me a hard time. It didn't say
anything with the Kids or Adults fields.....perplexing!

Here is the SQL from both tables that I am trying to use in the Union Query:

EnrolliesCountQuery:

SELECT Count(*) AS Adults, EnrolliesQuery.Source
FROM EnrolliesQuery
WHERE (((EnrolliesQuery.Expr1)="A"))
GROUP BY EnrolliesQuery.Source;

EnrolliesCountQuery2:

SELECT Count(*) AS Kids, "N" AS Source
FROM EnrolliesQuery
WHERE Expr1 = "K";

Again, any help is greatly appreciated! Thanks!
 
G

Guest

Try cutting out the middleman and use the SQL statements rather than the
names of the queries. You don't need to put any column headings for the
second part as the column headings from the first part are used for the
result table's column headings. Arising from that I think you are going to
need another column to distinguish the kids form the adults:

SELECT "Adults" AS AgeGroup,
COUNT(*) AS TotalNumber, Source
FROM EnrolliesQuery
WHERE Expr1 = "A"
GROUP BY EnrolliesQuery.Source
UNION ALL
SELECT "Kids",
COUNT(*), "N"
FROM EnrolliesQuery
WHERE Expr1 = "K";

This means that when you join this to the ClientsByInsuranceAndServiceDate
query you'll have one column with the counts in rather than separate ones,
with the value of the AgeGroup column distinguishing them in each row
returned.

I do wonder, however, whether you need the union query at all and whether
you need to join it to the ClientsByInsuranceAndServiceDate query for the
report. Could you not use unbound controls in the report with ControlSource
properties which look up the values form the two queries with DLookup
function calls?

Ken Sheridan
Stafford, England

memcmorris said:
This is the error message I keep receiving when I try to run my Union Query:

"The specific field 'Source' could refer to more than one table listed in
the FROM clause of my SQL statement."

Now this does not make much sense to me since I know you have to have the
same number of fields in a Union Query, and I have the same names in both
tables so I don't understand why it is giving me a hard time. It didn't say
anything with the Kids or Adults fields.....perplexing!

Here is the SQL from both tables that I am trying to use in the Union Query:

EnrolliesCountQuery:

SELECT Count(*) AS Adults, EnrolliesQuery.Source
FROM EnrolliesQuery
WHERE (((EnrolliesQuery.Expr1)="A"))
GROUP BY EnrolliesQuery.Source;

EnrolliesCountQuery2:

SELECT Count(*) AS Kids, "N" AS Source
FROM EnrolliesQuery
WHERE Expr1 = "K";

Again, any help is greatly appreciated! Thanks!


memcmorris said:
Here is my issue:

I have to generate a report which breaks down specific information,
specifically a count derived from when the information was created. I have
accomplished this. My problem is when I run a specific query for one piece
of information it returns what I am looking for. When I run a different
query with the same information looking for the other data I need - it does
not give me anything. Now the problem is there is nothing for the database
to pull up because the answer is zero, but I can not get Access to give me a
zero in the field to show there were no new enrollies for this specific month.

I used this statement:

Source: IIf([CreateDateTime]>=[Forms]![FromToDates]![Text0] And
[CreateDateTime]<=[Forms]![FromToDates]![Text2],"N","S")

to get my first bit of information and it worked because I had 2 new Adult
enrollies. It gives me nothing in the other query when I run for the kids,
because like I said there were no new kids enrolled. Now I fear that the
adult query may not work down the road if there were no new adult enrollies.

Please help, I have been on this specific issue for a day and a half now and
I am no closer to the answer. Everything I have tried is not working. Or
maybe I am putting it in the wrong query, not sure. Help! Thanks.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I concatenates two fields into one field in the same table? 3
field Null but not Null 9
Need help with a query 1
Crosstab Null value display as a "0" 2
Access Queiries 2
update tables 6
Convert Access 2007 to 2003 errors 1
Keeping the same record selected. 1

Top