Make Tables Query

[Jennifer]

Every month I upload that month's billing information into Access and I
create a running total of each customer's total billed in different cost
centers. When I created this project I was given the first 4 months and I
made master tables using the months I had and then from there created cross
tab queries that put the customer as the row header, the month as the column
header, and the sum as the value. From here I created a Master sum table and
then created a final cross tab query with only the sums as the values and the
cost centers as the column headers.

My problem arises when I try to upload new months. I go through the same
process by appending the original master table and then running a macro that
adds the new month into all the queries. However, after the new month is
added into all the queries I need to append the Master Sum table. But when I
append the table something funny happens. A new month column is added but
most of the values are null. I know that this happens because when i
originally created the Master Sum Table I added all 4 months for each cost
center so to go back and try to add an additional month to those existing
records doesn't work.

The only way I know how to get around this is to delete the current table
and make it all over again but this time adding all 5 months. This, however,
would take forever since there are about 30 cost centers which = 30 queries
to append into the table.

Any solutions?

 

[Larry Daugherty]

Yes. First, understand that you have committed "spreadsheet" on an
Access table. That tends to degrade its utility as a relational
object.

You need to learn and apply some of the fundamentals of Relational
DataBase Management Systems (RDBMS). Access is actually a cluster of
developer tools and user interface elements layered atop the
developer's choice of RDBMS. By default you get JET. One of those
fundamental concepts is "Normalization". I believe that they get into
it a bit at www.mvps.org/access If not there then you can google
these newsgroups on that word or even google the web. You won't be
able to deliver very sophisticated Access applications until you've
mastered and learned to apply the concepts.

Things like dates or time values: years, months, weeks, days, ...
should be neither table names nor column names. They are all
entities of the date/time datatype and should be entered as values.
It is very likely that the tables containing the data which have
columns badly named could actually span years and years if only the
date/time values had been entered as such. You can go back and
correct it. For what it's worth, I recommend correcting and keeping
"old" data along with the new. You will have ample reach within
Access for 2 Gbytes of data so don't worry about space. The retained
data allows you to later change what you do with your application such
as trending and measuring performance on a given variable over a
selected span of time. The month is an attribute of the records in
your application. The particular value of the month is specific to a
given record.

I recommend that you lurk

microsoft.public.access.gettingstarted and
microsoft.public.access.tablesdesign

HTH