I need to consecutively number table rows with user determined start number

[bcmarshall]

I have an Access2007/SQL Server 2012 system with 20 users for an insurance company. I've been accused before (fairly) of being wordy in my descriptions, but it's the only way I think those I'm asking for help can fully understand what I'm doing.

The company does most of its business via a network of vehicle dealers around the country. If someone comes in to buy a motorcycle, boat, or recreational vehicle at a dealership they need insurance to take it home, and our dealers send the quotes to us.

The dealers, in turn, receive payment from us each month for their efforts. Some are paid a % commission on the premium, some are paid for each quote they send regardless of whether the policy actually sells or not, and some are paid a set amount per sold policy. (Yes, that is relevant information!)

We already have reports that tally the amounts due each dealer based on their payment scheme, but last month our bookkeeper had to write about 650 checks manually because the check writing is not automated. She'd look at the report, and then enter name, address, and amount (in digits and words) into Quick Books and print the checks from there, a horribly tedious process. I've been asked to print the checks from Access. Basically one click would print all 650 checks.

I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy. At the end of the day, one table contains all the information necessary to print the checks...except one.

The check number.

I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.

By the way, the check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.

I think the best way to accomplish this is from the report itself. I've created a blank field on each record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once. That way, should print ever be interrupted, it will be easy to take up where we left off.

All help will be greatly appreciated.

 

[vbaInet]

I've been accused before (fairly) of being wordy in my descriptions, but it's the only way I think those I'm asking for help can fully understand what I'm doing.

I can see why All of this information is what a Consultant or Developer will require. You're asking for a solution for a small problem, we don't really need all the background information about what the company does and what needs automating. To be honest, most times I tend to avoid long descriptions because time is precious but since I saw your last thread I thought I should respond to this one.

I've extracted the bits I think are somewhat relevant and put them in order:

I have an Access2007/SQL Server 2012 system with 20 users for an insurance company.

I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.

I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy.

Why do you think you need an extra table? There's a lot you can do in queries. Can you show us some sample data from the original table and tell us what field(s) you made into a new table.

 

[bcmarshall]

I've extracted the bits I think are somewhat relevant and put them in order:
Why do you think you need an extra table? There's a lot you can do in queries. Can you show us some sample data from the original table and tell us what field(s) you made into a new table.

The reason I need the extra table is because the queries that feed it (Make Table + 2 Appends) are all totals queries. The original record set has the individual line items, while the queries sum the totals for each dealer so that we have Dealer X and his total amount as one line item.

The information in the table is pretty minimal, just enough to write the check. Amount, payee, and address (we use window envelopes for mailing). And of course, the extra field, currently unpopulated, for check number.

Hope that's enough info to answer your question.

 

[vbaInet]

How are the sum totals data derived? Do you do a Totals query?

 

[bcmarshall]

How are the sum totals data derived? Do you do a Totals query?

Yes, the three queries are all totals queries.

 

[vbaInet]

Hardly is a Make Table query require; it's very rare. Can you show me the SQL the three queries and some sample data (with the field names) of the Make Table queries.

 

[bcmarshall]

Hardly is a Make Table query require; it's very rare. Can you show me the SQL the three queries and some sample data (with the field names) of the Make Table queries.

Make table SQL is:

SELECT Commissions.DealerID, Commissions.DealerName, Commissions.IndName, Sum(Commissions.AmtPaid) AS SumOfAmtPaid, "Commissions from " & [Forms]![BinderReport]![Start] & " to " & [Forms]![BinderReport]![End] AS [Memo], Commissions.Address, Commissions.DealerCSZip, Commissions.AltAdd, Commissions.Payee, Null AS CheckNumber INTO PrintChecks
FROM Commissions
GROUP BY Commissions.DealerID, Commissions.DealerName, Commissions.IndName, Commissions.Address, Commissions.DealerCSZip, Commissions.AltAdd, Commissions.Payee;

SQL for first Append Query is:

INSERT INTO PrintChecks ( DealerID, DealerName, IndName, SumOfAmtPaid, [Memo], Address, DealerCSZip, AltAdd, Payee, CheckNumber )
SELECT DlrQuotes.DealerID, DlrQuotes.DealerName, DlrQuotes.IndName, Sum(DlrQuotes.AmtPaid) AS SumOfAmtPaid, "Referral fees from " & [Forms]![BinderReport]![Start] & " to " & [Forms]![BinderReport]![End] AS [Memo], DlrQuotes.Address, DlrQuotes.DealerCSZip, DlrQuotes.AltAdd, DlrQuotes.Payee, Null AS CheckNumber
FROM DlrQuotes
GROUP BY DlrQuotes.DealerID, DlrQuotes.DealerName, DlrQuotes.IndName, DlrQuotes.Address, DlrQuotes.DealerCSZip, DlrQuotes.AltAdd, DlrQuotes.Payee;


SQL for second Append is:

INSERT INTO PrintChecks ( DealerID, DealerName, IndName, SumOfAmtPaid, [Memo], Address, DealerCSZip, AltAdd, Payee, CheckNumber )
SELECT DlrBinders.DealerID, DlrBinders.DealerName, DlrBinders.IndName, Sum(DlrBinders.AmtPaid) AS SumOfAmtPaid, "Referral fees from " & [Forms]![BinderReport]![Start] & " to " & [Forms]![BinderReport]![End] AS [Memo], DlrBinders.Address, DlrBinders.DealerCSZip, DlrBinders.AltAdd, DlrBinders.Payee, Null AS CheckNumber
FROM DlrBinders
GROUP BY DlrBinders.DealerID, DlrBinders.DealerName, DlrBinders.IndName, DlrBinders.Address, DlrBinders.DealerCSZip, DlrBinders.AltAdd, DlrBinders.Payee
HAVING (((DlrBinders.DealerName) Not Like "Cross Sell*" And (DlrBinders.DealerName) Not Like "AMOD*" And (DlrBinders.DealerName) Not Like "Referral*" And (DlrBinders.DealerName) Not Like "Unknown*" And (DlrBinders.DealerName) Not Like "Rewrite*"));

Sample Data is the same for all queries:

DealerID: 1234
DealerName: Joe's RV
IndName: Joe Smith
AmtPaid: $225.63
Memo: Referral fees from 6/1/2014 to 6/30/2014
Address: 1234 Biscayne Blvd.
DealerCSZip: Miami, FL 33125
AltAdd: 6014 Home St.
Ft. Lauderdale, FL 33333
Payee: Tom Jones
CheckNumber: TBD

 

[vbaInet]

Do you want us to address this make table issue by showing you a better way of doing things or do you only want to get the ordering done?

 

[bcmarshall]

I'm open to any and all suggestions. I appreciate any suggestion that may smooth the way.

 

[vbaInet]

Ok good.

I'm not sure why you have two tables with the same fields, it would normally indicate a data normalisation issue (but that's for another topic). You can have one table and an extra field that indicates whether it's a Quote or Binder.

In relation to this field "Sum(Commissions.AmtPaid) AS SumOfAmtPaid" from the Make Table query, it seems it's possible for a Dealer to be present in both DlrQuotes and DlrBinders tables?

 

[bcmarshall]

Ok good.

I'm not sure why you have two tables with the same fields, it would normally indicate a data normalisation issue (but that's for another topic). You can have one table and an extra field that indicates whether it's a Quote or Binder.

In relation to this field "Sum(Commissions.AmtPaid) AS SumOfAmtPaid" from the Make Table query, it seems it's possible for a Dealer to be present in both DlrQuotes and DlrBinders tables?

I tried, through my wordy introduction, to explain the reasoning behind this. We essentially have three categories of dealers. Some are paid a % of the policy amount, some are paid a fee per quote regardless of whether the policy is sold, and some are paid a set fee per policy. Because of their different payment structures the queries that are used to feed the Make Table and the two Append queries are each different subsets of the dealer population. There is no duplication, since each dealer has only one payment schedule.


Each query that segregates the data for a given payment schedule dealer is derived from the same record source, so we have one source and have divided it into three to feed the individual reports for that type of dealer. Since totals and all that relevant information is already done, I'm now recombining the three back into one table for printing checks.

I acknowledge that there may be other ways to accomplish this without dividing and recombining, but because of other considerations it makes more sense. The division will be required regardless, and recombining them makes it easier to make sure that all record sets are identical.

 

[vbaInet]

In essence DlrBinders and DlrQuotes both feed off the same table?

With regards the "SumOfAmtPaid" field just make sure that both queries are not going to pull overlapping records otherwise you'll be doubling the amount paid instead of summing it. Very important!

Have you heard of a Union Query? That's what you're going to use to replace the Make Table strategy. So (in a backup copy of your queries):

1. convert your two UPDATE queries to normal SELECT queries and remove the Totals part (i.e. remove the Sum()) from each of them.
2. Use a Union query to combine both SELECT queries
3. Create a query based on the Union query from step 2 and derive the SumOfAmtPaid field here.

Then we can work on the renumbering.

 

[bcmarshall]

In essence DlrBinders and DlrQuotes both feed off the same table?

With regards the "SumOfAmtPaid" field just make sure that both queries are not going to pull overlapping records otherwise you'll be doubling the amount paid instead of summing it. Very important!

Have you heard of a Union Query? That's what you're going to use to replace the Make Table strategy. So (in a backup copy of your queries):

1. convert your two UPDATE queries to normal SELECT queries and remove the Totals part (i.e. remove the Sum()) from each of them.
2. Use a Union query to combine both SELECT queries
3. Create a query based on the Union query from step 2 and derive the SumOfAmtPaid field here.

Then we can work on the renumbering.

Union Query has been created that combines all three recordsets into one. Works fine.

How do we approach numbering?

 

[vbaInet]

I'll need to see some test records so I can determine which fields can be used for the numbering. An excel file would suffice.

 

[vbaInet]

I was hoping for some fictitious data. It's probably best you edit your post and remove the Excel file if it contains real data.

Are you going to apply a sort on any of the fields? That would affect the approach I take.

 

[vbaInet]

You probably missed this...

Are you going to apply a sort on any of the fields? That would affect the approach I take.

 

[bcmarshall]

I was hoping for some fictitious data. It's probably best you edit your post and remove the Excel file if it contains real data.

Are you going to apply a sort on any of the fields? That would affect the approach I take.

I've already removed the file and replaced it with a greatly abbreviated version. I added a field called Schedule, with 1 being Commissions, 2 being policies, and 3 being quotes. I want to sort on that field first.

Second sort is Ascending on Dealer Name.

Third sort is Ascending on Ind. Name

 

[bcmarshall]

I can see that it's a difficult task to sort based on the data itself since it's sort of a jumble. Wouldn't it be easier to just create the check number on print and write it back to the already sorted recordset in the query?

In other words, rather than trying to generate the count on the records, generate it after the action has taken place, or while it is taking place.

 

[vbaInet]

I wasn't concerned about the length. I was just making sure that you were ok with publishing those names and addresses on here.

With all these sorts in place an SQL solution won't be possible. The only other two options are code and a report.

So with that in mind, two more questions:
1. Do you know you can sort it in a report?
2. Do you have a field for holding the rankings?

 

[bcmarshall]

Since you already have the data in question I'll just remove the post.

There are three reports currently in use, one for each payment schedule. They provide each dealer with a list of which customer the payment is for, as well as totals. So yes, within each existing report the sorting is already done, and I can do the same from within the new report that actually prints the checks. That's why I think the easiest and best solution is to generate the check number on print of each record and write it back to the source query.

It has the additional advantage of being easy to take up where we left off in the event print is interrupted. I'm just not sure about the best way to provide the starting point and the sequencing.