[AccessD] Post Card Printing

Hi Mike

For the first run you sort by sequence ID.
To be sorted properly for the second run, they have to be ordered into another sequence, four by four (page) and two by two (column).

If the first run is printed column by column, a page will be printed like this:

  1  3
  2  4

where the CardIDs are sequential: 1 2 3 4, 5 6 7 8, etc.
To print correctly on the other side of the page, the second run must be printed this way:

  3  1
  4  2

thus the CardIDs must be ordered: 3 4 1 2, 7 8 5 6, etc.

The first query pulls the records in sequential order as well as the corresponding values for page and column to allow for the alternative ordering for the second run. As you need four cards to be printed on page 1 you need to compensate by passing an offset which equals the negative value of the first ID.

Without an offset (=0) and a first CardID of 1 you would only get three cards on the first page:

Page4	Column2	CardID
0	0	1
0	1	2
0	1	3
1	0	4
1	0	5
1	1	6
1	1	7
2	0	8

With an offset of -1 the pages will be filled correctly:

Page4	Column2	CardID
0	0	1
0	0	2
0	1	3
0	1	4
1	0	5
1	0	6
1	1	7
1	1	8

To retrieve the records for the first run, you really don't need to sort on anything else than the CardID:

  SELECT 
    Page4, 
    Column2, 
    CardID
  FROM 
    tblCardID
  ORDER BY 
    CardID;

For the second run, however, you need the other sorting on page and column as well:

  SELECT 
    Page4, 
    Column2, 
    CardID
  FROM 
    tblCardID
  ORDER BY 
    Page4, 
    Column2 DESC, 
    CardID;

This will return:

Page4	Column2	CardID
0	1	3
0	1	4
0	0	1
0	0	2
1	1	7
1	1	8
1	0	5
1	0	6

which is what you need.

Use these two queries as the basis for the queries for pulling the records to print first and second run respectively.

The temp table is only needed if you can't get the sorting right for the second run; sometimes Access refuses to sort on derived values.

If you don't have a sequential ID, look up the subject "Sequence number in query" from 2006-01-22 or write all the data you need to print to another temp table with an AutoNumber field.

/gustav

>>> Mike.W.Gowey at doc.state.or.us 02-02-2006 18:32:48 >>>

Sorry Gustav I am lost on the first part on creating the temp table with
the Offset and ID part.

-----Original Message-----
From: Gowey Mike W 
Sent: Thursday, February 02, 2006 10:10 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Post Card Printing

Sorry replied to the wrong post.


When you say sequential ID for the records do you mean within the
customer table?  I do have a unique customer ID for each customer within
the customer table, will that work?  

-----Original Message-----
From: Gustav Brock [mailto:Gustav at cactus.dk] 
Sent: Thursday, February 02, 2006 9:41 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Post Card Printing

Hi Mike

You will need a sequential ID for the records.
Then create a query to write a temp table:

  SELECT 
    ([Offset]+[ID])\4 AS Page4, 
    (([Offset]+[ID])\2) Mod 2 AS Column2, 
    ID AS CardID
  INTO 
    tblCardID
  FROM 
    tblYourTable;

Use parameter Offset to adjust the sequence for full pages by setting it
to the negative value of the first ID. If this is 67, set Offset to -67.
You could retrieve that value first or by an in-line subquery, but I'll
leave that to you.

If you set up columns to go down and then across, you can use this
method.
Read and sort the table for printing the front side of the cards:

  SELECT 
    Page4, 
    Column2, 
    CardID
  FROM 
    tblCardID
  ORDER BY 
    Page4, 
    Column2, 
    CardID;

and sort this way for the rear side of the cards:

  SELECT 
    Page4, 
    Column2, 
    CardID
  FROM 
    tblCardID
  ORDER BY 
    Page4, 
    Column2 DESC, 
    CardID;

Put this code into two queries and create inner join to your original
table and pull the fields you need from this to print the cards.

You may be able to skip the temp table and sort directly but it does not
always work.
Also, you may later reduce the queries like this:

  SELECT 
    CardID
  FROM 
    tblCardID
  ORDER BY 
    Page4, 
    Column2 DESC, 
    CardID;

By adjusting the sorting and the across/down sequence you can create
other printing sequences.

/gustav


>>> Mike.W.Gowey at doc.state.or.us 02-02-2006 16:21:32 >>>
 

I'm wondering if any one knows of a way that I can get post cards to
print from the database.  Here is what I am attempting to do but it is
not working.  I print the front of the card with the return address and
the address of the customer (4 per page).  Than what I want to be able
to do is flip the card stock over and print the customer billing data on
the back.  Currently the columns are set up to go across and than down
for printing.  Is there a way that I can get the columns to print across
the bottom and than up?  The problem is when I turn over the card stock
than the names and detail do not match up, they are backwards, because
of course when you flip the card stock over the first record is at the
bottom of the page instead of the top.

Anybody have any ideas?

Thanks in advance for any ideas,


Mike Gowey  MCSA, MCDST, A+, LME, NET+
Team Leader - SouthEast Region
Information Systems Unit