Updating Information in a Field

[Guest]

Hello,

I am fairly new to Access and I have a question concerning its
functionality. I pull a query of open items for reps. Some of these items are
by themselves (with the proper labels) and some of the open items are with in
groups. My question is concerning the labeling of items with in those groups.
The way that my system is set up, the query will pull both the individual
open items with in the group and the group tag. The group tag will contain
all of the up to date comments and the proper label (goodwill, reclaim, etc)
of all of the related items with in the group. I would like to be able to
somehow fill that information down from the group tag to the individual items
with in the group. At the moment I am doing this manually in excel and it can
take several hours. The reason that I need the labels is because I need to
assign the proper representative the proper open items, which is done by
label.

Any ideas? Am I making sense?

Thanks,

Josh

 

[Larry Daugherty]

Not really making sense. I infer that you run a query that returns a
bunch of data of interest. Some of the rows returned have empty
fields in the records, other rows have information you want to
populate into other rows. Is that your intent?

First, how do you know which of the records with empty fields should
get which additional data?

Secondly and probably most important, In a relational database it just
isn't done that you copy information from one place in the database to
any other place in the database. You can always perform the
calculation to display the information. If the same information
exists in more than one place then sooner or later the two (or more??)
sets of data will become unsynchronized. Data doesn't turn color when
it's gone bad so you'll never know...

To get a better answer you'd have to pose a better question with more
details about what you're doing and why.

HTH

 

[Guest]

Let me and try to explain this better.

Here is my current process of how I pull open items:

I use MS Access to query a series of tables that comes from my processing
computer system. This will pull all of the open items by date (greater than
five days old). It will have a dollar amount, date, rep ID, comments, Item
Number, and a group number, if the specific item is in a group.

I then export all of that information to excel and sort it in such a way
that I can manual fill the comments and labels of items that are in groups
from the group tag. I determine this by dollar amounts and group number. The
group ID number will be the same as one of the individual items that is with
in the group and the total dollar amount, positive or negative, of all of the
items with in the group will equal the dollar amount that is on the group tag.

Once I fill all of the comments and labels from the group tag to that of the
individual items with in the group, I then delete the group tag. There is no
need to inflate the amount of open items that I have by having misc. group
tags in there. By deleting the group tags, management will have a better idea
of the true amount of open items.

While still in excel, I sort the data again so that I can assign the proper
rep the proper items. This is done by three different ways. It could be done
by label, by REP ID (if it is a shared label such as RESEARCH ITEM), and by
account number. The way that I assign these items is that I cut them out of
the master excel spreadsheet and insert them into the individual reps open
items spreadsheet for that specific week. A Vlookup is performed compairing
the data from the previous week to that of the current week to see what open
items have been closed.

This takes several hours to do as there is a lot of manual work that I
believe can be automated.

Here is what I have already set up in Access.

I have set up some make table queries that pull the correct lables, rep
ID's, and bank accounts. I also have another query built that will pull of
the remaining open items, or "left overs" of the other queries that I have
built for my various reps. A problem that I see is that since the items that
are with in a group have no label, it is putting the group tag into the
correct reps table, but it is putting the unlabeled items that are with in a
group into the "left overs" table. I would like to try and elimiate this by
somehow having access put the correct comments and label from the group tag
into the appropriate open item.

I would also like to have access tell me what items are resolved and what
are open, similar to what I use Vlookup for in excel. But I asked that
question in another post.

Having access do this for me would save several hours per week.

Does this help at all?

 

[Larry Daugherty]

No, It still isn't clear to me what you are doing. It would help if
you would:

1) stop trying to get someone to feed you solutions in Access to
issues that you define in terms of Excel.: i.e. VLookup, etc.

2) give names to things

3) Describe the real world problem that your whole application is
trying to solve at a higher level than just the problems you're
encountering. Describe the problem and its solution in high level
terms. As it is, you seem to be saying that you don't need to do what
I'd suggested that you do because you already do it but it doesn't get
the job done.

4) Then describe things in successively lower level terms

"A problem, well defined, is half solved".

HTH

 

[Guest]

Sorry Larry. Let me think about a better way to explain it before I get back
to you.