Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a primary key
So I'm doing something for a client that probably isn't the best way, but the
client insists to have it that way. The client wants to work primarily out of an excel spreadsheet then periodically export the data to an access database that includes extra information about the records. The problem I'm having is transferring them from excel to access since I obviously need a key to uniquely identify each record. Since the client is working in excel the primary key has to be generated in excel and be transfered to access later. I can't figure out a reliable way to generate an automatic identifier on the fly in excel. I've found a lot of forums posts with the same problem but don't seem to completely understand any of the solutions. http://www.mcgimpsey.com/excel/udfs/sequentialnums.html I have seen the previous link come up many times but I don't know if it's the best implementation for my project. The excel spreadsheet is stored on a shared network drive if the affect the solution to the problem. Thanks for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a primary key
Could you perhaps setup the application in Access first, use the autonumber
field for the key, then periodically use the Access tools menu | Office Links | Analyze it with MS Office Excel to export the data into Excel. If its a primary key you want in Access, this might be the best way. "Access::Student" wrote: So I'm doing something for a client that probably isn't the best way, but the client insists to have it that way. The client wants to work primarily out of an excel spreadsheet then periodically export the data to an access database that includes extra information about the records. The problem I'm having is transferring them from excel to access since I obviously need a key to uniquely identify each record. Since the client is working in excel the primary key has to be generated in excel and be transfered to access later. I can't figure out a reliable way to generate an automatic identifier on the fly in excel. I've found a lot of forums posts with the same problem but don't seem to completely understand any of the solutions. http://www.mcgimpsey.com/excel/udfs/sequentialnums.html I have seen the previous link come up many times but I don't know if it's the best implementation for my project. The excel spreadsheet is stored on a shared network drive if the affect the solution to the problem. Thanks for any help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a primary key
Thanks for the reply.
I understand that solution but the problem is that the work and generation of content will take place in excel. It involves tons of numbers and large formulas that are easier to change and work with in excel. So storing data in access and exporting it would not be useful as all the formulas and calculations would be lost in translation. The access database is really only to store extra information about the records that can't be stored in the flat excel file. But by transferring the information from the excel spreadsheet to it it makes everything come together very nicely. So all I need is a way to periodically upload or link the excel to access. But to do either I need some primary key to identify the records, and the key must be generated in excel because that's were any additions to the data will take place. "Anthony Fontana" wrote: Could you perhaps setup the application in Access first, use the autonumber field for the key, then periodically use the Access tools menu | Office Links | Analyze it with MS Office Excel to export the data into Excel. If its a primary key you want in Access, this might be the best way. "Access::Student" wrote: So I'm doing something for a client that probably isn't the best way, but the client insists to have it that way. The client wants to work primarily out of an excel spreadsheet then periodically export the data to an access database that includes extra information about the records. The problem I'm having is transferring them from excel to access since I obviously need a key to uniquely identify each record. Since the client is working in excel the primary key has to be generated in excel and be transfered to access later. I can't figure out a reliable way to generate an automatic identifier on the fly in excel. I've found a lot of forums posts with the same problem but don't seem to completely understand any of the solutions. http://www.mcgimpsey.com/excel/udfs/sequentialnums.html I have seen the previous link come up many times but I don't know if it's the best implementation for my project. The excel spreadsheet is stored on a shared network drive if the affect the solution to the problem. Thanks for any help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a primary key
Hi
In cell A2 enter a formula like =IF(B2="","","Key"&TEXT(ROW(),"00000")) and copy down Before copying to Access, select the range of cells in column A for the number of rows to be copiedCopyPaste SpecialValues -- Regards Roger Govier "Access::Student" wrote in message ... So I'm doing something for a client that probably isn't the best way, but the client insists to have it that way. The client wants to work primarily out of an excel spreadsheet then periodically export the data to an access database that includes extra information about the records. The problem I'm having is transferring them from excel to access since I obviously need a key to uniquely identify each record. Since the client is working in excel the primary key has to be generated in excel and be transfered to access later. I can't figure out a reliable way to generate an automatic identifier on the fly in excel. I've found a lot of forums posts with the same problem but don't seem to completely understand any of the solutions. http://www.mcgimpsey.com/excel/udfs/sequentialnums.html I have seen the previous link come up many times but I don't know if it's the best implementation for my project. The excel spreadsheet is stored on a shared network drive if the affect the solution to the problem. Thanks for any help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a primary key
Hey, thanks for the reply.
This solution won't work for me either, since if a new record is inserted somewhere in the middle of the old ones (which it could), all the key would be off and the whole purpose of it would be lost. What I need is a key that has uniqueness enforced and will not change after it is created. "Roger Govier" wrote: Hi In cell A2 enter a formula like =IF(B2="","","Key"&TEXT(ROW(),"00000")) and copy down Before copying to Access, select the range of cells in column A for the number of rows to be copiedCopyPaste SpecialValues -- Regards Roger Govier "Access::Student" wrote in message ... So I'm doing something for a client that probably isn't the best way, but the client insists to have it that way. The client wants to work primarily out of an excel spreadsheet then periodically export the data to an access database that includes extra information about the records. The problem I'm having is transferring them from excel to access since I obviously need a key to uniquely identify each record. Since the client is working in excel the primary key has to be generated in excel and be transfered to access later. I can't figure out a reliable way to generate an automatic identifier on the fly in excel. I've found a lot of forums posts with the same problem but don't seem to completely understand any of the solutions. http://www.mcgimpsey.com/excel/udfs/sequentialnums.html I have seen the previous link come up many times but I don't know if it's the best implementation for my project. The excel spreadsheet is stored on a shared network drive if the affect the solution to the problem. Thanks for any help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a primary key
Hi,
Get the best of both worlds. If the data in Excel is in a named range, the easiest way to make this happen is to open Access and do an import of the data from Excel. *** You have the option to import or LINK to the Excel data. **** You can do the auto number in Access, and every time the Access database is opened, it will automatically update to the linked table in Excel. John "Access::Student" wrote in message ... Thanks for the reply. I understand that solution but the problem is that the work and generation of content will take place in excel. It involves tons of numbers and large formulas that are easier to change and work with in excel. So storing data in access and exporting it would not be useful as all the formulas and calculations would be lost in translation. The access database is really only to store extra information about the records that can't be stored in the flat excel file. But by transferring the information from the excel spreadsheet to it it makes everything come together very nicely. So all I need is a way to periodically upload or link the excel to access. But to do either I need some primary key to identify the records, and the key must be generated in excel because that's were any additions to the data will take place. "Anthony Fontana" wrote: Could you perhaps setup the application in Access first, use the autonumber field for the key, then periodically use the Access tools menu | Office Links | Analyze it with MS Office Excel to export the data into Excel. If its a primary key you want in Access, this might be the best way. "Access::Student" wrote: So I'm doing something for a client that probably isn't the best way, but the client insists to have it that way. The client wants to work primarily out of an excel spreadsheet then periodically export the data to an access database that includes extra information about the records. The problem I'm having is transferring them from excel to access since I obviously need a key to uniquely identify each record. Since the client is working in excel the primary key has to be generated in excel and be transfered to access later. I can't figure out a reliable way to generate an automatic identifier on the fly in excel. I've found a lot of forums posts with the same problem but don't seem to completely understand any of the solutions. http://www.mcgimpsey.com/excel/udfs/sequentialnums.html I have seen the previous link come up many times but I don't know if it's the best implementation for my project. The excel spreadsheet is stored on a shared network drive if the affect the solution to the problem. Thanks for any help. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a primary key
Hello,
I am not sure what you mean by that. When I link an excel table in access I get an un-editable table. I can't add any fields into the table or add information. What can I do to add an auto number field and have it push the result back to excel? "jaf" wrote: Hi, Get the best of both worlds. If the data in Excel is in a named range, the easiest way to make this happen is to open Access and do an import of the data from Excel. *** You have the option to import or LINK to the Excel data. **** You can do the auto number in Access, and every time the Access database is opened, it will automatically update to the linked table in Excel. John "Access::Student" wrote in message ... Thanks for the reply. I understand that solution but the problem is that the work and generation of content will take place in excel. It involves tons of numbers and large formulas that are easier to change and work with in excel. So storing data in access and exporting it would not be useful as all the formulas and calculations would be lost in translation. The access database is really only to store extra information about the records that can't be stored in the flat excel file. But by transferring the information from the excel spreadsheet to it it makes everything come together very nicely. So all I need is a way to periodically upload or link the excel to access. But to do either I need some primary key to identify the records, and the key must be generated in excel because that's were any additions to the data will take place. "Anthony Fontana" wrote: Could you perhaps setup the application in Access first, use the autonumber field for the key, then periodically use the Access tools menu | Office Links | Analyze it with MS Office Excel to export the data into Excel. If its a primary key you want in Access, this might be the best way. "Access::Student" wrote: So I'm doing something for a client that probably isn't the best way, but the client insists to have it that way. The client wants to work primarily out of an excel spreadsheet then periodically export the data to an access database that includes extra information about the records. The problem I'm having is transferring them from excel to access since I obviously need a key to uniquely identify each record. Since the client is working in excel the primary key has to be generated in excel and be transfered to access later. I can't figure out a reliable way to generate an automatic identifier on the fly in excel. I've found a lot of forums posts with the same problem but don't seem to completely understand any of the solutions. http://www.mcgimpsey.com/excel/udfs/sequentialnums.html I have seen the previous link come up many times but I don't know if it's the best implementation for my project. The excel spreadsheet is stored on a shared network drive if the affect the solution to the problem. Thanks for any help. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a primary key
Hi
Then a small piece of VBA code to copy the formula down the range, copy and paste special before exporting to Access should do the job. -- Regards Roger Govier "Access::Student" wrote in message ... Hey, thanks for the reply. This solution won't work for me either, since if a new record is inserted somewhere in the middle of the old ones (which it could), all the key would be off and the whole purpose of it would be lost. What I need is a key that has uniqueness enforced and will not change after it is created. "Roger Govier" wrote: Hi In cell A2 enter a formula like =IF(B2="","","Key"&TEXT(ROW(),"00000")) and copy down Before copying to Access, select the range of cells in column A for the number of rows to be copiedCopyPaste SpecialValues -- Regards Roger Govier "Access::Student" wrote in message ... So I'm doing something for a client that probably isn't the best way, but the client insists to have it that way. The client wants to work primarily out of an excel spreadsheet then periodically export the data to an access database that includes extra information about the records. The problem I'm having is transferring them from excel to access since I obviously need a key to uniquely identify each record. Since the client is working in excel the primary key has to be generated in excel and be transfered to access later. I can't figure out a reliable way to generate an automatic identifier on the fly in excel. I've found a lot of forums posts with the same problem but don't seem to completely understand any of the solutions. http://www.mcgimpsey.com/excel/udfs/sequentialnums.html I have seen the previous link come up many times but I don't know if it's the best implementation for my project. The excel spreadsheet is stored on a shared network drive if the affect the solution to the problem. Thanks for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
primary/secondary axis next to each other | Charts and Charting in Excel | |||
(Primary) Key Column? | Excel Discussion (Misc queries) | |||
two units on primary Y axis | Charts and Charting in Excel | |||
primary & secondary axis | Charts and Charting in Excel |