Show Dates With No Record in a Report

[E.Q.]

Greetings,
I'd like to create a database to track materials hauled away for disposal. I
have four tables, three of which are simply meant to fill combo boxes:
tblCompany (chrVendorID,chrCompName),
tblDisposal(chrDisposalSite),tblHauledType(chrHauledType). The "big" table
is tblInvoices (lngInvoiceID,dtmDate,dblWeight,chrMaterial,chrDisposal,
chrCompany).
I have an employee currently tracking this with a lot of cut and paste in a
painfully poorly set up Excel document. I can get the data I'll need for
budgeting from this database, but she generates reports for others as well.
I'd like to emulate one of the reports she's creating. That spreadsheet
report simply has every day of the week along the left side with headings for
the count and combined weight of all loads going to each landfill (this goes
to one specific hauler, so the "chrDisposal" field only has the values of
"Westside" or "Auburn" in this report). So the heading are "Date, Count to
Auburn, Weight to Auburn (tons), Weight to Auburn (lbs), Count to Westside,
Weight to Westside (tons), Weight to Westside (lbs), Total Hauled (tons) ,
Total Hauled (KG)
Two things I don't know how to do: 1. Have the unique disposal sites as a
heading in the report; 2. I need to see zero on the weekend and holiday dates
that the haulers don't come in.
Any help will be appreciated.
Peace.

 

[Klatuu]

It is possible to do what you want to do, but I would not recommend doing it
that way. First, Access is a relational database, not a spreadsheet. There
are significant differences on how they store data and how it can be
retrieved. To accomplish what you want to do would require some VBA
programming and would not be as efficient as it would to restyle the report.

Since your destination landfill is in your record, to list those across the
top would require a complex query to realign the data and, if you ever add a
new site, you have to rewrite the query and the report to accommodate it. It
would be easier to create your report using the Sorting and Grouping
functionality to group your report by landfill. This way, each landfill's
data would report together and you could create a total for it.
As to the empty dates, why bother? If the date doesn't show, then obviously,
nothing was hauled that date. To include this would take additional VBA
programming to create a temporary table to create a range of dates and load
the data by date. This would be a task for a professional.

In short, I recommend you use the native ability of Access rather than try
to make Access look like Excel.

 

[E.Q.]

I agree that the native abilities of Access would work best. My concern has
been in selling the application to the people actually entering the data.
There's a strong resistance to moving some of the functions currently being
done in Excel over to Access, even though a database would be a much stronger
tool. (For example, in another application, I had an employee delete a column
in an Excel worksheet; after that I put in worksheet-level protection and now
I'm getting a lot of complaints about the inability to use Spell Check and
Filtering; I wouldn't need to remove such functions in Access where a user
wouldn't be in a situation to accidentally delete an entire field along with
its contained data.)
For the Excel application currently used for hauling records, a monthly
spreadsheet contains the equivalent of a daily entry form with room for
potentially 15 invoices each day, along with some daily form headings. So
the data for the 31st of the month ends up about 600 lines down from the top
and of course provides no flexibility for creating reports. But it was
created by boss of the employee doing the data entry; I'll need to sell him
on a database approach as well.
I was hoping that that the dates with no hauling could be managed in a
reasonable way. I expect that the inability to show the non-hauling dates
will be construed as proof of the limitations of Access. If the employee has
to export the data to Excel and hand-configure such that all dates show, that
won't save any time over the cut and paste or rigid formula methods being
used now.
But for now I'll develop a prototype and demo it to the employee doing the
data entry. I think she'll like it better than what she has now, even if we
can't emulate the spreadsheet exactly. (I know I'd like it better for the
budget tracking I need to do.)
Regards.

 

[Klatuu]

I understand your dilema. As I said earlier, using some recordset
processing, a report table could be created that would give you the format
you have now. It is not that this is a limitation of Access, it is a matter
of using the power of the tool correctly. If you have any experience in VBA
and would like to give it a go, post back and we can work through it.

I also have found that in more cases than you would believe, people want to
stay with Excel because that want to see the results they want to see rather
than what the results are (if you know what I mean)