Gowey Mike W
Mike.W.Gowey at doc.state.or.us
Thu Feb 9 12:01:10 CST 2006
Thanks Mark that would be great. -----Original Message----- From: Mark A Matte [mailto:markamatte at hotmail.com] Sent: Thursday, February 09, 2006 10:56 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Post Card Printing Mike, I did this exact thing a few years ago. I'll send you a small db offline...let me know if you have any questions. Thanks, Mark A. Matte >From: "Gowey Mike W" <Mike.W.Gowey at doc.state.or.us> >Reply-To: Access Developers discussion and problem >solving<accessd at databaseadvisors.com> >To: "Access Developers discussion and problem >solving"<accessd at databaseadvisors.com> >Subject: Re: [AccessD] Post Card Printing >Date: Thu, 9 Feb 2006 09:04:48 -0700 > > >Gustav, > >Any idea on this? > >-----Original Message----- >From: Gowey Mike W >Sent: Monday, February 06, 2006 11:01 AM >To: 'Access Developers discussion and problem solving' >Subject: RE: [AccessD] Post Card Printing > > >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 > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com