Query/Report

[Beginner]

I have a table with detailed records of sale for every
month. I have to pull up the fields manipulate the data
and printout out a detailed summary of the records for
each customer. (a soft copy is also required).

Right now I am using a query to do the needful and
download the reports to excel, which I send to the
customers as both soft and hardcopies.

My question is :
Now the customer names are all hardcoded in the queries.
Is there a way to have a table of customer names(which
would be updated as and when required) linked to the
monthly table so I get the detailed statements for each
customer.
I tried the alternative-using reports . Reports work well
for taking printouts. for sending the sending the
statements by email, is there any way other than
snapshotviewer, since the users would like to manipulate
the file I send them.

Thanks for any help

Beginner
-----------------------------------------------------------
I have begun a new thread, but attaching my previous
question and answers for reference..
Beginner,

Possibly the way to do it would be to use a combobox whose
RowSource
is the Customers table, and then you can just select the
customer
required from the list. Would that work for you ok?

By the way, the answer given by Chris B was almost right,
but in fact
will not quite work. The Like operator is incorrect in
this context,
and the expression should use !s and not .s so use
criteria like this:
[Forms]![Form1]![ComboBox]

- Steve Schapel, Microsoft Access MVP

 

[Steve Schapel]

Beginner,

Exporting your report to Snapshot format, or printing it to a PDF
file, are the best ways from the point of view of preserving the
presentation of your data. But if the customer wants to be able to
edit the data, this doesn't work (although I understand that the most
recent versions of Adobe Acrobat allow the editing of existing PDF
documents). You could export to a RTF format document. The
disadvantage here would be that you would lose any graphic formatting
elements, including lines, in the report, but at least it would allow
the customer to use Word to edit. But you didn't actually say what
the problem is with your current system of exporting to a spreadsheet,
which seems to me, on the basis of what you have told us so far, to be
a good approach. By the way, you can export a report to a
spreadsheet, as well as directly from the query.

As regards the selection of the customer whose record you want to use,
I must admit that in my earlier replies I made the assumption that you
already had a table of Customers. If you haven't, then you need one
anyway! And I repeat my earlier advice to use a combobox to select
the customer, and then refer to the combobox in the criteria of the
query. If you don't like this idea, could you please say why, rather
than just re-asking the question when you have already been given an
answer?

- Steve Schapel, Microsoft Access MVP

 

[Beginner]

I would like to automate obtaining the statement for each
customer-with a combo box, we would have to go and
manually select each customer-Is my assumption correct ?

Instead I would like the macro/query to automatically
pull up the records pertaining to each customer and
download as a separate file. I am doing this currently by
running a macro which has several queries for each
customer A, B, C etc. If customer A changes to X or
customer W gets added, then I would have to go change the
corresponding query. And I would like to avoid that.
I hope I made my question clear-is it possible to keep
the customer names as variables-I am new to Access and so
have no idea of doing it

Thanks for any further help

Beginner

 

[Steve Schapel]

Beginner,

Thanks for the further explanation. You are correct, that the idea of
using a customer selected in a combobox as a criteria would involve
manually selecting each customer one by one.

If you want each customer's data presented as a separate report, you
can base a Report on a query which includes the data for all
customers, and then use the Report's 'Sorting and Grouping' facility
to show the data for each customer on a separate page. But this is
still all data in the one file if you export it.

It is possible to cycle through the customers one by one using a
macro, and exporting a file with the data for each. Assuming you have
a Customers table, make a temporary copy of it, and then this would
involve exporting, using your macro, from a query whose criteria is
DMax("[Customer]","CustomersCopy") followed by an OpenQuery action in
your macro to run a Delete Query to delete from the CustomersCopy
table using the same criteria. Make another macro using the RunMacro
action to run the macro that contains these two existing actions, and
in its Repeat Count argument put =DCount("*","Customers")

Obviously this is not a trivial procedure, but with a bit of work you
should be able to get it going properly. Otherwise, a somewhat less
awkward (though still non-trivial) approach is to use a VBA procedure
rather than a macro, using a For Each ... Next construct to loop
through the Customers and output the report for each.

I realise that this is not a complete answer. A complete answer would
be a long answer. But hopefully it might point you in the right
direction.

- Steve Schapel, Microsoft Access MVP

 

[Beginner]

Thanks Steve

Your answer gives me an idea. I am deciding not proceeding
with the macro for this process and rather explore
the "For each " option with VBA . I am very new to VBA in
Access(though I have a VB background), but it would be
worth a try to do it in VBA

Thanks once again for all your help.

Beginner

-----Original Message-----
Beginner,

Thanks for the further explanation. You are correct, that the idea of
using a customer selected in a combobox as a criteria would involve
manually selecting each customer one by one.

If you want each customer's data presented as a separate report, you
can base a Report on a query which includes the data for all
customers, and then use the Report's 'Sorting and Grouping' facility
to show the data for each customer on a separate page. But this is
still all data in the one file if you export it.

It is possible to cycle through the customers one by one using a
macro, and exporting a file with the data for each. Assuming you have
a Customers table, make a temporary copy of it, and then this would
involve exporting, using your macro, from a query whose criteria is
DMax("[Customer]","CustomersCopy") followed by an OpenQuery action in
your macro to run a Delete Query to delete from the CustomersCopy
table using the same criteria. Make another macro using the RunMacro
action to run the macro that contains these two existing actions, and
in its Repeat Count argument put =DCount("*","Customers")

Obviously this is not a trivial procedure, but with a bit of work you
should be able to get it going properly. Otherwise, a somewhat less
awkward (though still non-trivial) approach is to use a VBA procedure
rather than a macro, using a For Each ... Next construct to loop
through the Customers and output the report for each.

I realise that this is not a complete answer. A complete answer would
be a long answer. But hopefully it might point you in the right
direction.

- Steve Schapel, Microsoft Access MVP

I would like to automate obtaining the statement for each
customer-with a combo box, we would have to go and
manually select each customer-Is my assumption correct ?

Instead I would like the macro/query to automatically
pull up the records pertaining to each customer and
download as a separate file. I am doing this currently by
running a macro which has several queries for each
customer A, B, C etc. If customer A changes to X or
customer W gets added, then I would have to go change the
corresponding query. And I would like to avoid that.
I hope I made my question clear-is it possible to keep
the customer names as variables-I am new to Access and so
have no idea of doing it

Thanks for any further help

Beginner

.