Hello all!

I have a database that needs to post records that are joined from three different sources into a table. The query is done, and I get about 1,489 records out in 4 different states.

What I need to do is make a table with these records. Furthermore, it must be separated by state, whereas if there are less than 1000 records for each state, it must insert blank lines until it reaches then next thousandth (sp?) row (i.e. 1001, 2001, etc.), and then start posting the next state.

For example, AZ has 420 records. There has to be 580 blank lines before the query can start posting the next state, CA. At row 1001, CA starts posting, but there are only 200 records for CA so there must be another 800 blank lines before moving on to CO at row 2001, etc. etc.

Anyone have any ideas on this? Thanks!

This is not how relational databases work. You are attempting to create a "flat" file structure where row order has meaning. Row order in a table has no meaning so adding empty records at a particular point has no meaning. As soon as a recordset is created from the table, the row order will not necessarily be what you expect unless you specifically order it on a unique identifier.

What is the purpose of this? Surely there is some other solution.

Pat is absolutely right. I'll add a little bit, though, just to pound it home. And I'll add something else just so you won't feel totally whipped.

Access is designed to be SPARSE. That is, if there is nothing to say, Access doesn't say it. If there is no data to store, then Access doesn't store it. Later, Access makes no "assumptions" about the presence or absence of data. It just works on whatever is there. Your design, however, appears to make an assumption that is inconsistent with the reality that you can see. So your "gaps" normally would not be stored.

Having said that, there is still a way to do what you want, but doing it implies that the number you are trying to generate has meaning. Further, that it has a SUBSTRUCTURED meaning. Otherwise, the range of the number would not matter. And therein lies the problem. Also, therein lies the answer to Pat's question about the purpose.

If you really wanted numbers to be defined in a particular way, put a separate table into your DB and have it show the "State offset" - So if you have CA, its offset might be 1000, while CO has 2000 and AZ has 0.

Now you can do an expression something like this (look up the functions in Help to get the syntax right.)

Next-number = 1 + NZ( DMax( "[thenumber]", "thetable", "[State]=""" & thestate & """" ), DLookup( "[thebasenumber"], "thebasenumbertable", "[State]=""" & thestate & """" )

Or you could just learn to love Access for its other strengths. :)

Now I have a question for you. If the things represented by these records can change over time, will it ever happen that you have more than 1000 records for a given state? If so, what will that do to your numbering scheme? Can you accept gaps within a state such as might occur if you have to delete a record? Will you have to try to re-use record numbers? This design, as Pat points out, smells a bit like three-day-old fish. It is not a case that Access couldn't do it, but you sure as heck had better know WHY you are doing something in order to justify the work you will have to do to get it like you want it.

Thanks Pat, Doc. You guys are right. I know that Access doesn't care what order my records are in and that if it's blank, there's no sense in storing it. To be more specific, my end result needs to go to an Excel template, where the rows are specifically spaced by the thousands. We're matching up data from 5 different sources and then putting it out to the Excel template, so what I'm attempting to do is produce an end result.

Now I have a question for you. If the things represented by these records can change over time, will it ever happen that you have more than 1000 records for a given state? If so, what will that do to your numbering scheme? Can you accept gaps within a state such as might occur if you have to delete a record? Will you have to try to re-use record numbers? This design, as Pat points out, smells a bit like three-day-old fish. It is not a case that Access couldn't do it, but you sure as heck had better know WHY you are doing something in order to justify the work you will have to do to get it like you want it.

Yes, some will go over a thousand. I would be looking to create logic to recognize whether TOTAL AZ is 1000, 2000, 3000, 4000 etc. I wouldn't be adding or removing any records after this record sorting, because this is an end result to export to a much larger process in Excel.

Also, a side note, I realize that this is a very sloppy way of processing data; using Excel with pre-determined formula references would not be my first choice. This is an instance where I have to work around another group's design until I redesign the entire process myself to eliminate this need for thousand records separation. However, given that this Excel template is so large (between 30 and 50 megs) that it will crash the program regularly, it will be a huge undertaking and I need to get this done right now in the interim.

Thanks again, guys!!

So, any other ideas, guys?

Since putting the empty rows in a table doesn't make any sense, why not use Excel to create them? One method would be to automate the export so you can direct each section to a specific named range of cells. When using the TransferText Method/Action you cannot specify a cell range. That argument only works on import. That's why you need to use automation especially if you need to deal with a situation where a group may run over a thousand.

If this is a one time process, it almost doesn't matter what you do. If it is a recurring problem, I would fix the Excel sheet to avoid the problem or do the calculations in Access rather than excel. Quick and dirty is almost never quick but it is always dirty.