Report for each individual record

[waldorfedu]

I have created a Table and imported over 200 records with a over 10 columns
(copying and pasting from FileMaker). I am going to create a Report
(Invoice). I would need to have an individual Report for each record but
every time I do something with the Invoice it relates to all the records -
advice appreciated - thanks.

 

[John W. Vinson]

I have created a Table and imported over 200 records with a over 10 columns
(copying and pasting from FileMaker). I am going to create a Report
(Invoice). I would need to have an individual Report for each record but
every time I do something with the Invoice it relates to all the records -
advice appreciated - thanks.

I'm sure you don't want 200 separate Access Report objects, do you?

If you want to run a Report (i.e. use an Access Report to format, display
and/or print one or more records) on a single record, or a subset of records,
base the report on a Query selecting those records (rather than directly on
the table). You can, for instance, launch the report from a Form, and
reference that form in the query criterion:

=Forms!YourFormName!YourControlName

as a criterion on the invoice ID.

It's not at all clear from your post what you're doing when you "do
something".

John W. Vinson [MVP]

 

[waldorfedu]

Thank you for your input. It is all very new to me so I'm sorry if I am not
being clear. All I want to do is to be able to add a record and then print
out a Report (Invoice) for that particular record (no other record) and to be
able input appropriate data from the Table but every time I seem to end up
abstracting data from all the records etc. Perhaps I'm getting old but
I've learned FileMaker Dreamweaver, InDesign, Premiere and Illustrator but
perhaps Access is too complicated for what I want to do?

 

[John W. Vinson]

Thank you for your input. It is all very new to me so I'm sorry if I am not
being clear. All I want to do is to be able to add a record and then print
out a Report (Invoice) for that particular record (no other record) and to be
able input appropriate data from the Table but every time I seem to end up
abstracting data from all the records etc. Perhaps I'm getting old but
I've learned FileMaker Dreamweaver, InDesign, Premiere and Illustrator but
perhaps Access is too complicated for what I want to do?

It's not any more complicated - but it *is different*. Access is not a flawed
implementation of FileMaker; it's a different program and uses different
conventions and different techniques.

You have not posted any indication of your table or database structure, so I'm
working a bit in the dark here - but here's how I'd approach the problem.

1. Create a properly normalized set of Tables. For an invoicing system, you'll
probably want a table of Customers related one-to-many to a table of Orders;
if an invoice (order) can include multiple products, you'll need a table of
Products (what can be ordered) and an OrderDetails table (a list of the items
included in one order).
2. Create a Form to enter the data (a form with a subform, almost certainly).
3. Create a Report for your invoice, based on a query referencing the form.
4. Put a button on the form to launch the report.

Take a look at the Northwind sample database which came with your installation
of Access for an example.


John W. Vinson [MVP]

 

[waldorfedu]

Hi, Thanks, I am making progress. I used a form and found "Add Existing
Fields" that brought in the appropriate information from the Table. However,
I used the Property box for default width and height (letter size) and that
did not work - every time I go to Print Preview it indicates size is too
large. I need to have form letter size, probably simple but I cannot find it
- I have tried manually fitting by dragging outer edges but still not right -
there must be a simpler way - advice would be appreciated - thanks.

 

[John W. Vinson]

Hi, Thanks, I am making progress. I used a form and found "Add Existing
Fields" that brought in the appropriate information from the Table. However,
I used the Property box for default width and height (letter size) and that
did not work - every time I go to Print Preview it indicates size is too
large. I need to have form letter size, probably simple but I cannot find it
- I have tried manually fitting by dragging outer edges but still not right -
there must be a simpler way - advice would be appreciated - thanks.

This may be a jargon confusion.

An Access "Form" object is designed to be used onscreen for data viewing and
editing.

If you want to print something onto paper, don't use a Form - use a Report
instead.


John W. Vinson [MVP]

 

[waldorfedu]

Hi, Thanks for your help - I'm almost there. Problem - in Table I have say
column "Order1" - next column is "Quantity1" (say 20 ordered), next column is
"Price1" (say $1 each). I want the next column to be "Net1" (total of Q x P)
so I need to multiply the Q1 x P1 but I do not know how to do this - advice
gratefully received - thanks.

 

[John W. Vinson]

Hi, Thanks for your help - I'm almost there. Problem - in Table I have say
column "Order1" - next column is "Quantity1" (say 20 ordered), next column is
"Price1" (say $1 each). I want the next column to be "Net1" (total of Q x P)
so I need to multiply the Q1 x P1 but I do not know how to do this - advice
gratefully received - thanks.

You do not do this in a Table. Storing derived data such as this in your table
accomplishes three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and most importantly,
it risks data corruption. If one of the underlying fields is subsequently
edited, you will have data in your table WHICH IS WRONG, and no automatic way
to detect that fact.

Instead, create a Query based on the table. You can include the price and
quantity fields from the table, and in a vacant Field cell put

Net: [Quantity1] * [Price1]

Now for a much more serious possible problem: if you have fields in your table
named Order1, Quantity1, Price1, Order2, Quantity2, Price2, ..., Order10,
Quantity10, Price10 then you *DO NOT HAVE A TABLE*. You have a Spreadsheet!
Access isn't a spreadsheet and should not use spreadsheet logic! Each item
ordered should be in a separate record in a different table, related one to
many to your orders table. Take a look at the Orders application in the
Northwind sample database which comes with Access, and perhaps also look at
Crystal's tutorial or the Database Design 101 links on Jeff's webpage:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]

 

[waldorfedu]

Thank you for your email and information. You have given me much to research
and it is going to take me some time. Many thanks for the links - thanks.

 

[nolielinda]

I'm sure you don't want 200 separate Access Report objects, do you?

If you want to run a Report (i.e. use an Access Report to format, display
and/or print one or more records) on a single record, or a subset of records,
base the report on a Query selecting those records (rather than directly on
the table). You can, for instance, launch the report from a Form, and
reference that form in the query criterion:

=Forms!YourFormName!YourControlName

as a criterion on the invoice ID.

It's not at all clear from your post what you're doing when you "do
something".

John W. Vinson [MVP]

Hi John
I am having the same issue. I have bought a book on access 2007 and
have went on the help desk and gone on line I am having trouble
creating a report for just one record. I reviewed your posts and am
still having a hard time.
I believe my relationships are set correctly, one to many I have
created forms and report many times but can not get a report for one
record. Just all the records. The last thing was I had too many
fields for create the report. I do not believe I have that many
fields. I have 6 tables some with only one field. My data base is
not on sales or products its on more like employee info and
evaluation. so I would like to Create a report for each "employee"
with all information and comments on that person. Next I have a
scorring issure on how to calculate a score I want the total to end up
in a seperate table if possible. As long as it go's on the report I
guess it does not matter were it ends up. My main issue is the report
I am in desperate need of you expertise. Thanks
Linda

 

[John W. Vinson]

Hi John
I am having the same issue. I have bought a book on access 2007 and
have went on the help desk and gone on line I am having trouble
creating a report for just one record. I reviewed your posts and am
still having a hard time.
I believe my relationships are set correctly, one to many I have
created forms and report many times but can not get a report for one
record. Just all the records. The last thing was I had too many
fields for create the report. I do not believe I have that many
fields. I have 6 tables some with only one field. My data base is
not on sales or products its on more like employee info and
evaluation. so I would like to Create a report for each "employee"
with all information and comments on that person. Next I have a
scorring issure on how to calculate a score I want the total to end up
in a seperate table if possible. As long as it go's on the report I
guess it does not matter were it ends up. My main issue is the report
I am in desperate need of you expertise. Thanks

It sounds like you need to base the Report on a query with a criterion which
displays just the one employee's record. You may be basing it on the entire
table.

Please open the report in design view, view its Properties, and click the ...
icon by the Recordsource property. Open the query in SQL view; copy and paste
the SQL text to a message here. How do you want to decide WHICH one employee's
record should be printed? Or do you want all employees' records printed out,
but just one employee per page? I'm not sure what you're expecting!

As for the total... it should not be stored in ANY table. Calculated values
should be calculated on demand, based on the values in the table. You don't
say what it is that you want summed or where, so I really can't advise other
than to say that you can do calculations in Queries or in form or report
controls.

John W. Vinson [MVP]