Excel thinking...

[Guest]

My client is used to seeing data a certain way, and I can not seem to sway
her. I'm trying to normalize her database, and we're making pretty good
headway, but I have hit one particular snag that I can't seem to get around.

She's the librarian for a company's manuals, and she has to track full
manual revisions as well as temporary revisions. Apparently, a vendor might
publish 1 to 20 (or more) temporary revisions to different chapters of a
manual before they ever update the whole thing. While RevNum and RevDate
properly belong to tblManual, TempRevNum and TempRevDate belong in a seperate
table.

Here's the problem:

She wants the dynaset describing her manuals to include RevNum and RevDate
(easy enough), as well as ALL TemRevNum. Currently, she is WIPING OUT DATA,
and manually typing in something like this: "16-22, 44, 46a". Since she is
the client, and therefore right, I'm struggling with how to produce an
expression that concatenates every possible instance of a matching record,
and even accounts for lowest and highest value for consecutive numbers...

Is this even worth pursuing, or should I just give up? Should I tell her to
keep doing this the way she's doing it and try to work around her? Maybe I
should walk out in a huff and tell her, "It's a relational thing; you
wouldn't understand!" Am I making too much of this? Is there an easier
solution that I'm missing?

You guys are the best, so if you got nothing, I got nothing.

Thnx!
Dale

 

[Duane Hookom]

Could you provide some actual records and how she wants them displayed? You
description tells us little about your tables and fields.

 

[Guest]

tblManual includes (among others) the following fields: ManID, ManTitle,
CurRevNum, CurRevDate, CurTRNum, CurTRDate. A record would look something
like this:1, "How to...", 1, 1/1/2006, 1a, 1/2/2006.

Only it would only look like that if there were only one Current Temporary
Revision. The client would expect to see a different field for each
temporary revision. For example: ManID, ManTitle, CurRevNum, CurRevDate,
CurTRNum1, CurTRDate1, CurTRNum2, CurTRDate2, CurTRNum3, CurTRDate3, etc.,
with a record that would look something like this: 1, "How to...", 1,
1/1/2006, 1a, 1/2/2006, 1b, 1/3/2006, 1c, 1/4/2006, etc.

Currently, her records look like this, 1, "How to...", 1, 1/1/2006, 1a-1c,
1/4/2006.

My solution was to provide a table called tblTemp with the following fields:
ManID, CurTRNum, CurTRDate. When tblManual and tblTemp were linked, the
client could view the records like this:

1, "How to....", 1, 1/1/2006, 1a, 1/2/2006
1, "How to....", 1, 1/1/2006, 1b, 1/3/2006
1, "How to....", 1, 1/1/2006, 1c, 1/4/2006

The client does not like that solultion. She wants to see all of the
Temporary Revisions in one field or spread across an infinite number of
fields (Excel thinking.) The question is, would I concatenate every possible
Temporary Revision Number into one field, with the most recent date appearing
in the CurTRDate (and HOW would I do something insane like that?)

Dale

 

[Duane Hookom]

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. It is
not designed to hand 1a-1c functionality out-of-the-box.

 

[John Vinson]

I'm struggling with how to produce an
expression that concatenates every possible instance of a matching record,
and even accounts for lowest and highest value for consecutive numbers...

You should be able to adapt the code at

www.mvps.org/access/forms/mdl0008.htm

You'll need to add a bit of code in the loop to catch consecutive
values.

John W. Vinson[MVP]

 

[Guest]

John,

That link's not working for me. I can get as far as forms/index.html, but I
can't pull up forms/mdl0008.htm, or any of the many variations that I have
tried. I found some interesting entries on that page, but I did not find the
one relating to my question. Do you, by chance, remember the title of the
entry?

Thnx!
Dale

 

[Guest]

Hey! That's a cool function!

Now the question is, do I really want to go through with this.

Thnx so much for your help.
Dale

 

[Gina via AccessMonster.com]

I think it should be:

http://www.mvps.org/access/modules/mdl0008.htm

Gina

John,

That link's not working for me. I can get as far as forms/index.html, but I
can't pull up forms/mdl0008.htm, or any of the many variations that I have
tried. I found some interesting entries on that page, but I did not find the
one relating to my question. Do you, by chance, remember the title of the
entry?

Thnx!
Dale
[quoted text clipped - 8 lines]

John W. Vinson[MVP]

 

[John Vinson]

John,

That link's not working for me. I can get as far as forms/index.html, but I
can't pull up forms/mdl0008.htm, or any of the many variations that I have
tried. I found some interesting entries on that page, but I did not find the
one relating to my question. Do you, by chance, remember the title of the
entry?

OOPS! Sorry, cut and paste got out of hand:

www.mvps.org/access/modules/mdl0008.htm

It's the same logic as Duane's though so it may not get you much you
don't already have.

John W. Vinson[MVP]

 

[Guest]

thnx, guys. you're awesome!

dale

OOPS! Sorry, cut and paste got out of hand:

www.mvps.org/access/modules/mdl0008.htm

It's the same logic as Duane's though so it may not get you much you
don't already have.

John W. Vinson[MVP]