Document Numbers. Union Query.

Question

My company use 5 main documents. SalesInv, CreditorsInv, BankReceipt, BankPayment, Journals. I created a table for each, and formatted the Auto Number, my choice 3 letters and 6 numbers. I.e. INV000001, CDI000001, REC000001 etc. In a normal query it will show the document numbers correct. When I create a Union query it will only show the numbers, not the prefix in the format. Maybe when I create the queries to prepare for the Union query, there is a way?

Answer 1

My company use 5 main documents. SalesInv, CreditorsInv, BankReceipt, BankPayment, Journals. I created a table for each, and formatted the Auto Number, my choice 3 letters and 6 numbers. I.e. INV000001, CDI000001, REC000001 etc. In a normal query it will show the document numbers correct. When I create a Union query it will only show the numbers, not the prefix in the format. Maybe when I create the queries to prepare for the Union query, there is a way?

You understand that, by using autonumbers as the basis for identifiers that you will show users, you give up control over the numbers, and can pretty much guarantee that there will eventually be gaps in the number sequence that users may question?  Are you sure you want to do that?  You might do better to generate each ID number yourself using, code that adds 1 to the last number previously generated.

That said, given your current setup using autonumbers, there's a way to get what you want.  The autonumbers themselves, as stored in the tables, don't have any prefixes.  However, in any query that selects them from their tables, you can create a calculated field that tacks on the prefix and the right number of leading zeros.  So, for example a query that selects from table SalesInv, with autonumber field InvID, could have SQL like this:

    SELECT "INV" & Format([InvID],"000000") AS DocumentID FROM SalesInv

A union query might look something like this:

    SELECT "INV" & Format([InvID],"000000") AS DocumentID, OtherField FROM SalesInv
    UNION ALL
    SELECT "CDI" & Format([CreditorInvID],"000000") AS DocumentID, OtherField FROM CreditorsInv
    UNION ALL
    SELECT "REC" & Format([BankReceiptID],"000000") AS DocumentID, OtherField FROM BankReceipt

and so on.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

Thank you Dirk. I entered it exactly like you said in the Union query, but when I try to open the Union query it gives error message "Syntax error in FROM clause". Maybe you can help me, how to generate each ID using code.

Answer 3

Thank you Dirk. I entered it exactly like you said in the Union query, but when I try to open the Union query it gives error message "Syntax error in FROM clause". Maybe you can help me, how to generate each ID using code.

Could you please post the exact SQL (from SQL View) of the union query that gave you the error?  I'm not saying I couldn't have made a mistake in my "air SQL", or you may have adapted it incorrectly.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

Dont read it all. As you can see my Union query adds 14 files together, so on the first query I added just before UNION ALL. SELECT "BPM" & Format([BnkPmtID],"000000" AS DocumentID, Otherfield from t06BnkPmt
SELECT q06BnkPmt.PmtDte AS [Date], q06BnkPmt.BnkPmtID AS Document, q06BnkPmt.LdgAccNo, q06BnkPmt.Name, q06BnkPmt.Descr, q06BnkPmt.bpDebit AS Debit, q06BnkPmt.Credit, -[Credit] AS Balance
FROM q06BnkPmt
SELECT "BPM" & Format([BnkPmtID],"000000" AS DocumentID, Otherfield from t06BnkPmt
UNION ALL 
SELECT q06BnkPmtSub.PmtDte, q06BnkPmtSub.BnkPmt_ID, q06BnkPmtSub.LdgAccNo, q06BnkPmt.Name, q06BnkPmtSub.LdgAccDesc, q06BnkPmtSub.Debit, q06BnkPmtSub.Credit, q06BnkPmtSub.Debit AS Balance
FROM q06BnkPmtSub INNER JOIN q06BnkPmt ON q06BnkPmtSub.BnkPmt_ID = q06BnkPmt.BnkPmtID
UNION ALL
SELECT q06BnkPmtSub.PmtDte, q06BnkPmtSub.BnkPmt_ID, IIf([dtvat]>0,6101," ") AS LdgAccNo, q06BnkPmt.Name, q06BnkPmtSub.LdgAccDesc, q06BnkPmtSub.dtVAT, q06BnkPmtSub.Credit, q06BnkPmtSub.Debit AS Balance
FROM q06BnkPmtSub INNER JOIN q06BnkPmt ON q06BnkPmtSub.BnkPmt_ID = q06BnkPmt.BnkPmtID
UNION ALL
SELECT q06BnkRct.RctDate, q06BnkRct.BnkRctID, q06BnkRct.LdgAccNo, [EntityNme1] & ", " & [Name] AS Name1, q06BnkRct.Descr, q06BnkRct.Debit, q06BnkRct.brCredit, q06BnkRct.Debit AS Balance
FROM q06BnkRct
UNION ALL
SELECT q06BnkRctSub.RctDate, q06BnkRctSub.BnkRct_ID, q06BnkRctSub.LdgAccNo, q06BnkRctSub.EntityNme1, q06BnkRctSub.LdgAccDesc, q06BnkRctSub.brDebit, q06BnkRctSub.Credit, -[Credit] AS Balance
FROM q06BnkRctSub
UNION ALL
SELECT q06BnkRctSub.RctDate, q06BnkRctSub.BnkRct_ID, IIf([crVat]>0,6101," ") AS LdgAccNo, q06BnkRctSub.EntityNme1, q06BnkRctSub.LdgAccDesc, q06BnkRctSub.brDebit, q06BnkRctSub.crVat, -[crVat] AS balance
FROM q06BnkRctSub
UNION ALL
SELECT q06InvCrd.InvDte, q06InvCrd.InvCrdID, q06InvCrd.LdgAcc_ID, q06InvCrd.EntityNme1, q06InvCrd.Descr, q06InvCrd.icDebit, q06InvCrd.Credit, -[Credit] AS Balance
FROM q06InvCrd
UNION ALL
SELECT q06InvCrdSub.InvDte, q06InvCrdSub.InvCrd_ID, q06InvCrdSub.LdgAccNo, q06InvCrdSub.EntityNme1, q06InvCrdSub.LdgAccDesc, q06InvCrdSub.Debit, q06InvCrdSub.icCredit, q06InvCrdSub.Debit AS Balance
FROM q06InvCrdSub
UNION ALL
SELECT q06InvCrdSub.InvDte, q06InvCrdSub.InvCrd_ID, q06InvCrdSub.LdgAccNo, q06InvCrdSub.EntityNme1, q06InvCrdSub.LdgAccDesc, q06InvCrdSub.dtVat, q06InvCrdSub.icCredit, q06InvCrdSub.dtVat AS Balance
FROM q06InvCrdSub
UNION ALL
SELECT q06InvSal.InvDte, q06InvSal.InvSalID, q06InvSal.LdgAcc_ID, q06InvSal.EntityNme1, q06InvSal.description, q06InvSal.Debit, q06InvSal.isCredit, q06InvSal.Debit AS Balance
FROM q06InvSal
UNION ALL
SELECT q06InvSalSub.InvDte, q06InvSalSub.InvSal_ID, q06InvSalSub.LdgAccNo, q06InvSalSub.EntityNme1, q06InvSalSub.LdgAccDesc, q06InvSalSub.isDebit, q06InvSalSub.Credit, -[Credit] AS Balance
FROM q06InvSalSub
UNION ALL
SELECT q06InvSalSub.InvDte, q06InvSalSub.InvSal_ID, q06InvSalSub.VatCntrl, q06InvSalSub.EntityNme1, q06InvSalSub.LdgAccDesc, q06InvSalSub.isDebit, q06InvSalSub.crVat, -[crVat] AS Balance
FROM q06InvSalSub
UNION ALL
SELECT q06JournalsSub.JnlDte, q06JournalsSub.Jnl_ID, q06JournalsSub.LdgAccNo, q06JournalsSub.LdgAccDesc, q06JournalsSub.JnlDescr, q06JournalsSub.Debit, q06JournalsSub.Credit, [Debit]-[Credit] AS Balance
FROM q06JournalsSub
UNION ALL SELECT q06JournalsSub.JnlDte, q06JournalsSub.Jnl_ID, q06JournalsSub.LdgAccNo, q06JournalsSub.LdgAccDesc, q06JournalsSub.JnlDescr, q06JournalsSub.dtVat, q06JournalsSub.crVat, [dtVat]-[crVat] AS Balance
FROM q06JournalsSub;

Answer 5

Union queries must have the same number of fields, same datatype, and in same order.  

You need to remove -- "SELECT "BPM" & Format([BnkPmtID],"000000" AS DocumentID, Otherfield from t06BnkPmt"

from the query or match the rest of it field by field.

I tried color coding your fields based upon their names but several parts do not match ----

SELECT q06BnkPmt.PmtDte AS [Date], q06BnkPmt.BnkPmtID AS Document, q06BnkPmt.LdgAccNo, q06BnkPmt.Name, q06BnkPmt.Descr, q06BnkPmt.bpDebit AS Debit, q06BnkPmt.Credit, -[Credit] AS Balance

FROM q06BnkPmt

SELECT "BPM" & Format([BnkPmtID],"000000" AS DocumentID, Otherfield from t06BnkPmt

UNION ALL

SELECT q06BnkPmtSub.PmtDte, q06BnkPmtSub.BnkPmt_ID, q06BnkPmtSub.LdgAccNo, q06BnkPmt.Name, q06BnkPmtSub.LdgAccDesc, q06BnkPmtSub.Debit, q06BnkPmtSub.Credit, q06BnkPmtSub.Debit AS Balance

FROM q06BnkPmtSub INNER JOIN q06BnkPmt ON q06BnkPmtSub.BnkPmt_ID = q06BnkPmt.BnkPmtID

UNION ALL

SELECT q06BnkPmtSub.PmtDte, q06BnkPmtSub.BnkPmt_ID, IIf([dtvat]>0,6101," ") AS LdgAccNo, q06BnkPmt.Name, q06BnkPmtSub.LdgAccDesc, q06BnkPmtSub.dtVAT, q06BnkPmtSub.Credit, q06BnkPmtSub.Debit AS Balance

FROM q06BnkPmtSub INNER JOIN q06BnkPmt ON q06BnkPmtSub.BnkPmt_ID = q06BnkPmt.BnkPmtID

UNION ALL

SELECT q06BnkRct.RctDate, q06BnkRct.BnkRctID, q06BnkRct.LdgAccNo, [EntityNme1] & ", " & [Name] AS Name1, q06BnkRct.Descr, q06BnkRct.Debit, q06BnkRct.brCredit, q06BnkRct.Debit AS Balance

FROM q06BnkRct

UNION ALL

SELECT q06BnkRctSub.RctDate, q06BnkRctSub.BnkRct_ID, q06BnkRctSub.LdgAccNo, q06BnkRctSub.EntityNme1, q06BnkRctSub.LdgAccDesc, q06BnkRctSub.brDebit, q06BnkRctSub.Credit, -[Credit] AS Balance

FROM q06BnkRctSub

UNION ALL

SELECT q06BnkRctSub.RctDate, q06BnkRctSub.BnkRct_ID, IIf([crVat]>0,6101," ") AS LdgAccNo, q06BnkRctSub.EntityNme1, q06BnkRctSub.LdgAccDesc, q06BnkRctSub.brDebit, q06BnkRctSub.crVat, -[crVat] AS balance

FROM q06BnkRctSub

UNION ALL

SELECT q06InvCrd.InvDte, q06InvCrd.InvCrdID, q06InvCrd.LdgAcc_ID, q06InvCrd.EntityNme1, q06InvCrd.Descr, q06InvCrd.icDebit, q06InvCrd.Credit, -[Credit] AS Balance

FROM q06InvCrd

UNION ALL

SELECT q06InvCrdSub.InvDte, q06InvCrdSub.InvCrd_ID, q06InvCrdSub.LdgAccNo, q06InvCrdSub.EntityNme1, q06InvCrdSub.LdgAccDesc, q06InvCrdSub.Debit, q06InvCrdSub.icCredit, q06InvCrdSub.Debit AS Balance

FROM q06InvCrdSub

UNION ALL

SELECT q06InvCrdSub.InvDte, q06InvCrdSub.InvCrd_ID, q06InvCrdSub.LdgAccNo, q06InvCrdSub.EntityNme1, q06InvCrdSub.LdgAccDesc, q06InvCrdSub.dtVat, q06InvCrdSub.icCredit, q06InvCrdSub.dtVat AS Balance

FROM q06InvCrdSub

UNION ALL

SELECT q06InvSal.InvDte, q06InvSal.InvSalID, q06InvSal.LdgAcc_ID, q06InvSal.EntityNme1, q06InvSal.description, q06InvSal.Debit, q06InvSal.isCredit, q06InvSal.Debit AS Balance

FROM q06InvSal

UNION ALL

SELECT q06InvSalSub.InvDte, q06InvSalSub.InvSal_ID, q06InvSalSub.LdgAccNo, q06InvSalSub.EntityNme1, q06InvSalSub.LdgAccDesc, q06InvSalSub.isDebit, q06InvSalSub.Credit, -[Credit] AS Balance

FROM q06InvSalSub

UNION ALL

SELECT q06InvSalSub.InvDte, q06InvSalSub.InvSal_ID, q06InvSalSub.VatCntrl, q06InvSalSub.EntityNme1, q06InvSalSub.LdgAccDesc, q06InvSalSub.isDebit, q06InvSalSub.crVat, -[crVat] AS Balance

FROM q06InvSalSub

UNION ALL

SELECT q06JournalsSub.JnlDte, q06JournalsSub.Jnl_ID, q06JournalsSub.LdgAccNo, q06JournalsSub.LdgAccDesc, q06JournalsSub.JnlDescr, q06JournalsSub.Debit, q06JournalsSub.Credit, [Debit]-[Credit] AS Balance

FROM q06JournalsSub

UNION ALL SELECT q06JournalsSub.JnlDte, q06JournalsSub.Jnl_ID, q06JournalsSub.LdgAccNo, q06JournalsSub.LdgAccDesc, q06JournalsSub.JnlDescr, q06JournalsSub.dtVat, q06JournalsSub.crVat, [dtVat]-[crVat] AS Balance

FROM q06JournalsSub;

---

Build a little, test a little

Answer 6

Dirk and Karl.

I don't know if this reply will reach you. I am putting in every minute I am awake for 90 days, and the progress is good. But still can do few things through SQL. The videos spoonfeed me and it works most of the time. So if you are convinced. My Union query combines 14 queries(see above), and it works. I also got to hide zero through an "if" statement, but it gives an error if I use that field in an expression. If you still can help me with what you replied to get my document numbers showing. It probably doesn't matter whether I type the line you suggest anywhere in the Union query because you can see it is lengthy. 

Regards. Hans

Answer 7

I don't know if this reply will reach you. I am putting in every minute I am awake for 90 days, and the progress is good. But still can do few things through SQL. The videos spoonfeed me and it works most of the time. So if you are convinced. My Union query combines 14 queries(see above), and it works. I also got to hide zero through an "if" statement, but it gives an error if I use that field in an expression. If you still can help me with what you replied to get my document numbers showing. It probably doesn't matter whether I type the line you suggest anywhere in the Union query because you can see it is lengthy. 

Hi Hans,

When I read that you need the UNION of 14 queries, I scratched my ears. I am running now 90 different applications and I never have needed that must UNION's.

Most queries, if not all, are based on the 5 main documents. Are these tables? I wonder if you could base your final query directly on those documents.

I also made a couple of financial applications, but with only one table for all transactions. If my image of your application is right, then I think the structure of the 5 documents is (almost) the same. And in that case you can consoder then to combine them in one table, with a reference to one of the 5 Documenttypes.

Does it make sense?

Imb.

  

Answer 8

Hello Imb.  90 apps phew!

Yes the 5 documents have 5 tables, and each have its own kind of document numbers. Each causes a debit and a credit and a VAT inscription. My insight at this time leads me to Union 14 queries and it works. I am concerned that I chose to use the auto numbers with a prefix in each case, because auto numbers are not reliable.

When my wife was still alive we fixed those autonumbers through SQL when they went wrong. I have a video on creating numbers in code, and I need the spoon feed of a video to get it done. I wonder if the numbers then become reliable, and maybe creating numbers through code will solve my problem that finally on the report the whole number will show with the prefix. Each have a mainform and subform. The VAT ends up being on the subform in each case. 4 Mainform queries, 5Vat queries and 5 subform queries because JNL's dont have numbers on the main form. That is 14. According to GAAP(General Accepted Accounting Practice) there are many more documents in business, but I will bring it back to these 5.

My business have less admin challenges than a bakery or other manufacturing entities. I plan to develop a database for a bakery after this one is finished. But this one still have difficult things before it will be perfect.

Because I want 5 different document nos I don't have insight how to put them all in one table, and the videos taught me "MAIN FORMS" and "SUB FORMS" separate tables. I assume you create a field where you choose the document type, but do you create 5 separate number fields or put in document numbers manually , surely not.

You may call them categories, I call them entities. 7 Of them in my database (agents, employees, customers, vehicles, cash suppliers, creditors and "other sub ledger items".) I also have Prospects table, but the moment a prospect pays money he becomes a customer, and only then an entity. Before paying anything a prospect does not involve any figure work.

Ken Sheridan's advice on type and subtype helped me a long way to combine the 7 entities. I see it as a challenge that I don't want to duplicate one press of a button, or click of a mouse. If I do more thinking maybe the 5 documents can be brought together in a similar way, but I don't see that. The documents mostly have one line on the mainform, and a few lines on the subform.

I have 4 unanswered questions on this forum, and I try hard to solve them, trying to talk to anybody and everybody with experience and knowledge, but not so easy. My make shift solutions helps a bit, while I learn from videos, and other literature.

Answer 9

Hello Imb.  90 apps phew!

Yes the 5 documents have 5 tables, and each have its own kind of document numbers. Each causes a debit and a credit and a VAT inscription. My insight at this time leads me to Union 14 queries and it works. I am concerned that I chose to use the auto numbers with a prefix in each case, because auto numbers are not reliable.

Hi Hans,

Many points to discuss.

90 apps is not a problem. I have worked a long life on databases. In fact they go back to records with fields, and relations between the records. That part of the database I have automated, so that I only have to specify which fields and which relations, to make a complete application, but without reporting. And it makes no difference whether is it an accounting application, a (coin) collection application, an applications for running events, a genealogic application.

The basis of all this is generalization. And here lies the trigger for the interest in your application: I "feel" you can still gain a lot form generalization, based on the 14 queries to make a document overview.

I picked the above citation about the auto numbers. Auto numbers are perfectly reliable and suitable for making relations between records. In my opinion it is even the most efficient way to do. But you should not give them any human interpretation. What you need for the documents are unique numbers, and that is different from auto numbers. And it is not difficult to make a small VBA routine to generate these unique document numbers.

Unfortunately I can not see how your tables are defined. I only have Access2003 available, so it has no sense to send a link. But perhaps you can explain the fields in the document tables.

Imb.

Answer 10

If you say generalization, I understand that my 5 document tables looks almost the same as you said. Every table is a form and each one have a subform, so it is 10 tables, that is what the videos taught me.

I have to learn how to make a VBA routine to generate document no's. But after the videos trained me how to create a sales invoice, bank payment, bank receipt, creditors invoice, and Journals, I still have little insight how to have one table for them.

The documents work the same, so lets take the sales invoice.

a. I first create a "mainform"(table and query) with date and foreign key to ledger, so I can connect to the debtor.

b. I formatted the Primary key autonumber to be INV000001.

c. Our VAT is 14%. But lets say this invoice debits the debtor with R1140 for rent on vehicle on the main form. The subform will then keep similar info but credit VAT with R140 and Vehicle rent income with R1000.

d. For each one record in main form, it can be connected to many lines in the subform, like any invoice.

e. My accounting works on ledger accounts which of course must represent every transaction. Sub ledger accounts does not need to represent every transaction since they are there to give account of subdivisions of choice.

Surely you also use forms and subforms for invoices and other documents. With not enough experience my intuition does tell me all transactions can be kept in one table, and queries can be filtered to give me the same result I have now. I do feel that the videos can not be wrong all the time, or that there is a better way. But I do prefer one table. At his moment I understand what I do, but I will think and learn.

To Union 14 queries does take a bit of frustrating time, but also not too long. Somehow I feel I would have preferred one big table, but again, I understand main form and subform as various videos trained me. So tonight I guess, one table can be created, with one query, but still we will need 2 forms and two reports. Main report, and sub report to print an invoice. At least you got me thinking.

When you do a VBA routine on numbers, do you number different documents all in one field? Lets say INV000026, BPM000088. So if the next invoice gets processed that one same field will show INV000027, and if the next bankpayment comes up it will be BPM000089 in the same field. Numbering is possibly the main reason why I don't see one table.

You wrote you use one table for all transactions. How? All the one to many relations in my mind should have a separate table for the one's, and separate for the many's? If a business has got 4 different addresses, I still battle to keep 4 addresses as 4 records in the address table, and one in the Customer table. These addresses could be of type, Office addr, Postal addr, Billing addr, Delivery addr. The videos taught me to use address type, but I still do not know how to do it on a form so 4 records is kept in the address table. many things I still have to learn.

Answer 11

You wrote you use one table for all transactions. How? All the one to many relations in my mind should have a separate table for the one's, and separate for the many's? If a business has got 4 different addresses, I still battle to keep 4 addresses as 4 records in the address table, and one in the Customer table. These addresses could be of type, Office addr, Postal addr, Billing addr, Delivery addr. The videos taught me to use address type, but I still do not know how to do it on a form so 4 records is kept in the address table. many things I still have to learn.

Hi Hans,

Not to confuse things, but there are in general a couple of different ways to solve the problems that you meet in designing databases. What you learn from the videos is a good approach, I took a complete different strategy. "Your best practice" is what suits you the best in your environment.

Other ways mean other boundary conditions, and that is interesting to know. So I am not advising you, I only tell you how I see the things.

For any table I use an artificial key (Autonumber) as primary key. This is the fastest way to join to other foreign keys. Moreover, the value is otherwise meaningless, and that makes it easy to generalize relations. The invoice numbers, such as "INV000001" could then be in a separate field, eventually indexed as alternate or secundary key that forces unique values. You can even think of splitting the invoice numbers in two parts: one is the "INV"-part (for later referencing to the invoices)  and a sequence number. Sequence numbers are handy because you can simply increas them. In this latter case the combination of "INV" and the sequence number must be unique. The "formal" invoice number "INV000001" can Always be constructed from the two underlying parts.

About your last paragraph on the addresses. Do you keep one addres in the Customer table, and 4 addresses in the Address table? In a couple of applications I have the same issue. Translated to your situation do Customer table does not contain an address. All addresses, including the Address type, would be in the address table, Each records contains a Customer_id foreign key, pointing to the customer to which the addresses belong. In a couple of cases I added a boolean field to the address record, to indicate which of the different addresses is the default address (in fact I assume that this is the function of the address in the Customer table).

These were just a few topics. Overthink it and continue questioning.

Imb.

field as primary key.

Answer 12

Thanks, I appreciate your time, I thought I might frustrate you by now. In the 90 days since I started, I learned that even the experienced guys make choices for their own solutions of course. I will use "make shift solutions", until I learn the correct way. My wife is not here, my 4 kids are adults, my two best friends moved to Australia. My social life and best friend is now Access, smile. Until I am good at it, I enjoy it.

Your last writing maybe the solution for my Document number issue, I will test that just now. In the query I will create a calculated field where I concatenate "INV" with the autonumber even if I don't have the Zero's. Maybe the Union query will then show the document numbers the way I want it to. Rather INV55, than 55. I would have liked INV000055, but 55 will not do it on my reports, INV55 is good enough.

No addresses in the customer table. I have separate tables for "street address" and "postal address". It works right, but something is wrong, I want one address table. Most businesses will have a different Street postal Code than their Postal Office Code. Streetaddresstable have a foreign key to postal codes table, and postal codes table have a foreign key to province table(SA have 9 provinces)(3 towns with same name???). Same; Postaladdresstable also has a foreign key to postal codes table. In a rare case the physical address may be in a different province than the postal address. That gives me a challenge not solved yet, on province. I also have two separate tables for postal codes. Street Codes, and PostOffice codes. It works but I want it to be in one table.

If you may understand me. When I get to the form for input, and I refer to the same PostalCode table twice, one for streetpostalcode, and the other for Postalofficecode, Access will clash, or am I confused? I was probably a bit tired watching that video, have to watch it again. The video advised to create a small table "addresstype", so addresstype is not in the addresstable. I am sure the solution lies there, but I am long past that, but have to go back.

I added a check box, which should be checked if postal address is the same as physical address, that should be common to you. To me it stays a challenge that no information is ever stored twice. 3 Years ago my wife programmed, when you tick the checkbox it copies the physical address and stores it again as another record. If ever we do mail or an address list I want the checkbox to be the reason why the address will show on a list of all addresses. I still have to learn exactly how. 

Customers rent our cars, and they must give information of at least two relatives. Again, my relatives Table store 2 relatives in one long line(record). It should be stored as two records, two lines. The result is good as I did it, but I want to learn how to do it right.

Hope you understand me.

Answer 13

The sun is just coming up and my document numbers works OK, so I'm happy.