Subform based on a query with no data yet

[SunTsu]

Hi experts!

I need to create a subform based on a following recordset: date_field, text_field, numerical_fields1-17. text-field have 5 specific values and I need the final record set to consist of 5 rows (based on text_field) followed by 17 columns of numerical fields where date_field is used for filtering purposes and is being taken from main form. The problem is that there might be no records for exact date but I still need to have five rows with five unique text_field values and numercal_fields 1 through 17 shown as '0'.

When I try to use nz function on date in query I get the records with null records for specific text_field, and other dates for the rest of specific text_field. When I add "Where" clause I see only those rows of text_field where date is null. I am totally fine with dynamically created record source but I still cannot figure out how to do it.

Thanks!

 

[jdraw]

I think it would be helpful if you told us about your application, and a model of your tables and relationships. Rarely can you work from a subform description out of context. Some sample data and a picture/mock up of the expected output would also be helpful.

 

[SunTsu]

I am using MS Access 2007.
I have a base query (q1) that has 19 columns: date, Row_name (with 5 possible values) and 17 numerical columns. You are right, at the moment it doesn't matter that it's going to be used in subform. Basically, a query that provides a record set with five rows and 18 visible columns filtered by date even if there is no data for that date/row (in this case I need to have '0' in field using nz function)
If I didn't understand correctly what you mean by "model" could you please show me a good example?

 

[jdraw]

There are several free data models here.

Your data represents something -- you mention query but not table(s).

Perhaps some description and some sample data would help.

 

[SunTsu]

Perhaps some description and some sample data would help.

Here is the sample query (final query will have all num_fields with nz function to convert null values to '0'

SELECT t1.Date_field, t0.textfiel, t1.Num_field1, t1.Num_field2, t1.Num_field3, t1.Num_field4, t1.Num_field5, t1.Num_field6, t1.Num_field7, t1.Num_field8, t1.Num_field9, t1.Num_field10, t1.Num_field11, t1.Num_field12, t1.Num_field13, t1.Num_field14, t1.Num_field15, t1.Num_field16, t1.Num_field17
FROM t0 LEFT JOIN t1 ON t0.ID = t1.text_field;

The screenshot of results of this query is in q1.png. The result I need is in final_table.png. However, this final example still does not answer how to populate in empty fields the date I need.

Thanks!

 

[smig]

what kind of query is it if you already know the number of rows in it ?
The only thing I can think of is a kind of pivot table.

 

[SunTsu]

what kind of query is it if you already know the number of rows in it ?
The only thing I can think of is a kind of pivot table.

Ok, let's try with a pivot table.
Basically, I have a table ("tType") that has just IDs and names of five meals: breakfast, lunch, etc. And I have a query that contains all the summary for those meals for different dates such total of calories. When I run the query for the date where there are records for all five meals I get the correct output. But when there are no records for any date/meal combination I need the record/row still to be shown with '0' values populated in calories field. Left join shows me only those record that have data for the date/meal combination and populate zeros (via nz function) for those meals that are absolutely absent in the table. For example, if there is a breakfast data for today and yesterday, lunch data for yesterday and no records for dinner I see breakfast and dinner records but no record is shown for lunch.

Thanks!