Create Records Sequence # Start and End Values

[LONeill13]

Hello

It's been a while since I've worked in Access. I have a query based off a alignment table that shows the following data:

Acct# - Acct Name - Rep - Mth/Start - Mth/End
00001 XYZ Acct Rep1 1 4
00001 XYZ Acct Rep 2 5 12

What I'm trying to do is create "Table Maker Query" that would include the Acct # Acct Name and Rep from Query 1, but then would create a Mth Sequence field based on Mth/Start and Mth/End. For example Rep 1 would have 4 records for this account with Mth Sequence of 1, 2, 3, and 4, and Rep 2 would have 8 records for this account with Mth Sequence of 5,6,7,8,9,10,11,12 (see example below). I'm struggling with how to create the Mth Sequence

Acct# - Acct Name - Rep - Mth Squence
00001 XYZ Acct Rep1 1
00001 XYZ Acct Rep1 2
00001 XYZ Acct Rep1 3
00001 XYZ Acct Rep1 4
00001 XYZ Acct Rep2 5
00001 XYZ Acct Rep2 6
00001 XYZ Acct Rep2 7
00001 XYZ Acct Rep2 8 ...etc...

 

[Micron]

My brain seems to be wired for code as I sometimes propose code solutions and others chime in afterwards with neat query tricks. This may be one of those times.

I'm thinking that one recordset based on your first query provides the first 3 fields of data, which are assigned to variables. A value for the number of months between start/end provides the counter for a loop. The variable values are written to a second recordset along with the counter value, which gets written to the MthSequence field in each record in this second recordset. How to control when to move to a new record in the first recordset would be based on your decision as to what constitutes such a grouping. That is, at the beginning when the first Rep is known, is it Do While Rep = Rep1, then Move Next when it is not? Or is it the account? Based on your post, I'd say it was Rep. When the dust settles, you then have options as to where to put these records. Creating a table from the second recordset is doable, but I'd stay away from that for anything beyond a one-time use. Appending the records to a table might be the best bet.

If there's a simple query solution, I'm too tired to see it by now!

 

[LONeill13]

Thanks Micron. I was hoping there would be a non code way to accomplish this. I'm getting better with code in Excel, but never attempted code in Access and wouldn't even know where to start. I'll keep scouring the web to see if I can find something. Hope you got some rest last nite~

 

[Micron]

I could probably help with access code solution, but maybe the best idea would be to wait a bit in case someone has a simpler idea, unless you are very pressed for time. It would likely take me a day or two, allowing for time to get the details from you.

 

[LONeill13]

Thanks, Micron. I'll keep looking in the meantime. Have a great weekend

 

[LONeill13]

Hey Micron:
I found this tidbit of code which I believe might be able to be modified to suit my needs sql - Increment and record creation based on field value - Stack Overflow. I just don't know where I'd put this code. Any suggestions?

Here is my modification:

[TABLE="width: 64"]
 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl64, width: 64"]SELECT Qry_CorpAcctRep.[ID], [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]       Qry_CorpAcctRep.[MaxMth], [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]       MthSeq.MthSeq AS [MthVal] INTO NewQry_CorpAcctRep[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]FROM  Qry_CorpAcctRep, MthSeq[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]WHERE  Numbers.Number Between [MinMth] And [MaxMth][/TD]
[/TR]
</tbody>[/TABLE]

 

[LONeill13]

Micron....I figured it out!!!!! I had to go to SQL view of my query and append the code above to my SQL....I also realized that I forgot to modify the WHERE part of the code above to my data. But once I fixed it and ran the query it worked perfectly....

 

[Micron]

That is not only excellent that you found a solution, but that you shared it with us is even more so. I KNEW there had to be a simpler solution than defaulting to code; I was just too tired to think of any, given that it was after 2:00 AM. Of course, it was a more reasonable hour during my second post, but I elected to wait upon your research.

What you have there is a solution based on a Cartesian Product (albeit a filtered one because of the query criteria) which is what you'll always get if you involve two or more tables without joining them. Simple, but elegant. The only caution I'd raise is that the solution posted at the other site uses at least one reserved word (Number) and so have you - but you should not. Bookmark this site:
Microsoft Access tips: Problem names and reserved words in Access
Here is what I tweaked when I implemented the other site's solution and saw the result:

SELECT samples.[Sample ID], Numbers.Nums AS [Jar Number] INTO NewSamples
FROM samples, Numbers WHERE (((Numbers.Nums)<=[Number of Jars]))
ORDER BY samples.[Sample ID], Numbers.Nums;

You might notice I eliminated the number of jars field as it didn't seem important to repeat it in the new table. I also implemented a sort order. The only thing I don't like about the solution is that it creates a table and will do so each time. If you continually overwrite objects, it can cause db corruption, or at least significant db bloat. I would either run this query once, then open the new table in design view and either create a composite index using SampleID and JarNumber or a composite primary key, OR simply design a table from scratch and do the same. Some say never to create a composite key, but I've never had any issues doing so and don't know what the objection is. After doing so, I'd change the query sql to

INSERT INTO NewSamples ( [Sample ID], [Jar Number] )
SELECT samples.[Sample ID], Numbers.Nums AS [Jar Number]
FROM samples, Numbers
WHERE (((Numbers.Nums)<=[Number of Jars]))
ORDER BY samples.[Sample ID], Numbers.Nums;

so that it appends new records to the same table. If you have created the composite index or PK, you will get a warning about not being able to append all the duplicates (because the query doesn't discriminate between what is new and what is not), you'd want to suppress that message unless you were only going to be the user of the db. This could be done in either of 2 ways:
1) the button click event that drives this has to handle the warnings one of two ways (so a wee bit of code would be required). Warnings are either turned off then back on again at the end of the query execution, OR the db.Execute method is used to run the query
2) the sql could be modified to attempt to prevent duplicate records from being appended. This seems to work:

INSERT INTO NewSamples ( [Sample ID], [Jar Number] )
SELECT DISTINCT samples.[Sample ID], Numbers.Nums AS [Jar Number]
FROM Numbers, NewSamples INNER JOIN samples ON NewSamples.[Sample ID] = samples.[Sample ID]
WHERE (((Numbers.Nums)<=[Number of Jars] And [Numbers].[Nums]<>[NewSamples].[Jar Number]))
ORDER BY samples.[Sample ID], Numbers.Nums;

Notice that this solution requires the target table to be part of the query, as well as I'm using the DISTINCT predicate. Go to the query in design view and on the property sheet, look for Unique Values and Unique Records and read your help file on these attributes. Without this setting, the query will return some duplicate rows for each sample ID.
P.S.: I almost never have spaces in my object names, but I ended up with some here by way of copying someone else's sql statement.