Report prob


Z

zufie

Here is my code. When I delete the HSFT data the AllPrograms Report
those not return any values. That is, this query for which I provide
the code below returns the values for this report. Thanks, John

SELECT qryTotalAge.*, qryTotalRace.*, qryTotalEmploy.*,
qryTotalEducation.*, qryTotalIncome.*, qryTotAgeOthNR.*,
qryTotEducOthNR.*, qryHSFTageEdu.[SumOfQ1 <=12 Years], qryHSFTageEdu.
[SumOfQ1 13 Years], qryHSFTageEdu.[SumOfQ1 14 Years] AS [SumOfQ1 14
Years HSFTageEdu], qryHSFTageEdu.[SumOfQ1 >=15 years], qryHSFTageEdu.
[SumOfQ1 Other], qryHSFTageEdu.[SumOfQ1 No Response], qryHSFTageEdu.
[SumOfQ2 <=12 Years], qryHSFTageEdu.[SumOfQ2 13 Years], qryHSFTageEdu.
[SumOfQ2 14 Years] AS [SumOfQ2 14 Years HSFTageEdu], qryHSFTageEdu.
[SumOfQ2 >=15 years], qryHSFTageEdu.[SumOfQ2 Other], qryHSFTageEdu.
[SumOfQ2 No Response], qryHSFTageEdu.[SumOfQ3 <=12 Years],
qryHSFTageEdu.[SumOfQ3 13 Years], qryHSFTageEdu.[SumOfQ3 14 Years] AS
[SumOfQ3 14 Years HSFTageEdu], qryHSFTageEdu.[SumOfQ3 >=15 years],
qryHSFTageEdu.[SumOfQ3 Other1], qryHSFTageEdu.[SumOfQ3 No Response],
qryHSFTageEdu.[SumOfQ4 <=12 Years], qryHSFTageEdu.[SumOfQ4 13 Years],
qryHSFTageEdu.[SumOfQ4 14 Years] AS [SumOfQ4 14 Years HSFTageEdu],
qryHSFTageEdu.[SumOfQ4 >=15 years], qryHSFTageEdu.[SumOfQ4 Other1],
qryHSFTageEdu.[SumOfQ4 No Response], qryHSFTageEdu.[SumOfQ1 <=8th
Grade], qryHeartSmartForTeens.[SumOfQ1 <=8th Grade] AS [Q1 <=8th
Grade1], qryHSFTageEdu.[SumOfQ1 9th-12th Grade], qryHeartSmartForTeens.
[SumOfQ1 9th-12th Grade] AS [Q1 9th-12th Grade1], qryHSFTageEdu.
[SumOfQ1 Other1], qryHSFTageEdu.[SumOfQ1 No Response1], qryHSFTageEdu.
[SumOfQ2 <=8th Grade], qryHeartSmartForTeens.[SumOfQ2 <=8th Grade] AS
[Q2 <=8th Grade1], qryHSFTageEdu.[SumOfQ2 9th-12th Grade],
qryHeartSmartForTeens.[SumOfQ2 9th-12th Grade] AS [Q2 9th-12th
Grade1], qryHSFTageEdu.[SumOfQ2 Other1], qryHSFTageEdu.[SumOfQ2 No
Response1], qryHSFTageEdu.[SumOfQ3 <=8th Grade], qryHeartSmartForTeens.
[SumOfQ3 <=8th Grade] AS [Q3 <=8th Grade1], qryHSFTageEdu.[SumOfQ3
9th-12th Grade], qryHeartSmartForTeens.[SumOfQ3 9th-12th Grade] AS [Q3
9th-12th Grade1], qryHSFTageEdu.[SumOfQ3 Other], qryHSFTageEdu.
[SumOfQ3 No Response1], qryHSFTageEdu.[SumOfQ4 <=8th Grade],
qryHeartSmartForTeens.[SumOfQ4 <=8th Grade] AS [Q4 <=8th Grade1],
qryHSFTageEdu.[SumOfQ4 9th-12th Grade], qryHeartSmartForTeens.[SumOfQ4
9th-12th Grade] AS [Q4 9th-12th Grade1], qryHSFTageEdu.[SumOfQ4 No
Response1], qryTotalEthnicity.*
FROM qryTotalAge, qryTotalEmploy, qryTotalIncome, qryTotalRace,
qryTotAgeOthNR, qryTotEducOthNR, qryHSFTageEdu, qryTotalEthnicity,
qryTotalEducation, qryHeartSmartForTeens;
 
Ad

Advertisements

J

John W. Vinson

Here is my code. When I delete the HSFT data the AllPrograms Report
those not return any values. That is, this query for which I provide
the code below returns the values for this report. Thanks, John

Do you have a question, John?

As written your query has *no joins at all*; the WHERE clause (defining the
source of the data) is

FROM qryTotalAge, qryTotalEmploy, qryTotalIncome, qryTotalRace,
qryTotAgeOthNR, qryTotEducOthNR, qryHSFTageEdu, qryTotalEthnicity,
qryTotalEducation, qryHeartSmartForTeens;

This is called a "Cartesian Join"; you have ten queries, each of which might
have multiple records (in principle, I don't know what these queries are).
Access will match *every single record* in each of these queries with *every
single record* in each of the other queries, in all possible combinations; if
you had ten records in each query, you would end up with 10,000,000,000 rows
in the result - certainly NOT what you want.

Do you mean that you're deleting *data* from the HSFT table? or deleting the
HSFT query from this query? or what?

Please don't make the assumption that any of the volunteers here can see your
table structures, know how the tables are related, know what's in qryTotalAge
or anything else: we can only see what you post.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Ad

Advertisements

J

John W. Vinson

Yes, I mean that I am deleting *data* from the HSFT table?

Could you please post an explanation of the problem you are encountering and
indicate what help you need?

So far you've said "Report prob" and listed a largish but very incomplete SQL
query. It calls multiple other queries (which you have not posted) referencing
one or more tables (which you have not described).

We're just people here, volunteers donating our time. Nobody here can see your
computer, nor do we know anything you don't post. I'd love to help, but given
what you've posted, there's nothing useful that I can contribute!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 

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

Top