Fixed Row Report

  • Thread starter Thread starter David Chavez
  • Start date Start date
D

David Chavez

Is it possible to create a report with fixed Rows? If the
row is null then I could have a default of zero. My
Report will be sorted by state and year of expense. I
want to be able to have a default states with default
years. Any ideas?

Thanks
 
If I understand correctly, you have a table of expenses, with a field for
State, and a field for Year, and you want a record for every combination of
State + Year, whether there is a record in your table or not.

If that's the idea, the data has to come from somewhere.
1. Create a table of states (one record for each state), and another table
for years (one record for each year).

2. Create a query containing both tables. If you see any line joining the 2
tables, delete the line. It is the LACK of a join that gives you a record
for every combination (called a Cartesian Product). Save with the name
"qryStateYear".

3. Create another query that uses qryStateYear as an input table, as well as
your original table.

4. In the top of the query design window, drag the State field from the
table to the query. Access draws a join line. Double-click the line. Access
offers 3 options. Choose the one that says:
All records from qryStateYear, and any matches from ...
(This is known as an Outer join.)

5. Still in query design, drag the Year field from the table to the query,
and repeat the process to make this an outer join also.

6. Drag the fields you want into the output grid of the query, e.g.:
qryStateYear.State
qryStateYear.Year
YourTable.Expense

The query returns a record for every combination of Year and State, and the
Expense column is blank where your original table had no record. You can use
Nz() to convert that to a zero if desired.
 
David said:
Is it possible to create a report with fixed Rows? If the
row is null then I could have a default of zero. My
Report will be sorted by state and year of expense. I
want to be able to have a default states with default
years.
Click to expand...


The report can not display nonexistent data. If you want to
show something for every combination of state and year, then
create a one column table that has an entry for every state
and another table with a list of the possible years.

next create a query that has both of those tables with no
Join line between them. This will will produce a list of
all possible combinations.

Next create the report's record source query by adding your
data table and the above query. Make sure the Join lines go
from the two fieds in the query to the corresponding fields
in the table. Right click on each join line and select the
Join Type that includes all the records from the query and
any matching records from the table.

Run the report's record source query from the query design
window and check if you're getting all the appropriate data.
If it's what you want, except for getting nulls instead of
zeros, then use it in the report. If not, post back with
more details about the data and a copy/paste of the SQL view
of both queries.
 
Back
Top