Query returns column headings only

[Beyuduzz]

How do I keep a query from just returning the column headings. I have a
query that is returning just the column headings if one of the fields has a
null value.

 

[Jeff Boyce]

You haven't described wether the underlying data comes from a single table
or the joining of more than one table.

Can you post the SQL statement of your query, that the newsgroup readers
might be able to get a better understanding of what (and how) you are trying
to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Beyuduzz]

Here is my SQL from the query
--------------------------------------------SQL-----------------------------------------------
SELECT Null AS [Date], DateRange_AllHours.SumOfDuration AS Total,
DateRange_Hosthours.[Host hours] AS Host, DateRange_MCUhours.[MCU hours],
DateRange_Attendeehours.TotalAttendee AS Attendee,
DateRange_CnxHours.[Cancelled hours] AS Cancelled
FROM DateRange_Hosthours, DateRange_Attendeehours, DateRange_AllHours,
DateRange_CnxHours, DateRange_MCUhours
GROUP BY Null, DateRange_AllHours.SumOfDuration, DateRange_Hosthours.[Host
hours], DateRange_MCUhours.[MCU hours],
DateRange_Attendeehours.TotalAttendee, DateRange_CnxHours.[Cancelled hours]
----------------------------------SQL--------------------------------------------------------
If the "DateRange_CnxHours"field has no data the query returns only the
column headings. I don't even get blank cells back.

 

[Jeff Boyce]

From your SQL statement, based on the names of the tables, it appears you
have multiple tables, each containing a different "type" of "Hours". If so,
this is exactly what you'd need to do, ... if you were working with a
spreadsheet!

In a relational database (e.g., MS Access), you'd use a single table, with a
field for your time component, and a field to describe "type". All the
"hours" would be in one table.

Your SQL statement appears to have a number of tables (all the names
following "FROM"), but no connection between the tables. If you don't
"join" the tables on common fields, Access will "multiply" the tables'
fields and generate what is called a "cartesian product".

You might want to consider getting the data structure better normalized
before trying to create the queries...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is my SQL from the query.
--------------------------------------------SQL-----------------------------------------------
SELECT Null AS [Date], DateRange_AllHours.SumOfDuration AS Total,
DateRange_Hosthours.[Host hours] AS Host, DateRange_MCUhours.[MCU hours],
DateRange_Attendeehours.TotalAttendee AS Attendee,
DateRange_CnxHours.[Cancelled hours] AS Cancelled
FROM DateRange_Hosthours, DateRange_Attendeehours, DateRange_AllHours,
DateRange_CnxHours, DateRange_MCUhours
GROUP BY Null, DateRange_AllHours.SumOfDuration, DateRange_Hosthours.[Host
hours], DateRange_MCUhours.[MCU hours],
DateRange_Attendeehours.TotalAttendee, DateRange_CnxHours.[Cancelled
hours];
----------------------------------SQL--------------------------------------------------------
If the "DateRange_CnxHours"field has no data the query returns only the
column headings. I don't even get blank cells back.


--
I''m a novice with an advance way of thinking.

You haven't described wether the underlying data comes from a single
table
or the joining of more than one table.

Can you post the SQL statement of your query, that the newsgroup readers
might be able to get a better understanding of what (and how) you are
trying
to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Beyuduzz]

You are correct, there are multiple tables (queries actually) that the
"hours' are being pulled from. There is a good reason for this. Each set of
hours (CnxHours, AttendeeHours, etc) have check boxes associated with them.
I set up the query so it only Sums up the hours that have a "yes" check box,
otherwise, it would add up all the hours, which I don't want.
So, I know this isn't the most efficient or proper way of doing it, but it's
the only way I could get it to work.
--

I''m a novice with an advance way of thinking.

From your SQL statement, based on the names of the tables, it appears you
have multiple tables, each containing a different "type" of "Hours". If so,
this is exactly what you'd need to do, ... if you were working with a
spreadsheet!

In a relational database (e.g., MS Access), you'd use a single table, with a
field for your time component, and a field to describe "type". All the
"hours" would be in one table.

Your SQL statement appears to have a number of tables (all the names
following "FROM"), but no connection between the tables. If you don't
"join" the tables on common fields, Access will "multiply" the tables'
fields and generate what is called a "cartesian product".

You might want to consider getting the data structure better normalized
before trying to create the queries...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is my SQL from the query.
--------------------------------------------SQL-----------------------------------------------
SELECT Null AS [Date], DateRange_AllHours.SumOfDuration AS Total,
DateRange_Hosthours.[Host hours] AS Host, DateRange_MCUhours.[MCU hours],
DateRange_Attendeehours.TotalAttendee AS Attendee,
DateRange_CnxHours.[Cancelled hours] AS Cancelled
FROM DateRange_Hosthours, DateRange_Attendeehours, DateRange_AllHours,
DateRange_CnxHours, DateRange_MCUhours
GROUP BY Null, DateRange_AllHours.SumOfDuration, DateRange_Hosthours.[Host
hours], DateRange_MCUhours.[MCU hours],
DateRange_Attendeehours.TotalAttendee, DateRange_CnxHours.[Cancelled
hours];
----------------------------------SQL--------------------------------------------------------
If the "DateRange_CnxHours"field has no data the query returns only the
column headings. I don't even get blank cells back.


--
I''m a novice with an advance way of thinking.

You haven't described wether the underlying data comes from a single
table
or the joining of more than one table.

Can you post the SQL statement of your query, that the newsgroup readers
might be able to get a better understanding of what (and how) you are
trying
to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP


How do I keep a query from just returning the column headings. I have
a
query that is returning just the column headings if one of the fields
has
a
null value.

 

[Beyuduzz]

OK. I can clarify my question even further.
Simply put, I want to convert the NULL values of my check boxes to ZEROS. I
found a couple of help sites, but no appear to work.

thanks

 

[Douglas J. Steele]

Use the Nz function in a query:

SELECT Nz(MyCheckboxField, 0) ...

 

[Douglas J. Steele]

I may have misinterpretted your problem.

If your query is returning just the column headings if one of the fields has
a null value, that implies that you've got a condition specified for the
field that has Null (i.e. you're using that field in the Where clause). What
condition(s) are you using in the Where clause?

 

[Beyuduzz]

Funny you should ask. I'm not using a WHERE clause anywhere. I think i'm
going to have to live with this given the unorthodox method I put this query
together. Afterall, it's a query pulling data from a query pulling from a
third query. (Don't ask. Its the only way I could get it to work together.)
If interested, I can post the SQL from the other queries.

 

[Jeff Boyce]

If you'll post the SQL statement, perhaps the newsgroup readers can offer
alternate approaches...

Regards

Jeff Boyce
Microsoft Office/Access MVP

You are correct, there are multiple tables (queries actually) that the
"hours' are being pulled from. There is a good reason for this. Each set
of
hours (CnxHours, AttendeeHours, etc) have check boxes associated with
them.
I set up the query so it only Sums up the hours that have a "yes" check
box,
otherwise, it would add up all the hours, which I don't want.
So, I know this isn't the most efficient or proper way of doing it, but
it's
the only way I could get it to work.
--

I''m a novice with an advance way of thinking.

From your SQL statement, based on the names of the tables, it appears you
have multiple tables, each containing a different "type" of "Hours". If
so,
this is exactly what you'd need to do, ... if you were working with a
spreadsheet!

In a relational database (e.g., MS Access), you'd use a single table,
with a
field for your time component, and a field to describe "type". All the
"hours" would be in one table.

Your SQL statement appears to have a number of tables (all the names
following "FROM"), but no connection between the tables. If you don't
"join" the tables on common fields, Access will "multiply" the tables'
fields and generate what is called a "cartesian product".

You might want to consider getting the data structure better normalized
before trying to create the queries...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is my SQL from the query.
--------------------------------------------SQL-----------------------------------------------
SELECT Null AS [Date], DateRange_AllHours.SumOfDuration AS Total,
DateRange_Hosthours.[Host hours] AS Host, DateRange_MCUhours.[MCU
hours],
DateRange_Attendeehours.TotalAttendee AS Attendee,
DateRange_CnxHours.[Cancelled hours] AS Cancelled
FROM DateRange_Hosthours, DateRange_Attendeehours, DateRange_AllHours,
DateRange_CnxHours, DateRange_MCUhours
GROUP BY Null, DateRange_AllHours.SumOfDuration,
DateRange_Hosthours.[Host
hours], DateRange_MCUhours.[MCU hours],
DateRange_Attendeehours.TotalAttendee, DateRange_CnxHours.[Cancelled
hours];
----------------------------------SQL--------------------------------------------------------
If the "DateRange_CnxHours"field has no data the query returns only
the
column headings. I don't even get blank cells back.


--
I''m a novice with an advance way of thinking.


:

You haven't described wether the underlying data comes from a single
table
or the joining of more than one table.

Can you post the SQL statement of your query, that the newsgroup
readers
might be able to get a better understanding of what (and how) you are
trying
to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP


How do I keep a query from just returning the column headings. I
have
a
query that is returning just the column headings if one of the
fields
has
a
null value.