Access 2007 Update Advice on What to do

[Guest]

Background
I have a large customer database that I update monthly with figures from
Sage (via Excel)
I import 3 sage files via Excel that are:
Customer Spend (current)
Customer Spend (Last Month)
Custtomer Spend (y2d)
I link these together by the account number & then link this file to the
main database, again using the account number as the key. This works very
well & I have no need to change it.

What I want to do now is, produce a report that will show the spends by
month in a table. So the report will look like:
Customer Name, Acc No, Y2D, Sep 07, Oct07, Nov07 etc.

Trying to link the monthly reports together seems messy as I could only link
2, then link those 2 to another & so on.
So I thought I could do an update query, whilst I know how to do update
queries, I cant convince myself what way to do it.
So I am looking for ideas & advice.
Thanks for reading this far
Steve

 

[Arvin Meyer [MVP]]

Seems like a crosstab query, and report should do it for you. If you have
the YTD data and a datefield within that data, you do not need to use an
update query, merely replace the existing data with the next import.

 

[Guest]

Arvin, Thanks for reply, I have not done a cross tab query, I will read up on
them tomorrow.
Steve

 

[Guest]

Arvin, I had had a look at doing it with a crosstab, I thought I would be
better of using a query & changing the field names on the old months. This is
working & looks ok.
I have one problem, which is. When I run the query I had a sort on a
particular field & the report was ok. I have now changed the sort field in
the query & it sorts as required, BUT in the report the sort is still on the
other field. I have tried going to 'property sheet' & changing the table then
changing back to the original.

 

[Arvin Meyer [MVP]]

Reports have their own sorting and grouping. Look in View >>> Sorting and
Grouping for the dialog box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Arvin
Many thanks, sorted, excuss the pun.
Steve

Reports have their own sorting and grouping. Look in View >>> Sorting and
Grouping for the dialog box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com