If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Roadblock in my design
I have designed a database that is functioning well. Now I found that it
needs to be changed for some expansion and I can't seem to find the solution, even though it seems like it should be really obvious. So, I am hoping someone can give me a suggestion to get my brain moving again. There are 2 tables, Invoices and InvoiceDetail, that appear in a form/subform format. The InvoiceID and InvoiceDetailID (primary keys) are both autonumbers and the Invoice form is set to sort Ascending so that the most recently input invoice is at the end of the list. There are 2 support tables, Customers, and Products, that are referenced form these as dropdowns. What I need to do is create a second database that is identical for use at another location. The data from the second copy, needs to import into the primary data table. The data from the Invoice and InvoiceDetails tables can be deleted from the secondary location after each import. The data in the 2 support tables will need to be kept reconcilled where they match on both ends. The only way these 2 database will be "linked" is by copying data onto a cd or jumpdrive and installing on the other computer. I am open to just about any suggestions. Thanks in advance, Beth |
#2
|
|||
|
|||
Beth,
There are a number of approaches that are taken to this type of sutuation, depending on circumstances. For example, it may be possible to set up the computer at the second location to use Windows XP Remote Desktop or PCAnywhere or Terminal Services or some such to directly access the database at the first location. In this case, you wouldn't have to change your database design at all, assuming you have a standard frontend/backend structure. Anoterh approach is to use the Replication/Synchronisation features built into Access. This would also require very little change to your design, although your AutoNumber fields would become Random rather than Incremental. However, if you have decided on the manual import method, the main design change needed would be the InvoiceID and InvoiceDetailID to change from Autonumber to Number data types, with some sort of routine to allocate these numbers for new records. Then you would need to decide whether you want a separate InvoiceID numbering system for each location, in which case you would need a new field in the Invoices table for Location (1 or 2), and the combination of InvoiceID and Location could be the primary key. Otherwise, you will need to use an Append Query and an Update Query when the data from the second location is imported, in order to assign new InvoiceID values to the imported Invoice records, and then update the InvoiceID foreign key field in the InvoiceDetail table to correspond with these values. -- Steve Schapel, Microsoft Access MVP Beth wrote: I have designed a database that is functioning well. Now I found that it needs to be changed for some expansion and I can't seem to find the solution, even though it seems like it should be really obvious. So, I am hoping someone can give me a suggestion to get my brain moving again. There are 2 tables, Invoices and InvoiceDetail, that appear in a form/subform format. The InvoiceID and InvoiceDetailID (primary keys) are both autonumbers and the Invoice form is set to sort Ascending so that the most recently input invoice is at the end of the list. There are 2 support tables, Customers, and Products, that are referenced form these as dropdowns. What I need to do is create a second database that is identical for use at another location. The data from the second copy, needs to import into the primary data table. The data from the Invoice and InvoiceDetails tables can be deleted from the secondary location after each import. The data in the 2 support tables will need to be kept reconcilled where they match on both ends. The only way these 2 database will be "linked" is by copying data onto a cd or jumpdrive and installing on the other computer. I am open to just about any suggestions. Thanks in advance, Beth |
#3
|
|||
|
|||
I agree w/ Steve.
Having an exact copy/duplicate at the new location is ideal in terms of simplicity - - and then all you have to do is be sure the new location & existing location do not generate the same key. Appending data from the new location will be quite easy. "Beth" wrote: I have designed a database that is functioning well. Now I found that it needs to be changed for some expansion and I can't seem to find the solution, even though it seems like it should be really obvious. So, I am hoping someone can give me a suggestion to get my brain moving again. There are 2 tables, Invoices and InvoiceDetail, that appear in a form/subform format. The InvoiceID and InvoiceDetailID (primary keys) are both autonumbers and the Invoice form is set to sort Ascending so that the most recently input invoice is at the end of the list. There are 2 support tables, Customers, and Products, that are referenced form these as dropdowns. What I need to do is create a second database that is identical for use at another location. The data from the second copy, needs to import into the primary data table. The data from the Invoice and InvoiceDetails tables can be deleted from the secondary location after each import. The data in the 2 support tables will need to be kept reconcilled where they match on both ends. The only way these 2 database will be "linked" is by copying data onto a cd or jumpdrive and installing on the other computer. I am open to just about any suggestions. Thanks in advance, Beth |
#4
|
|||
|
|||
Thank you! The manual import is the only possibility for their situation.
I like the idea of adding the field to designate the location and making the key off a concatenation of the invoice number and location. I really appreciate the help. Beth "Steve Schapel" wrote in message ... Beth, There are a number of approaches that are taken to this type of sutuation, depending on circumstances. For example, it may be possible to set up the computer at the second location to use Windows XP Remote Desktop or PCAnywhere or Terminal Services or some such to directly access the database at the first location. In this case, you wouldn't have to change your database design at all, assuming you have a standard frontend/backend structure. Anoterh approach is to use the Replication/Synchronisation features built into Access. This would also require very little change to your design, although your AutoNumber fields would become Random rather than Incremental. However, if you have decided on the manual import method, the main design change needed would be the InvoiceID and InvoiceDetailID to change from Autonumber to Number data types, with some sort of routine to allocate these numbers for new records. Then you would need to decide whether you want a separate InvoiceID numbering system for each location, in which case you would need a new field in the Invoices table for Location (1 or 2), and the combination of InvoiceID and Location could be the primary key. Otherwise, you will need to use an Append Query and an Update Query when the data from the second location is imported, in order to assign new InvoiceID values to the imported Invoice records, and then update the InvoiceID foreign key field in the InvoiceDetail table to correspond with these values. -- Steve Schapel, Microsoft Access MVP Beth wrote: I have designed a database that is functioning well. Now I found that it needs to be changed for some expansion and I can't seem to find the solution, even though it seems like it should be really obvious. So, I am hoping someone can give me a suggestion to get my brain moving again. There are 2 tables, Invoices and InvoiceDetail, that appear in a form/subform format. The InvoiceID and InvoiceDetailID (primary keys) are both autonumbers and the Invoice form is set to sort Ascending so that the most recently input invoice is at the end of the list. There are 2 support tables, Customers, and Products, that are referenced form these as dropdowns. What I need to do is create a second database that is identical for use at another location. The data from the second copy, needs to import into the primary data table. The data from the Invoice and InvoiceDetails tables can be deleted from the secondary location after each import. The data in the 2 support tables will need to be kept reconcilled where they match on both ends. The only way these 2 database will be "linked" is by copying data onto a cd or jumpdrive and installing on the other computer. I am open to just about any suggestions. Thanks in advance, Beth |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Who owns the copyright on graphic design layouts prepared in MS Wo | Karen | General Discussion | 4 | February 1st, 2005 07:01 AM |
How can I customise an entire design set | David | Publisher | 2 | November 12th, 2004 09:43 AM |
Action queries changing when reopened in design view | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 12:34 AM |
Document Starts in Design Mode every time I open it | Colin Higbie | General Discussion | 4 | June 14th, 2004 12:24 PM |
opening a document so it is NOT in design mode | Brad Pears | New Users | 1 | May 3rd, 2004 09:13 PM |