multiple branch primary key conflict

[soul786]

I made basic invoice app. As long as i used it for 1 store it worked fine. It is ladies garment store. Problem started with 2nd branch. I want to see data of both branches in 1 main computer. I tried export import through email and not persistent online connection. The problem is that sales id, invoice id etc primary keys r autonumbers so have same values per branch. If i try to import i cant because pk values r same.


if somehow i can add branch code in all those ids i hope the problem will solve. Because that will make it unique even between branches. I dont know how. Please advice a no coding solution. I dont know programming.

[ItsMe]

Before you do anything, make copies of your databases.

Rather than using AutoNumber with Long Integer field size, you might get what you want using Autonumber with Replication ID field size. You can make the adjustment in design view of your tables. However, you will need to do some importing of data. Depending on how your DB is set up, you might be able to use Relationships and Referential integrity rules to assist the process.

[soul786]

Before you do anything, make copies of your databases.

Rather than using AutoNumber with Long Integer field size, you might get what you want using Autonumber with Replication ID field size. You can make the adjustment in design view of your tables. However, you will need to do some importing of data. Depending on how your DB is set up, you might be able to use Relationships and Referential integrity rules to assist the process.

hi, thanks
replication id may do the job, but it is not good choice, first it consumes lots of memory, 2nd it is not meaningful for humans etc.
what i want is like i have branches RB and KB
sales, invoice IDs etc. of say KB is
KB-1, KB-2 and so on, it is meaningful and consumes less resources
this is not possible with autonumber,
i tried format option, but it just shows prefix KB along with autonumber but not actually saves it

[ItsMe]

You lost me after, "first it consumes lots of memory...". Perhaps your best bet is to hire a consultant. I say this because you mentioned that you do not understand programming.

[thuzkee02]

I encountered the same problem on my project. I think the problem there is that when you call 2 or more primary keys from different Tables and use it on your query, it automatically adopts the properties of the fields themselves.

What I did was declare the property of the field on the query itself: SELECT tableA.ID, CLNG(TableB.ID) AS ID_b or something to this effect. It worked for me.

Hope it works for you!

[CJ_London]

sounds like you are trying to use the autonumber as a meaningful value (an invoice number) - which is a big no no. All an autonumber does is identify a record, no more, no less and should not be used for anything meaningful - which is why the replicationID is an acceptable alternative for your situation

[soul786]

thanks all
i found a solution
i used composite primary key, also i can use
sequential numbering with prefixes so i get auto generated numbers as a1,a2,a3 where prefix is a

see my solution for forms
1.
me.control (for respective field name) = nz(DMax("field name", "table name"), starting value) + 1
put this code in form's design view, in form's event property before insert
this works even if the field is text field
2.
me.control (for respective field name) = "string" & nz(mid(DMax("field name", "table name"),starting place), starting value) + 1
string = anything text
for mid function example, if field = a9, starting place = 2, answer is 9

[soul786]

friends
pls advice whether to use composite key method or sequential numbering method
composite key method consumes much more memory as more columns r required, but is easier
sequential numbering needs coding for every front end and again if say business expands in different states then along with branch code, there must be state code
for many tables like sales, sales returns, purchase, purchase returns, expenses, employees etc all must contain prefix as per different branches.
front end for every branch must be edited with different prefix
please advice any other method or suggestion or help me choose between these 2
and now i dont mind writing or using vba

[CJ_London]

personally I would go for two/three separate columns - one to identify the branch, one to determine an incremental number and a third to determine document type