Custom report needed with line for every day of month, regardless of whether or not data exists for

[DKMortensen]

I am having trouble modifying a report in accordance with the
requirements recently required by a customer.

To make matters worse, all of the Access databases we've done for our
clients have been done by another employee in our company, who started
a new job 3 weeks ago. Now I am following in his "Access footsteps". I
have C++ programming experience, as well as have done a lot with web
sites, linux scripting & some basic database design. So, other than a
learning curve, I think that I should be able to continue to maintain
his creations as needed by our clients. However, this first request
seems to have me a little perplexed. Help would be GREATLY
appreciated ;-)

The problem is as follows:

There is a report that the customer can view, which shows expenses,
grouped by day. Each line is one day, with a column for each expense
'type', and then the last column gives the total for the expenses of
that day. That works fine.

The problem is that the customer also wants a column before the end,
which give's each day's net profit. This it not really a problem
either. It also works fine.

The last line contains monthly totals for each column (expense type).
This is where things "mostly" work, but are not exactly what is
needed.

Everything I have mentioned was created by my past co-worker in the
initial incarnation of this database. The customer recently called me
and said that the report was only including days where an expense was
entered. This is fine for the majority of data in the report, however,
was a problem with the "Net Profit" column. This is because most every
day, the business generates a net profit (either positive or
negative). Not including data from the days where no expenses were
incurred causes the net profit for the days with no expenses to be
omitted. Nothing gets displayed for those days. There is not even a
line in the report for those days. In addition to the problem of this
data not showing up for those days, a second problem that this creates
is that the final line of the report, which contains totals for each
day, displays an the wrong number for the "Net Profit". This is
because it is only including the values for those lines which are
displayed above it in the report.

This is where our client has the problem. This is also where I've been
struggling to come up with the correct method to accomplish what is
needed.

What the client needs is a line for EVERY day of the month to appear
on the report, regardless of whether any data exists in the database
tables for that day. If no data exists, then no numbers need printed.
We don't needs zeros in the fields to know that there were no such
expenses those days. We know that a blank value (nothing) means zero.

Looking further at the current design, and how to accomplish this,
I've found that the report is getting its data from 1 query. As I've
looked at that query, it is actually a cross-tab query, getting all of
its data EXCEPT for the net profit, from one table. The expense table.
For each day in the current working month that expenses exist (in the
expense table), the query returns a line which gives a sum of the
expenses for that day, broken down by category (again, each category
is represented by a column). One of the last columns however is the
net profit column, which actually pulls its value from a separate
table. Its 'Field' field has the value:
Net Income: DLookUp("GrossProfit","UnsecuredLoansQuery","ULDate=#" &
[ExpenseDate] & "#")-[Total Of ExpenseCost]

ExpenseCost is one of the columns/values in each line of our the cross-
tab query.

I've had various ideas as to how to make it display a line for every
month, and experimented with some of them, but to no avail. I think
the bottom line is that we need to have the first field of the query
(which is the ExpenseDate, from the Expenses table), get its data from
somewhere other than the Expenses table, since it is not a reliable
source of returning EVERY day of the month.

I appreciate any help/ideas VERY much, and would be willing to provide
some type of compensation to someone who was able to produce the
desired result for me. Our customer has asked us to deliver this new
report by 5/31/07, which give us about 6 days now.

Again, thanks in advance for your help!!

Sincerely,
-
Doug Mortensen
impala Networks
www.impalanetworks.com

 

[John W. Vinson]

I've had various ideas as to how to make it display a line for every
month, and experimented with some of them, but to no avail. I think
the bottom line is that we need to have the first field of the query
(which is the ExpenseDate, from the Expenses table), get its data from
somewhere other than the Expenses table, since it is not a reliable
source of returning EVERY day of the month.

You're on target with this idea.

I'd suggest creating a table Calendar, with one date/time field; use Excel
fill-series to fill it with dates from January 1 2007 (or some other
appropriate date) for twenty or thirty years.

Base your Report on a Query left-joining this table to your expenses table.
This will give you a record for every date, with NULL values for dates which
do not occur in the expenses table.

John W. Vinson [MVP]

 

[DKMortensen]

John,

Thank you VERY much for the quick response. YOU ARE THE MAN! And if
this works, you will be the man even more ;-)

I actually had thought of something very similar. Only I was thinking
of having a Calendar table that only kept the dates for the current
month, and whose records were dynamically purged & recreated each time
that the user clicks on the button to view the report. That way we
don't have an extra couple of Megabytes of data in the database, that
just store dates for this query. I don't know how simple that would be
to implement though. I did some checking, but it seemed like a lot of
work to write a VBA script for "onclick" to do all of that. What I saw
would have involved telling the script where the database was, having
it connect to it (which included at least 2-3 steps), and from there I
still needed to figure out how to dynamically delete & create records.
I would have used a loop to re-populate the table.

I'll see how much the database grows by creating the Calendar table as
you suggested, and if it's not significant, then I'll probably just
stick with that solution. It would be much simpler. In the mean time
though, I would be interested in whether you think my idea above is
worth considering, or really just a bad idea, due to things that I am
not aware of (which at this point encompasses a LOT of things ;-)

Really, I appreicate your advice. I'll give it a try and post the
results here.

-
Doug Mortensen

 

[John W. Vinson]

That way we
don't have an extra couple of Megabytes of data in the database, that
just store dates for this query.

Eh. A Date/Time is a Double Float, 8 bytes. 100 years of dates - 36525 records
- 29200 bytes, not even half a meg. Don't sweat it!

You can even ( at the cost of some reduced efficiency) instead use a VERY
handy multiuse table Iotas, with one Long Integer field Iota, values 0 through
100000 or so; and join to a query with

DateAdd("d", [Iota], #1/1/2000#)

I'd use both tables though.

John W. Vinson [MVP]

 

[Albert D. Kallal]

I don't know how simple that would be

to implement though. I did some checking, but it seemed like a lot of
work to write a VBA script for "onclick" to do all of that. What I saw
would have involved telling the script where the database was, having
it connect to it (which included at least 2-3 steps), and from there I
still needed to figure out how to dynamically delete & create records.
I would have used a loop to re-populate the table.

I would write out one year. Remember, your designs must be a balance
between how often you delete data. Deleting data, and re-filling tables
is a VERY bad design, since when you delete records, the disk space
in the application is not returned until you do a compact and repair. So,
design as a general rule should avoid *unnecessary* deleting, and
re-creating of data. So, I think a nice compromise is to write out the
current year....

The code is quite easy:

You could call this routine every before you launch a report...

Sub MakeDates()

Dim dtStart As Date
Dim dtend As Date
Dim lngDPtr As Long
Dim rstDates As DAO.Recordset
Dim bolMakeDates As Boolean
dim strSql As String
' check if table matches this year.....

strSql = "select * from tblDatelist order by Mydate"
Set rstDates = CurrentDb.OpenRecordset(strSql)
If rstDates.RecordCount = 0 Then
bolMakeDates = True
ElseIf Year(rstDates!MyDate) <> Year(Date) Then
bolMakeDates = True
End If

If bolMakeDates Then

CurrentDb.Execute "delete * from tblDatelist"
dtStart = DateSerial(Year(Date), 1, 1)
dtend = DateSerial(Year(Date) + 1, 1, 1) - 1
For lngDPtr = dtStart To dtend
rstDates.AddNew
rstDates!MyDate = lngDPtr
rstDates.Update
Next lngDPtr

End If
rstDates.Close

End Sub

The above would correlty set the table with leap years etc. The reocrd count
check is for the first time you run this, as there will be no reocrds. And,
while the above is "air" code, you can see that it not that hard (hardly 20
lines of code).

And, I could/should have declared the lngDPtr as a date type also, but a
long data type will work just fine....

 

[DKMortensen]

Gentlemen,

Thanks a lot for the advice. I have been trying to follow John's first
recommendation. But I am having some problems getting the
implementation to work correctly. Have posted a screenshot that shows
the query in its previous form, and then another showing what I'm
trying to do, but I'm not positive what I need to do to get the
existing fields in the query to pull the date they use from the first
column in the query (which uses the new Calendar table).

The screen shots are available here:
http://www.kgotsi.com/static.php?page=access_query_problems_screenshots

I REALLY appreciate your help!! Thanks again in advance!

-
Doug Mortensen
Impala Networks

 

[DKMortensen]

I'm more than willing to pay for somebody to take care of this for me.
I really don't think it would take that long though, if I was just a
bit more familiar with Access, or had somebody explain to me what I
need to do. However, time is money and I don't expect someone to take
all day to help me for free. So if your terms for help with this
require some compensation, just let me know, and we can work it out
offline. You can get me at doug(--at--)impalanetworks(dott--)com

Our phone number is available at www.impalanetworks.com/contact.htm

Thanks again!

-
Doug Mortensen
Impala Networks

 

[Albert D. Kallal]

You need to drop in the "calendar" table FIRST....

place the DayForline as your first field in the query builder.....

Now, simply drop in the 2nd table into the query builder (you can right
click in the area where the Dayforline table is, and select "add table".

So, drop in your 2nd table.....

Now, simply draw a join line from the calendar "DayForLine" field to the
Expense date field in the Expenses table. Now, double click on this join
line and make it a left join.


The above type simple placing of tables in the query builder, and making a
join line is a basic skill you need to acquire.

Further, it looks like this query might be a cross tab, so you want to
actually build this query that joins calendar + Expense BEFORE you make the
cross tab. (in other words, this query that has a date for each day (+ the
expense table and fields) will be your base query that you build the cross
tab on...


here is some informaton on makeing left joins:

==================================

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to!

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

For most applications, you can see that a VERY high % of joins are left
joins.