G
Guest
greetings! i have been lurking on this forum for the past couple of weeks,
seeking guidance in the insane task of redesigning a database that i
inherited so that it can evolve with the scope of our equipment service /
repair department. i've already gathered several useful tricks, for which i
thank you all, however, here's the problem i can't seem to solve.
The database was made in Access 2000. i've got 2003 on my machine (running
Win XP Pro)
So i don't hurt the original data, i've made a copy dubbed the test-dummy
version, and i ran the Table Analyzer on the one large ponderous table that
formed the original database with intent to make it more efficient / logical
/ compact... This table had had several fields pruned away over the years as
they were no longer used / cared about, and several more new fields (storing
new types of data about which the current department is more interested in
tracking) grafted on as well.
The latest request came down the pipe for us to track all customer contacts
that did NOT become equipment returns as a way to gauge the effectiveness of
our troubleshooting. Previously this database has only stored return and
repair information. Since while every return record would have an associated
"claim" record, not every "claim" record would have an associated return, i
was certain that we could do it with the database that we already had, ...if
i could just arrange it that way.
Table Analyzer Wizard split the one big table up into several little ones,
as promised, and created a query which was supposed to replace my previous
datasheet-version-of-the-old-form for routine data entry. (Note: i use the
datasheet version of the form as opposed to the form version largely because
of personal preference / quirk.) i then modified its split job further along
the lines of Pertaining to Customer Claim of Trouble vs. Pertaining to Repair
of Equipment.
i then remade the original form for use by my engineers when they were
talking to the customer, and, as per the instructions given to me by the
Access help, based it on the query. Thanks to some nifty code i found in the
2002 version of MS Access Inside Out (which i've had my head in for the
better part of the week) i've even made it possible to add a new customer on
the fly.
But here's the wall i've hit that i can't seem to knock down:
i can add a new customer's claim of equipment trouble via the form and see
the information show up on the various tables.
i can also add the same information to the query (even though the three text
fields Symptoms, Work Details, and Comments we once had have been newly
upgraded to Memo to allow the engineers to save more details).
HOWEVER, when i add a new record via the form, while the info shows up in
the tables, it does NOT show up in the query -- unless i type it all in again
directly to the query -- which, it would seem, would defeat the purpose of
having done this shell game with the tables in the first place.
i did read (in this forum, IIRC) that memo fields can prevent a query from
being updatable, but i would like to have more than 255 characters available
for the engineers to have when they record customer problem info. Short of
tearing it all down and rebuilding it from scratch, and then importing all
the relevant data from the original, how can i get this thing to work?
Thanks in advance for your recommendations,
Querent
(and for my next trick, i want to set the form up so that pressing a button
"Assign RMA" takes the user to the repair-related info, with the next RMA
number waiting, ...but that can wait until after i get this licked.)
seeking guidance in the insane task of redesigning a database that i
inherited so that it can evolve with the scope of our equipment service /
repair department. i've already gathered several useful tricks, for which i
thank you all, however, here's the problem i can't seem to solve.
The database was made in Access 2000. i've got 2003 on my machine (running
Win XP Pro)
So i don't hurt the original data, i've made a copy dubbed the test-dummy
version, and i ran the Table Analyzer on the one large ponderous table that
formed the original database with intent to make it more efficient / logical
/ compact... This table had had several fields pruned away over the years as
they were no longer used / cared about, and several more new fields (storing
new types of data about which the current department is more interested in
tracking) grafted on as well.
The latest request came down the pipe for us to track all customer contacts
that did NOT become equipment returns as a way to gauge the effectiveness of
our troubleshooting. Previously this database has only stored return and
repair information. Since while every return record would have an associated
"claim" record, not every "claim" record would have an associated return, i
was certain that we could do it with the database that we already had, ...if
i could just arrange it that way.
Table Analyzer Wizard split the one big table up into several little ones,
as promised, and created a query which was supposed to replace my previous
datasheet-version-of-the-old-form for routine data entry. (Note: i use the
datasheet version of the form as opposed to the form version largely because
of personal preference / quirk.) i then modified its split job further along
the lines of Pertaining to Customer Claim of Trouble vs. Pertaining to Repair
of Equipment.
i then remade the original form for use by my engineers when they were
talking to the customer, and, as per the instructions given to me by the
Access help, based it on the query. Thanks to some nifty code i found in the
2002 version of MS Access Inside Out (which i've had my head in for the
better part of the week) i've even made it possible to add a new customer on
the fly.
But here's the wall i've hit that i can't seem to knock down:
i can add a new customer's claim of equipment trouble via the form and see
the information show up on the various tables.
i can also add the same information to the query (even though the three text
fields Symptoms, Work Details, and Comments we once had have been newly
upgraded to Memo to allow the engineers to save more details).
HOWEVER, when i add a new record via the form, while the info shows up in
the tables, it does NOT show up in the query -- unless i type it all in again
directly to the query -- which, it would seem, would defeat the purpose of
having done this shell game with the tables in the first place.
i did read (in this forum, IIRC) that memo fields can prevent a query from
being updatable, but i would like to have more than 255 characters available
for the engineers to have when they record customer problem info. Short of
tearing it all down and rebuilding it from scratch, and then importing all
the relevant data from the original, how can i get this thing to work?
Thanks in advance for your recommendations,
Querent
(and for my next trick, i want to set the form up so that pressing a button
"Assign RMA" takes the user to the repair-related info, with the next RMA
number waiting, ...but that can wait until after i get this licked.)