HI
Sorry to be so long getting back to you. I use an offline reader &
this is a very busy newsgroup. I seldom go back more than a day to
look for responses.
The conundrum in what you've explained so far is that you want to
capture some data, create some related data, delete the master data
(and the related data) and then capture the next batch of data which
may or may not have some of the same entities as the date you just
deleted. In that case, you'd like to keep the related data (already
deleted) with the same entity which may have new information in its
record. Are you familiar with "Schrödinger's Cat" from metaphysics?
Except in your case there is no doubt, the earlier data is gone.
"Macro" is kind of a dirty word where serious Access developers are
concerned. The VBA code that is referred to as "macro" in other
platforms is referred to as "VBA" in Access. There is a crippled
facility available in Access called "Macro". You can do a lot with it
and Microsoft encourages beginners to use it. Some few things can
only be done with it. However, debugging is impossible with it and
advanced development requires that you shift to VBA anyway. I could
go on ...
You need a third table for the imports. call it something like
tblWorking. You may create it initially by transfer spreadsheet but
you'll need to rename it. Forever after, imports will be appended
into this table. You can delete all records in the table with a
delete query before each import. If you were to repeatedly create and
delete the table your application could bloat between Compact and
Repair cycles.
Create an Update Query wherein for every record whose primary key in
tblDataDump is equal to the primary key in tblWorking then update the
value from every field in tblWorking into the field in tblDataDump.
That will update all existing entities that were also in the new
version of tblDataDump.
Create an Append Query wherein for every record in tblWorking whose
primary key is not matched in tblDataDump, append the record. That
appends all new entities.
Of course you're going to build a nice user interface on this so that
your users are working from Forms and you can present your data in
Reports. Access is a far cry from Excel and you absolutely should not
require nor allow your users to muck around in the Tables nor to be
aware of nor run Queries.
HTH
--
-Larry-
--
All we can tell at this point is that it appears that your design
doesn't match the needed business solution. In order to get help you
need to post back with a description of what you are trying to achieve
in "real world" terms, not Access. It is clear that you have data
coming from Excel that you want to manage in an Access application.
Please also list the entities involved in your business problem and
desired solution.
While you can post Access issues into any of the
microsoft.public.access* newsgroups get a response, I particularly
recommend
microsoft.public.access.gettingstarted and
microsoft.public.access.tablesdesign
for those just starting with Access.
Also, you should visit and bookmark
www.mvps.org/access
it's an incredibly valuable resource for Access developers.
HTH
--
-Larry-
--
news:
[email protected]...
- Show quoted text -
Hi,
Thanks for your response. My problem/situation is as follows:
Our company funtion is Incident and problem management of a 3rd party
application. Due to various reasons we have to use a part of this
application to log and monitor all issues. As this tool is not ours
it will only do the very basics of recording. The 3rd party company
then email us an Excel Data Dump of all issues with a few fields -
Logged by, date raised and closed, summary etc.
As we need much more information attached to each issue we have
created a spreadsheet which Vlookup's from the Data Dump for changes.
On this new Spreadsheet we then add the further fields we need -
Workaround, business impact, action, root cause etc.
Due to the large numbers of issues we are dealing with and the amount
of extra fields we require, the spreadsheet is unreadable and
unmanagable. What I wanted is a simply database, in that I can create
a table (Table A) with the extra fields - Workarond, business impact
etc. I would then like to upload the 3rd Party data dump to another
table (Table B) and have these linked. I have done all of this but
would like to know how I can constantly replace the data in Table B
without it affecting records in Table A.
Maybe I have made this sound too complicated as I though it should be
a relatively easy task with the right knowledge.
Thanks
