New to Access

[Guest]

I am brand new to access - it has been plopped on me at work. I have learned
how to link data to access from different sources - now I need a way for
access to ask me a question when I open it - like "enter record number" then
I need it to filter the data and create a report using only the data under
that record number. I can sort of make this work by using an excel sheet to
enter data then create a relationship between that data and the records I
have linked to - I create a report and I have the info I need, but it has to
be easier than that. Please help

Jason

 

[Guest]

Wilke,

Selection of specific records from a recordset is done through queries.
Access has a convenient Query By Example design mode in which you drag fields
of interest from one or more tables to the query grid. You may then set
criteria in one or more fields by entering a value in the Criteria row.

A "parameter" query is one which prompts the user for criteria when the
query is executed. It is created by typing a prompt between brackets in the
Criteria row. So one way to do what you're asking is to base a form on a
parameter query, with a prompt such as [Please enter the record ID:].

Note that a record "number" is not a reliable unique identifier. If you
delete records, the "record number" of those following it will change. Each
table in your database should have a unique primary key that unambiguously
identifies the record.

Another way is to provide an unbound combo box on your data form itself,
often placed in the form header. The combo box presents a list of all the
records in the recordset. You can then use the AfterUpdate event of the
combo box to go to the selected record, using the FindRecord method (see VBA
Help for more information):

' Move focus to the primary key field and find the first value equal to the
combo box
Me![YourPrimaryKeyField].SetFocus
DoCmd.FindRecord Me![YourComboBox].Value

For multiple critieria, a simple way is to provide a criteria form for your
users to select or enter the criteria of interest. Your query then
references the form controls:

= Forms![YourCriteriaForm]![YourCriteriaControl]

Hope that helps.
Sprinks

 

[Guest]

WOW - that really got me going - thanks - and it was easy too! - now that I
can do that I see more possibilities - so more questions. Once I had the
first part that made me think of other needs we have. What I really need is
an indented bill of material - each record or bill has sub bills and some of
those have sub bills, and so on and so on - what I need to do is type in a
record number (which I know how to do now) and it needs to spit a report back
at me that has that bill with each of it's sub bills, all the way to the
bottom - you have been so helpful so far - thank you very much Sprinks.

Wilke_jb

Wilke,

Selection of specific records from a recordset is done through queries.
Access has a convenient Query By Example design mode in which you drag fields
of interest from one or more tables to the query grid. You may then set
criteria in one or more fields by entering a value in the Criteria row.

A "parameter" query is one which prompts the user for criteria when the
query is executed. It is created by typing a prompt between brackets in the
Criteria row. So one way to do what you're asking is to base a form on a
parameter query, with a prompt such as [Please enter the record ID:].

Note that a record "number" is not a reliable unique identifier. If you
delete records, the "record number" of those following it will change. Each
table in your database should have a unique primary key that unambiguously
identifies the record.

Another way is to provide an unbound combo box on your data form itself,
often placed in the form header. The combo box presents a list of all the
records in the recordset. You can then use the AfterUpdate event of the
combo box to go to the selected record, using the FindRecord method (see VBA
Help for more information):

' Move focus to the primary key field and find the first value equal to the
combo box
Me![YourPrimaryKeyField].SetFocus
DoCmd.FindRecord Me![YourComboBox].Value

For multiple critieria, a simple way is to provide a criteria form for your
users to select or enter the criteria of interest. Your query then
references the form controls:

= Forms![YourCriteriaForm]![YourCriteriaControl]

Hope that helps.
Sprinks

I am brand new to access - it has been plopped on me at work. I have learned
how to link data to access from different sources - now I need a way for
access to ask me a question when I open it - like "enter record number" then
I need it to filter the data and create a report using only the data under
that record number. I can sort of make this work by using an excel sheet to
enter data then create a relationship between that data and the records I
have linked to - I create a report and I have the info I need, but it has to
be easier than that. Please help

Jason

 

[Guest]

Wilke,

I'm very familiar with Bills of Material, however, I've never implemented
one in a relational database. I vaguely remember reading years ago that
relational databases are not well suited to BOM, however, I can't remember
why, and I'm certainly no expert. Perhaps someone else can offer some
direction here.

In the meantime, you might search the Forms and Database Design Newsgroups
and Google under the topic. Also, there is a model of a BOM at the following
website that you might find useful:

http://www.databaseanswers.org/data_models/bom/index.htm

Hope that helps.
Sprinks

WOW - that really got me going - thanks - and it was easy too! - now that I
can do that I see more possibilities - so more questions. Once I had the
first part that made me think of other needs we have. What I really need is
an indented bill of material - each record or bill has sub bills and some of
those have sub bills, and so on and so on - what I need to do is type in a
record number (which I know how to do now) and it needs to spit a report back
at me that has that bill with each of it's sub bills, all the way to the
bottom - you have been so helpful so far - thank you very much Sprinks.

Wilke_jb

Wilke,

Selection of specific records from a recordset is done through queries.
Access has a convenient Query By Example design mode in which you drag fields
of interest from one or more tables to the query grid. You may then set
criteria in one or more fields by entering a value in the Criteria row.

A "parameter" query is one which prompts the user for criteria when the
query is executed. It is created by typing a prompt between brackets in the
Criteria row. So one way to do what you're asking is to base a form on a
parameter query, with a prompt such as [Please enter the record ID:].

Note that a record "number" is not a reliable unique identifier. If you
delete records, the "record number" of those following it will change. Each
table in your database should have a unique primary key that unambiguously
identifies the record.

Another way is to provide an unbound combo box on your data form itself,
often placed in the form header. The combo box presents a list of all the
records in the recordset. You can then use the AfterUpdate event of the
combo box to go to the selected record, using the FindRecord method (see VBA
Help for more information):

' Move focus to the primary key field and find the first value equal to the
combo box
Me![YourPrimaryKeyField].SetFocus
DoCmd.FindRecord Me![YourComboBox].Value

For multiple critieria, a simple way is to provide a criteria form for your
users to select or enter the criteria of interest. Your query then
references the form controls:

= Forms![YourCriteriaForm]![YourCriteriaControl]

Hope that helps.
Sprinks

I am brand new to access - it has been plopped on me at work. I have learned
how to link data to access from different sources - now I need a way for
access to ask me a question when I open it - like "enter record number" then
I need it to filter the data and create a report using only the data under
that record number. I can sort of make this work by using an excel sheet to
enter data then create a relationship between that data and the records I
have linked to - I create a report and I have the info I need, but it has to
be easier than that. Please help

Jason

 

[Guest]

Thanks for all your help Sprinks - I can really get going with this - If
anyone has anything else to chime in feel free. Thanks

Wilke,

I'm very familiar with Bills of Material, however, I've never implemented
one in a relational database. I vaguely remember reading years ago that
relational databases are not well suited to BOM, however, I can't remember
why, and I'm certainly no expert. Perhaps someone else can offer some
direction here.

In the meantime, you might search the Forms and Database Design Newsgroups
and Google under the topic. Also, there is a model of a BOM at the following
website that you might find useful:

http://www.databaseanswers.org/data_models/bom/index.htm

Hope that helps.
Sprinks

WOW - that really got me going - thanks - and it was easy too! - now that I
can do that I see more possibilities - so more questions. Once I had the
first part that made me think of other needs we have. What I really need is
an indented bill of material - each record or bill has sub bills and some of
those have sub bills, and so on and so on - what I need to do is type in a
record number (which I know how to do now) and it needs to spit a report back
at me that has that bill with each of it's sub bills, all the way to the
bottom - you have been so helpful so far - thank you very much Sprinks.

Wilke_jb

Wilke,

Selection of specific records from a recordset is done through queries.
Access has a convenient Query By Example design mode in which you drag fields
of interest from one or more tables to the query grid. You may then set
criteria in one or more fields by entering a value in the Criteria row.

A "parameter" query is one which prompts the user for criteria when the
query is executed. It is created by typing a prompt between brackets in the
Criteria row. So one way to do what you're asking is to base a form on a
parameter query, with a prompt such as [Please enter the record ID:].

Note that a record "number" is not a reliable unique identifier. If you
delete records, the "record number" of those following it will change. Each
table in your database should have a unique primary key that unambiguously
identifies the record.

Another way is to provide an unbound combo box on your data form itself,
often placed in the form header. The combo box presents a list of all the
records in the recordset. You can then use the AfterUpdate event of the
combo box to go to the selected record, using the FindRecord method (see VBA
Help for more information):

' Move focus to the primary key field and find the first value equal to the
combo box
Me![YourPrimaryKeyField].SetFocus
DoCmd.FindRecord Me![YourComboBox].Value

For multiple critieria, a simple way is to provide a criteria form for your
users to select or enter the criteria of interest. Your query then
references the form controls:

= Forms![YourCriteriaForm]![YourCriteriaControl]

Hope that helps.
Sprinks


:

I am brand new to access - it has been plopped on me at work. I have learned
how to link data to access from different sources - now I need a way for
access to ask me a question when I open it - like "enter record number" then
I need it to filter the data and create a report using only the data under
that record number. I can sort of make this work by using an excel sheet to
enter data then create a relationship between that data and the records I
have linked to - I create a report and I have the info I need, but it has to
be easier than that. Please help

Jason

 

[Larry Linson]

I am brand new to access - it has been plopped on me at work. I have
learned
how to link data to access from different sources - now I need a way for
access to ask me a question when I open it - like "enter record number"
then
I need it to filter the data and create a report using only the data under
that record number. I can sort of make this work by using an excel sheet
to
enter data then create a relationship between that data and the records I
have linked to - I create a report and I have the info I need, but it has
to
be easier than that. Please help

Can I suggest some self-study books? It's amazing how incredibly easier it
is to get started with a decent text than just by stumbling around and
trying to find the appropriate pages in Help.

Microsoft Access Step by Step, from Microsoft Press, for your version of
Access. This is great for the brand new user, but doesn't delve too deeply,
so once you are a comfortable end-user, you will want something that goes
farther long.

Microsoft Access 2003 Inside-Out, by John Viescas, or Microsoft Access 2007
Inside-Out, by John Viescas and Jeff Conrad, also from Microsoft Press,
starts at the beginning, probably assumes a bit more general computer
knowledge, and goes deeper into Intermediate/Advanced areas.

Special Edition Using Microsoft Access, by Roger Jennings, from Que... an
alternative to the Inside-Out books.

Larry Linson
Microsoft Access MVP