tweaking query results prior to export

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a query that generates address information I can export as a CSV
file. That info gets printed on a label and stuck on a magazine. The problem
is that some addresses get more than one copy of the magazine. What I'd like
to do is parse the query results and add "duplicate" records for those
addresses that are supposed to get more than one copy. I have a "number of
copies" field included in the query. What's the best way to proceed?
 
Stevewoo said:
I've got a query that generates address information I can export as a CSV
file. That info gets printed on a label and stuck on a magazine. The
problem
is that some addresses get more than one copy of the magazine. What I'd
like
to do is parse the query results and add "duplicate" records for those
addresses that are supposed to get more than one copy. I have a "number of
copies" field included in the query. What's the best way to proceed?
Click to expand...

Provided the number of copies is within a reasonable range, an easy way
would be to define a 'helper table' which you link in a query. If you
called this tblNumber, it would look like this:

N1 N2
1 1
2 1
2 2
3 1
3 2
3 3
<..etc>

See the pattern? Now if you create a query with a join between
tblAddress.CopyCount and tblNumber.N1, then you will automatically get the
right count.

There are other ways you could try, but this is solution does not require
any vba coding.
 
Brian Wilson said:
Provided the number of copies is within a reasonable range, an easy way
would be to define a 'helper table' which you link in a query. If you
called this tblNumber, it would look like this:

N1 N2
1 1
2 1
2 2
3 1
3 2
3 3
<..etc>

See the pattern? Now if you create a query with a join between
tblAddress.CopyCount and tblNumber.N1, then you will automatically get the
right count.

There are other ways you could try, but this is solution does not require
any vba coding.
Click to expand...

Brian - Thank you! I see the pattern and set up my query as you described -
and it works. Danged if I can figure out why...
 
Stevewoo said:
Brian - Thank you! I see the pattern and set up my query as you
described -
and it works. Danged if I can figure out why...
Click to expand...

Glad to hear it worked - the explanation is quite simple:
If tblAddress.CopyCount=1, there is 1 matching record in tblNumber so 1
record is returned for this address in the query. If
tblAddress.CopyCount=2, there are 2 matching records in tblNumber so 2
addresss records are returned in the query, etc.
 
Back
Top