multiple queries needed to accomplish task??

[Guest]

Hi all,

I have a first name and last name field that I need to manipulate to get the
look needed for a report. There are three parts to what I want to do, and I
need help determining if this should be done in three separate queries or
less? Example below

For instance:

Jerry Smith
Cameron Handle
Carol Frick
Peter Liu
Jessica Silles

Every time Jessica Silles is an attendee at an event, I need her to show up
as JDS, and Peter Liu to show up as PTL, with the other people showing up as
their first initial and last name. On top of that, for every time Jessica or
Peter are on the attendee listing, I need JDS to show up first, PTL to show
second and then the rest of the names following in any order. First I
created a query to change all of the names to first initial and last name.

Then I opened a second query and used the following to get the format I need
for JDS and PTL: Names: (IIf([Name]='J. Silles,"JDS")) & (IIf([Name]='P.
Liu',"PTL"))

When I run the query it does convert to JDS and PTL, but then no other names
show up, I need to specify to leave all the other names the same, but don’t
know how. AND, I need the order of JDS listed first for any event she
attends and PTL listed second (or in the case JDS does not attend, PTL would
then default to being first) followed by the rest.

Any help is greatly appreciated!!

-Valerie

 

[Jeff Boyce]

Valerie

If Jessica Stiles has a middle initial of "D", why not just store the "D" in
a MiddleInitial field in the table? And I hope that Jerry Smith's middle
name is not "Daniel" or "Douglas" or "David"... <g!>

If you always want Jessica's initials to show up first, and no one else will
EVER be the "first one" (i.e., Jessica lives forever, never leaves, ...),
your approach seems workable. On the other hand, if Jessica is not
permanent, consider adding a "ranking" field on the Person/Name table, so
you can, when needed, change the ranking/order. Peter would have a
ranking/order that follows Jessica's, and you could use a single value if
all the rest are "in a big pile" (but wouldn't you want to see, say, an
alpha-sort on the rest?).

Are you using the JDS and PTL as identifiers for attendees? If so, consider
a different approach... create a Person table and use an arbitrary ID field
(Autonumber comes to mind) if you don't have unique person identifiers
(e.g., Employee#). Then, instead of storing initials (remember, JDS and
J?S), store the unique ID#. You can always use a query to "lookup" who goes
with PersonID = 51.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff, thanks for the prompt response. The database where I am pulling
information from into Access is large, thousands and thousands of people are
in this database, and any number of them could attend an event. I don't
believe using a middle initial would prove beneficial. (By the way, all the
names are fake, for example purposes....just want to put that out there).


As far as JDS and PTL. I understand the ranking suggestion, however, for
each event, there are different people attending, I don't know how I would
add a ranking field and tell it JDS is always 1, PTL is always 2, and the
rest are in any order. Is this possible? Alpha otherwise has no
significance for the particular report I am working on now.

JDS and PTL are strictly representing Jessica Silles and Peter Liu. Each
person in the database of course has a unique identifier, which I've pulled
into access, but does not need to be represented in the report.

I know what I need to do, but I'm self taught in Access, and don't
understand the logic needed to do what I want to do, I guess......

Oh! One more thing I forgot. I have all of these people concatenated: J.
Smith, C. Handle, C. Frick, P. Liu, J. Silles. (this is how it exists now
because I can't figure the answer to my first question yet)

I also need to have this field just show: JDS, Select Guests
when there are more than 15 guests attached to a particular event. It's
actually a calendar of events report, but for some events we have 500
attendees, and that's way to lenghty to list for this particular report.

I guess self-training can only get you so far......

Valerie

If Jessica Stiles has a middle initial of "D", why not just store the "D" in
a MiddleInitial field in the table? And I hope that Jerry Smith's middle
name is not "Daniel" or "Douglas" or "David"... <g!>

If you always want Jessica's initials to show up first, and no one else will
EVER be the "first one" (i.e., Jessica lives forever, never leaves, ...),
your approach seems workable. On the other hand, if Jessica is not
permanent, consider adding a "ranking" field on the Person/Name table, so
you can, when needed, change the ranking/order. Peter would have a
ranking/order that follows Jessica's, and you could use a single value if
all the rest are "in a big pile" (but wouldn't you want to see, say, an
alpha-sort on the rest?).

Are you using the JDS and PTL as identifiers for attendees? If so, consider
a different approach... create a Person table and use an arbitrary ID field
(Autonumber comes to mind) if you don't have unique person identifiers
(e.g., Employee#). Then, instead of storing initials (remember, JDS and
J?S), store the unique ID#. You can always use a query to "lookup" who goes
with PersonID = 51.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,

I have a first name and last name field that I need to manipulate to get
the
look needed for a report. There are three parts to what I want to do, and
I
need help determining if this should be done in three separate queries or
less? Example below

For instance:

Jerry Smith
Cameron Handle
Carol Frick
Peter Liu
Jessica Silles

Every time Jessica Silles is an attendee at an event, I need her to show
up
as JDS, and Peter Liu to show up as PTL, with the other people showing up
as
their first initial and last name. On top of that, for every time Jessica
or
Peter are on the attendee listing, I need JDS to show up first, PTL to
show
second and then the rest of the names following in any order. First I
created a query to change all of the names to first initial and last name.

Then I opened a second query and used the following to get the format I
need
for JDS and PTL: Names: (IIf([Name]='J. Silles,"JDS")) & (IIf([Name]='P.
Liu',"PTL"))

When I run the query it does convert to JDS and PTL, but then no other
names
show up, I need to specify to leave all the other names the same, but don't
know how. AND, I need the order of JDS listed first for any event she
attends and PTL listed second (or in the case JDS does not attend, PTL
would
then default to being first) followed by the rest.

Any help is greatly appreciated!!

-Valerie

 

[Jeff Boyce]

Valerie

I'm not quite visualizing what you're describing.

Are you saying that Jessica and Peter are #1 and #2 no matter what event(s)
they register for, and everyone else is #3? Or are you saying that each
event will have someone (perhaps neither Jessica nor Peter) who will be #1
and #2, with all others #3?

If Jessica is always #1, the #1 belongs with Jessica. If each persons
registration for a specific event could have someone different as #1, the #1
belongs in the [Registration] table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff, thanks for the prompt response. The database where I am pulling
information from into Access is large, thousands and thousands of people
are
in this database, and any number of them could attend an event. I don't
believe using a middle initial would prove beneficial. (By the way, all
the
names are fake, for example purposes....just want to put that out there).



As far as JDS and PTL. I understand the ranking suggestion, however, for
each event, there are different people attending, I don't know how I would
add a ranking field and tell it JDS is always 1, PTL is always 2, and the
rest are in any order. Is this possible? Alpha otherwise has no
significance for the particular report I am working on now.

JDS and PTL are strictly representing Jessica Silles and Peter Liu. Each
person in the database of course has a unique identifier, which I've
pulled
into access, but does not need to be represented in the report.

I know what I need to do, but I'm self taught in Access, and don't
understand the logic needed to do what I want to do, I guess......

Oh! One more thing I forgot. I have all of these people concatenated:
J.
Smith, C. Handle, C. Frick, P. Liu, J. Silles. (this is how it exists now
because I can't figure the answer to my first question yet)

I also need to have this field just show: JDS, Select Guests
when there are more than 15 guests attached to a particular event. It's
actually a calendar of events report, but for some events we have 500
attendees, and that's way to lenghty to list for this particular report.

I guess self-training can only get you so far......

Valerie

If Jessica Stiles has a middle initial of "D", why not just store the "D"
in
a MiddleInitial field in the table? And I hope that Jerry Smith's middle
name is not "Daniel" or "Douglas" or "David"... <g!>

If you always want Jessica's initials to show up first, and no one else
will
EVER be the "first one" (i.e., Jessica lives forever, never leaves, ...),
your approach seems workable. On the other hand, if Jessica is not
permanent, consider adding a "ranking" field on the Person/Name table, so
you can, when needed, change the ranking/order. Peter would have a
ranking/order that follows Jessica's, and you could use a single value if
all the rest are "in a big pile" (but wouldn't you want to see, say, an
alpha-sort on the rest?).

Are you using the JDS and PTL as identifiers for attendees? If so,
consider
a different approach... create a Person table and use an arbitrary ID
field
(Autonumber comes to mind) if you don't have unique person identifiers
(e.g., Employee#). Then, instead of storing initials (remember, JDS and
J?S), store the unique ID#. You can always use a query to "lookup" who
goes
with PersonID = 51.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,

I have a first name and last name field that I need to manipulate to
get
the
look needed for a report. There are three parts to what I want to do,
and
I
need help determining if this should be done in three separate queries
or
less? Example below

For instance:

Jerry Smith
Cameron Handle
Carol Frick
Peter Liu
Jessica Silles

Every time Jessica Silles is an attendee at an event, I need her to
show
up
as JDS, and Peter Liu to show up as PTL, with the other people showing
up
as
their first initial and last name. On top of that, for every time
Jessica
or
Peter are on the attendee listing, I need JDS to show up first, PTL to
show
second and then the rest of the names following in any order. First I
created a query to change all of the names to first initial and last
name.

Then I opened a second query and used the following to get the format I
need
for JDS and PTL: Names: (IIf([Name]='J. Silles,"JDS")) &
(IIf([Name]='P.
Liu',"PTL"))

When I run the query it does convert to JDS and PTL, but then no other
names
show up, I need to specify to leave all the other names the same, but
don't
know how. AND, I need the order of JDS listed first for any event she
attends and PTL listed second (or in the case JDS does not attend, PTL
would
then default to being first) followed by the rest.

Any help is greatly appreciated!!

-Valerie

 

[Guest]

I think I asked too many questions in one post, although I asked all together
because they kind of layer upon each other.

Yes, Jessica and Peter are #1 and #2 respectively for every event they
attend. Think of it as the President and VP of a company who attend all
events. There are cases where Jessica will not be there however, and Peter
would need to default to #1. I think I have it figured out how I am going to
set up a ranking system. Still not sure on the rest though. I'm really just
hung up on the actual wording in the expression. I know what I want to do,
pretty sure I know what order I want to do it in, just not sure of the
wording / building an expression to represent the ideas in my mind.

Thanks Jeff, very much for your assistance. If you can't help further I
certainly understand. I think I should have posted my scenarios separately.

-Valerie

Valerie

I'm not quite visualizing what you're describing.

Are you saying that Jessica and Peter are #1 and #2 no matter what event(s)
they register for, and everyone else is #3? Or are you saying that each
event will have someone (perhaps neither Jessica nor Peter) who will be #1
and #2, with all others #3?

If Jessica is always #1, the #1 belongs with Jessica. If each persons
registration for a specific event could have someone different as #1, the #1
belongs in the [Registration] table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff, thanks for the prompt response. The database where I am pulling
information from into Access is large, thousands and thousands of people
are
in this database, and any number of them could attend an event. I don't
believe using a middle initial would prove beneficial. (By the way, all
the
names are fake, for example purposes....just want to put that out there).



As far as JDS and PTL. I understand the ranking suggestion, however, for
each event, there are different people attending, I don't know how I would
add a ranking field and tell it JDS is always 1, PTL is always 2, and the
rest are in any order. Is this possible? Alpha otherwise has no
significance for the particular report I am working on now.

JDS and PTL are strictly representing Jessica Silles and Peter Liu. Each
person in the database of course has a unique identifier, which I've
pulled
into access, but does not need to be represented in the report.

I know what I need to do, but I'm self taught in Access, and don't
understand the logic needed to do what I want to do, I guess......

Oh! One more thing I forgot. I have all of these people concatenated:
J.
Smith, C. Handle, C. Frick, P. Liu, J. Silles. (this is how it exists now
because I can't figure the answer to my first question yet)

I also need to have this field just show: JDS, Select Guests
when there are more than 15 guests attached to a particular event. It's
actually a calendar of events report, but for some events we have 500
attendees, and that's way to lenghty to list for this particular report.

I guess self-training can only get you so far......

Valerie

If Jessica Stiles has a middle initial of "D", why not just store the "D"
in
a MiddleInitial field in the table? And I hope that Jerry Smith's middle
name is not "Daniel" or "Douglas" or "David"... <g!>

If you always want Jessica's initials to show up first, and no one else
will
EVER be the "first one" (i.e., Jessica lives forever, never leaves, ...),
your approach seems workable. On the other hand, if Jessica is not
permanent, consider adding a "ranking" field on the Person/Name table, so
you can, when needed, change the ranking/order. Peter would have a
ranking/order that follows Jessica's, and you could use a single value if
all the rest are "in a big pile" (but wouldn't you want to see, say, an
alpha-sort on the rest?).

Are you using the JDS and PTL as identifiers for attendees? If so,
consider
a different approach... create a Person table and use an arbitrary ID
field
(Autonumber comes to mind) if you don't have unique person identifiers
(e.g., Employee#). Then, instead of storing initials (remember, JDS and
J?S), store the unique ID#. You can always use a query to "lookup" who
goes
with PersonID = 51.

Regards

Jeff Boyce
Microsoft Office/Access MVP




Hi all,

I have a first name and last name field that I need to manipulate to
get
the
look needed for a report. There are three parts to what I want to do,
and
I
need help determining if this should be done in three separate queries
or
less? Example below

For instance:

Jerry Smith
Cameron Handle
Carol Frick
Peter Liu
Jessica Silles

Every time Jessica Silles is an attendee at an event, I need her to
show
up
as JDS, and Peter Liu to show up as PTL, with the other people showing
up
as
their first initial and last name. On top of that, for every time
Jessica
or
Peter are on the attendee listing, I need JDS to show up first, PTL to
show
second and then the rest of the names following in any order. First I
created a query to change all of the names to first initial and last
name.

Then I opened a second query and used the following to get the format I
need
for JDS and PTL: Names: (IIf([Name]='J. Silles,"JDS")) &
(IIf([Name]='P.
Liu',"PTL"))

When I run the query it does convert to JDS and PTL, but then no other
names
show up, I need to specify to leave all the other names the same, but
don't
know how. AND, I need the order of JDS listed first for any event she
attends and PTL listed second (or in the case JDS does not attend, PTL
would
then default to being first) followed by the rest.

Any help is greatly appreciated!!

-Valerie

 

[Jeff Boyce]

Valerie

I guess I'm still not clear on any one thing you are trying to do.

If you add a "Ranking" field to the Person table, you can make everyone
except Jessica and Peter #3 for ranking.

You'd then use the ranking in a query to determine the order of the results.

If the ID corresponds to Jessica, you would use the initials.

If the ID corresponds to Peter, you would use the initials.

Otherwise, you would use your FirstInitial LastName construction.

What am I not getting?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I think I asked too many questions in one post, although I asked all
together
because they kind of layer upon each other.

Yes, Jessica and Peter are #1 and #2 respectively for every event they
attend. Think of it as the President and VP of a company who attend all
events. There are cases where Jessica will not be there however, and
Peter
would need to default to #1. I think I have it figured out how I am going
to
set up a ranking system. Still not sure on the rest though. I'm really
just
hung up on the actual wording in the expression. I know what I want to
do,
pretty sure I know what order I want to do it in, just not sure of the
wording / building an expression to represent the ideas in my mind.

Thanks Jeff, very much for your assistance. If you can't help further I
certainly understand. I think I should have posted my scenarios
separately.

-Valerie

Valerie

I'm not quite visualizing what you're describing.

Are you saying that Jessica and Peter are #1 and #2 no matter what
event(s)
they register for, and everyone else is #3? Or are you saying that each
event will have someone (perhaps neither Jessica nor Peter) who will be
#1
and #2, with all others #3?

If Jessica is always #1, the #1 belongs with Jessica. If each persons
registration for a specific event could have someone different as #1, the
#1
belongs in the [Registration] table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff, thanks for the prompt response. The database where I am
pulling
information from into Access is large, thousands and thousands of
people
are
in this database, and any number of them could attend an event. I
don't
believe using a middle initial would prove beneficial. (By the way,
all
the
names are fake, for example purposes....just want to put that out
there).



As far as JDS and PTL. I understand the ranking suggestion, however,
for
each event, there are different people attending, I don't know how I
would
add a ranking field and tell it JDS is always 1, PTL is always 2, and
the
rest are in any order. Is this possible? Alpha otherwise has no
significance for the particular report I am working on now.

JDS and PTL are strictly representing Jessica Silles and Peter Liu.
Each
person in the database of course has a unique identifier, which I've
pulled
into access, but does not need to be represented in the report.

I know what I need to do, but I'm self taught in Access, and don't
understand the logic needed to do what I want to do, I guess......

Oh! One more thing I forgot. I have all of these people concatenated:
J.
Smith, C. Handle, C. Frick, P. Liu, J. Silles. (this is how it exists
now
because I can't figure the answer to my first question yet)

I also need to have this field just show: JDS, Select Guests
when there are more than 15 guests attached to a particular event.
It's
actually a calendar of events report, but for some events we have 500
attendees, and that's way to lenghty to list for this particular
report.

I guess self-training can only get you so far......



:

Valerie

If Jessica Stiles has a middle initial of "D", why not just store the
"D"
in
a MiddleInitial field in the table? And I hope that Jerry Smith's
middle
name is not "Daniel" or "Douglas" or "David"... <g!>

If you always want Jessica's initials to show up first, and no one
else
will
EVER be the "first one" (i.e., Jessica lives forever, never leaves,
...),
your approach seems workable. On the other hand, if Jessica is not
permanent, consider adding a "ranking" field on the Person/Name table,
so
you can, when needed, change the ranking/order. Peter would have a
ranking/order that follows Jessica's, and you could use a single value
if
all the rest are "in a big pile" (but wouldn't you want to see, say,
an
alpha-sort on the rest?).

Are you using the JDS and PTL as identifiers for attendees? If so,
consider
a different approach... create a Person table and use an arbitrary ID
field
(Autonumber comes to mind) if you don't have unique person identifiers
(e.g., Employee#). Then, instead of storing initials (remember, JDS
and
J?S), store the unique ID#. You can always use a query to "lookup"
who
goes
with PersonID = 51.

Regards

Jeff Boyce
Microsoft Office/Access MVP




Hi all,

I have a first name and last name field that I need to manipulate to
get
the
look needed for a report. There are three parts to what I want to
do,
and
I
need help determining if this should be done in three separate
queries
or
less? Example below

For instance:

Jerry Smith
Cameron Handle
Carol Frick
Peter Liu
Jessica Silles

Every time Jessica Silles is an attendee at an event, I need her to
show
up
as JDS, and Peter Liu to show up as PTL, with the other people
showing
up
as
their first initial and last name. On top of that, for every time
Jessica
or
Peter are on the attendee listing, I need JDS to show up first, PTL
to
show
second and then the rest of the names following in any order. First
I
created a query to change all of the names to first initial and last
name.

Then I opened a second query and used the following to get the
format I
need
for JDS and PTL: Names: (IIf([Name]='J. Silles,"JDS")) &
(IIf([Name]='P.
Liu',"PTL"))

When I run the query it does convert to JDS and PTL, but then no
other
names
show up, I need to specify to leave all the other names the same,
but
don't
know how. AND, I need the order of JDS listed first for any event
she
attends and PTL listed second (or in the case JDS does not attend,
PTL
would
then default to being first) followed by the rest.

Any help is greatly appreciated!!

-Valerie