Gustav Brock
Gustav at cactus.dk
Fri Feb 10 04:38:02 CST 2006
Hi Mike
That will happen if you don't use the right value for Offset. The right value will ensure that the first page is full having two Column2 of 0 followed by two of 1. Please read the explanation on this (below, from the original post).
For your example data, it seems Offset should be -69.
By second thought, you could easily pull this value automatically.
This is the revised query doing that:
SELECT
([ID]-[Offset])\4 AS Page4,
(([ID]-[Offset])\2) Mod 2 AS Column2,
ID AS CardID,
(SELECT
MIN(deliveryid)
FROM
tblYourTable;) AS
Offset
INTO
tblCardID
FROM
tblYourTable;
/gustav
>>> Mike.W.Gowey at doc.state.or.us 09-02-2006 23:28:36 >>>
Gustav,
Sorry to be such a pest, when I create the tblCardID table it keeps
putting at the top the Page4 number that has the least amount of
records.
For Example:
Page4 Column2 CardID
17 0 69
17 1 70
17 1 71
18 0 72
18 0 73
18 1 74
18 1 75
19 0 76
19 0 77
19 1 78
19 1 79
20 0 80
20 0 81
20 1 82
20 1 83
So when I apply the query below to add records if needed if does not
find any because the top records are 20 and already has 4 records but 17
has only 3 records.
Any Idea why it does that?
Thanks again
-----Original Message-----
From: Gustav Brock [mailto:Gustav at cactus.dk]
Sent: Thursday, February 09, 2006 11:01 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Post Card Printing
Hi Mike
After you have (re)created the tblCardID table for sorting, you will
need to run a second query which appends missing records to tblCardID up
to a multiplum of four.
If the last page has one card, it will add records for three empty
cards, if two is present, then two cards are added, etc. Thus your two
printing queries will always return full pages of four cards.
This is the query (watch out, guru code):
INSERT INTO
tblCardID
( Page4, Column2, CardID )
SELECT TOP 4
[Page4]+1 AS PageMax,
Column2,
[CardID]+4 AS Card1
FROM
tblCardID
WHERE
(([CardID]+4) Not In
(Select Top 4
CardID
From
tblCardID
Order By
CardID DESC;))
AND
([Page4]=
(Select
Max(Page4)-1
From
tblCardID;))
ORDER BY
Page4 DESC,
Column2 DESC,
CardID DESC;
/gustav
>>> Mike.W.Gowey at doc.state.or.us 06-02-2006 19:01:06 >>>
Gustav,
One problem I have run into on this, it works great as long as you have
a record count that is divisable by 4. If the record count is not
divisible by 4 the last page that prints when you turn over the post
card prints incorrectly because of the order, so if you have a record
count of 14 than the first 3 pages print great, but the last page that
only has 2 records on it will print on the opposite side of the page.
Any ideas on maybe how to correct this or how to insert blank records
when not divisible by 4?
Thanks,
Mike
-----Original Message-----
From: Gustav Brock [mailto:Gustav at cactus.dk]
Sent: Friday, February 03, 2006 4:47 AM
To: accessd at databaseadvisors.com
Subject: Re: [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