Stuck on a Querry

[Charles]

Hello,

{Please reply via email... don't get to check this group much}

Below are the tables...

TBL-WorkEvent: WorkEventID, Date, StartTime, EndTime, PostID, OICID,
SupervisorID, CoWorker1, CoWorker2, WorkedAsSupervisor, Notes

TBL-Employee: EmployeeID, LastName, FirstName, Rank

Ok... the fields in WorkEvents tables (OICID, SupervisorID, CoWorker1,
and CoWorker2) are all one-to-many linked to Employee table via
"lookup" datatype on the Workevent table...

This works great while entering data into the WorkEvent table... the
names drop down just fine. But when I go to make a querry to search by
name for any one of the fields (OICID SupervisorID, CoWorker1, and
CoWorker2) by placing [Enter Last Name] in the criteria field of the
querry of any of the mentioned fields I get a result of litterally
nothing... no results, just the headers of the querry.

Sorry if I am missing something fundamental, but I am a self taught
novice.

{Please reply via email... don't get to check this group much}

Thanks,
Charles
charles.cromer[at]gmail.com

 

[strive4peace]

Hi Charles,

the newsgroups are read by many people and serve as a reference for
others, so I am going to post here where others can benefit as well

You should be using forms to enter your data, not putting it directly
into tables.

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

"But when I go to make a querry to search by

name for any one of the fields (OICID SupervisorID, CoWorker1, and
CoWorker2) by placing [Enter Last Name] in the criteria field of the
querry of any of the mentioned fields I get a result of litterally
nothing... no results, just the headers of the querry."

that is because the ID is stored, not the name... on a form, you can
make a combo to search where the first column is the hidden ID and you
pick a name -- but what you have to look for is what is stored, not what
is displayed



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Hello,

{Please reply via email... don't get to check this group much}

Below are the tables...

TBL-WorkEvent: WorkEventID, Date, StartTime, EndTime, PostID, OICID,
SupervisorID, CoWorker1, CoWorker2, WorkedAsSupervisor, Notes

TBL-Employee: EmployeeID, LastName, FirstName, Rank

Ok... the fields in WorkEvents tables (OICID, SupervisorID, CoWorker1,
and CoWorker2) are all one-to-many linked to Employee table via
"lookup" datatype on the Workevent table...

This works great while entering data into the WorkEvent table... the
names drop down just fine. But when I go to make a querry to search by
name for any one of the fields (OICID SupervisorID, CoWorker1, and
CoWorker2) by placing [Enter Last Name] in the criteria field of the
querry of any of the mentioned fields I get a result of litterally
nothing... no results, just the headers of the querry.

Sorry if I am missing something fundamental, but I am a self taught
novice.

{Please reply via email... don't get to check this group much}

Thanks,
Charles
charles.cromer[at]gmail.com

 

[Guest]

Hi Charles

Remove the prompt from all fields except the [LastName] in the query.

The reason you are only getting field headers is that you "are" seeing the
correct result of your query - i.e. no records match the criteria.

Sorry - don't do e mail replies. Hope this helps when you look at the forum
again in the future.

 

[Guest]

Just noticed that you don't have the [LastName] field in your query and this
is what you are prompting on. It will never work this way.

Bring the [LastName] into the query

 

[Guest]

Hi Crystal

I think they are using a query to search (may be wrong though)

But when I go to make a querry to search by

name for any one . . . . . . . . . .

--
Wayne
Manchester, England.

Hi Charles,

the newsgroups are read by many people and serve as a reference for
others, so I am going to post here where others can benefit as well

You should be using forms to enter your data, not putting it directly
into tables.

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

"But when I go to make a querry to search by

name for any one of the fields (OICID SupervisorID, CoWorker1, and
CoWorker2) by placing [Enter Last Name] in the criteria field of the
querry of any of the mentioned fields I get a result of litterally
nothing... no results, just the headers of the querry."

that is because the ID is stored, not the name... on a form, you can
make a combo to search where the first column is the hidden ID and you
pick a name -- but what you have to look for is what is stored, not what
is displayed



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Hello,

{Please reply via email... don't get to check this group much}

Below are the tables...

TBL-WorkEvent: WorkEventID, Date, StartTime, EndTime, PostID, OICID,
SupervisorID, CoWorker1, CoWorker2, WorkedAsSupervisor, Notes

TBL-Employee: EmployeeID, LastName, FirstName, Rank

Ok... the fields in WorkEvents tables (OICID, SupervisorID, CoWorker1,
and CoWorker2) are all one-to-many linked to Employee table via
"lookup" datatype on the Workevent table...

This works great while entering data into the WorkEvent table... the
names drop down just fine. But when I go to make a querry to search by
name for any one of the fields (OICID SupervisorID, CoWorker1, and
CoWorker2) by placing [Enter Last Name] in the criteria field of the
querry of any of the mentioned fields I get a result of litterally
nothing... no results, just the headers of the querry.

Sorry if I am missing something fundamental, but I am a self taught
novice.

{Please reply via email... don't get to check this group much}

Thanks,
Charles
charles.cromer[at]gmail.com

 

[Charles]

You should be using forms to enter your data, not putting it directly
into tables.

I am using forms for data entry... I am sorry... I was just describing
the tables the forms are based on.

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

Interesting link... I will have to look more at this.

"But when I go to make a querry to search by

name for any one of the fields (OICID SupervisorID, CoWorker1, and
CoWorker2) by placing [Enter Last Name] in the criteria field of the
querry of any of the mentioned fields I get a result of litterally
nothing... no results, just the headers of the querry."

that is because the ID is stored, not the name... on a form, you can
make a combo to search where the first column is the hidden ID and you
pick a name -- but what you have to look for is what is stored, not what
is displayed

Forgive me here, but how does one go about doing this? I am wanting
the OICID, SupervisorID, CoWorker1, and CoWorker2 to all be linked to
one Employee table. How do I go about creating a form that will have
drop downs for those fields that all link to the Employee table and
that will still work when I try to do a querry by last name? I am
sorry if I am being too vague or not catching something here.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Hello,

{Please reply via email... don't get to check this group much}

Below are the tables...

TBL-WorkEvent: WorkEventID, Date, StartTime, EndTime, PostID, OICID,
SupervisorID, CoWorker1, CoWorker2, WorkedAsSupervisor, Notes

TBL-Employee: EmployeeID, LastName, FirstName, Rank

Ok... the fields in WorkEvents tables (OICID, SupervisorID, CoWorker1,
and CoWorker2) are all one-to-many linked to Employee table via
"lookup" datatype on the Workevent table...

This works great while entering data into the WorkEvent table... the
names drop down just fine. But when I go to make a querry to search by
name for any one of the fields (OICID SupervisorID, CoWorker1, and
CoWorker2) by placing [Enter Last Name] in the criteria field of the
querry of any of the mentioned fields I get a result of litterally
nothing... no results, just the headers of the querry.

Sorry if I am missing something fundamental, but I am a self taught
novice.

{Please reply via email... don't get to check this group much}

Thanks,
Charles
charles.cromer[at]gmail.com

 

[strive4peace]

Hi charles,

here is an analogy for you...

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

You should be using forms to enter your data, not putting it directly
into tables.

I am using forms for data entry... I am sorry... I was just describing
the tables the forms are based on.

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

Interesting link... I will have to look more at this.

"But when I go to make a querry to search by

name for any one of the fields (OICID SupervisorID, CoWorker1, and
CoWorker2) by placing [Enter Last Name] in the criteria field of the
querry of any of the mentioned fields I get a result of litterally
nothing... no results, just the headers of the querry."

that is because the ID is stored, not the name... on a form, you can
make a combo to search where the first column is the hidden ID and you
pick a name -- but what you have to look for is what is stored, not what
is displayed

Forgive me here, but how does one go about doing this? I am wanting
the OICID, SupervisorID, CoWorker1, and CoWorker2 to all be linked to
one Employee table. How do I go about creating a form that will have
drop downs for those fields that all link to the Employee table and
that will still work when I try to do a querry by last name? I am
sorry if I am being too vague or not catching something here.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Hello,

{Please reply via email... don't get to check this group much}

Below are the tables...

TBL-WorkEvent: WorkEventID, Date, StartTime, EndTime, PostID, OICID,
SupervisorID, CoWorker1, CoWorker2, WorkedAsSupervisor, Notes

TBL-Employee: EmployeeID, LastName, FirstName, Rank

Ok... the fields in WorkEvents tables (OICID, SupervisorID, CoWorker1,
and CoWorker2) are all one-to-many linked to Employee table via
"lookup" datatype on the Workevent table...

This works great while entering data into the WorkEvent table... the
names drop down just fine. But when I go to make a querry to search by
name for any one of the fields (OICID SupervisorID, CoWorker1, and
CoWorker2) by placing [Enter Last Name] in the criteria field of the
querry of any of the mentioned fields I get a result of litterally
nothing... no results, just the headers of the querry.

Sorry if I am missing something fundamental, but I am a self taught
novice.

{Please reply via email... don't get to check this group much}

Thanks,
Charles
charles.cromer[at]gmail.com