Query/Table Autolookup within another Table

[Guest]

Hi. I know that this is possible, but I can't seem to get it to work.

I have a table called "House Plans", which has, among other things the Plan
number (the Unique key for the table), the Description and the SqFt.

I have built a query - "House Plan Description Query". In this query is
three fields - Plan, Description, SqFt. I have tried it with the Criteria
[Plan No] in the "Plan" field and without.

I also have a Table called "Tracking". Along with the other information
required for each entry, when I put in the Plan number, I would like the next
two fields, namely Description and SqFt to fill automatically. This
information is required when I export this transaction/entry to the Invoice
when it's to be printed out.

How do I connect the query to the Table to make this happen? Ideally, I
would like to type in the "Plan No" in the tracking table, and have the next
two columns automatically fill, but I'd be ok with a pulldown list being
created too - although it shouldn't be necessary since each "Plan No" is
unique. I have attempted to create this with an autolookup, but it isn't
initiating the query, so I'm not getting any results back in the "Tracking"
table's corresponding columns.

Thanks for you help.

Jennifer

 

[Jeff Boyce]

Jennifer

You want to duplicate data in your Tracking table, when it is already
recorded in [House Plans]?

This is absolutely necessary, ... but only if you're using a spreadsheet.

Since Access is a relational database, use the information you already have
in [House Plans], via a query, to display those two fields. Don't re-enter
them (manually or automatically).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff.

First off, I put what I thought was two separate questions to two separate
groups. You see it as all one issue. So,

How would you query the entry in the "Tracking" table and the required
information in the "House Plan" table at the same time, and export them
programatically to Excel? Would it be a step by step progression? First,
to query the last entry in the "Tracking" table, then based on that result,
Query the "House Plan" table for the Description and SqFt information, then
export it to Excel? If that is the case, I can make that work and put it
into a Macro, but then the question is how do you programatically send it to
Excel?

Thanks for clarifying so far. I appreciate the help.

Jennifer

Jennifer

You want to duplicate data in your Tracking table, when it is already
recorded in [House Plans]?

This is absolutely necessary, ... but only if you're using a spreadsheet.

Since Access is a relational database, use the information you already have
in [House Plans], via a query, to display those two fields. Don't re-enter
them (manually or automatically).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi. I know that this is possible, but I can't seem to get it to work.

I have a table called "House Plans", which has, among other things the
Plan
number (the Unique key for the table), the Description and the SqFt.

I have built a query - "House Plan Description Query". In this query is
three fields - Plan, Description, SqFt. I have tried it with the Criteria
[Plan No] in the "Plan" field and without.

I also have a Table called "Tracking". Along with the other information
required for each entry, when I put in the Plan number, I would like the
next
two fields, namely Description and SqFt to fill automatically. This
information is required when I export this transaction/entry to the
Invoice
when it's to be printed out.

How do I connect the query to the Table to make this happen? Ideally, I
would like to type in the "Plan No" in the tracking table, and have the
next
two columns automatically fill, but I'd be ok with a pulldown list being
created too - although it shouldn't be necessary since each "Plan No" is
unique. I have attempted to create this with an autolookup, but it isn't
initiating the query, so I'm not getting any results back in the
"Tracking"
table's corresponding columns.

Thanks for you help.

Jennifer

 

[Jeff Boyce]

If you have a common field between the tables, use a query, joining the two
tables on the common field. Add the fields you want to see from each.

Once your query is returning the information you want, go back to the
database window and use File | Export (with the query highlighted) to export
the info to Excel.

If you want to take on the challenge, you can do this through code, using
automation to open an Excel spreadsheet and "push" the data in -- not a
trivial exercise, though.

If all you really want is an Excel spreadsheet with the results of the query
in it, you can build the query and use a macro to do the File | Export
process...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff.

First off, I put what I thought was two separate questions to two separate
groups. You see it as all one issue. So,

How would you query the entry in the "Tracking" table and the required
information in the "House Plan" table at the same time, and export them
programatically to Excel? Would it be a step by step progression?
First,
to query the last entry in the "Tracking" table, then based on that
result,
Query the "House Plan" table for the Description and SqFt information,
then
export it to Excel? If that is the case, I can make that work and put it
into a Macro, but then the question is how do you programatically send it
to
Excel?

Thanks for clarifying so far. I appreciate the help.

Jennifer

Jennifer

You want to duplicate data in your Tracking table, when it is already
recorded in [House Plans]?

This is absolutely necessary, ... but only if you're using a spreadsheet.

Since Access is a relational database, use the information you already
have
in [House Plans], via a query, to display those two fields. Don't
re-enter
them (manually or automatically).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi. I know that this is possible, but I can't seem to get it to work.

I have a table called "House Plans", which has, among other things the
Plan
number (the Unique key for the table), the Description and the SqFt.

I have built a query - "House Plan Description Query". In this query
is
three fields - Plan, Description, SqFt. I have tried it with the
Criteria
[Plan No] in the "Plan" field and without.

I also have a Table called "Tracking". Along with the other
information
required for each entry, when I put in the Plan number, I would like
the
next
two fields, namely Description and SqFt to fill automatically. This
information is required when I export this transaction/entry to the
Invoice
when it's to be printed out.

How do I connect the query to the Table to make this happen? Ideally,
I
would like to type in the "Plan No" in the tracking table, and have the
next
two columns automatically fill, but I'd be ok with a pulldown list
being
created too - although it shouldn't be necessary since each "Plan No"
is
unique. I have attempted to create this with an autolookup, but it
isn't
initiating the query, so I'm not getting any results back in the
"Tracking"
table's corresponding columns.

Thanks for you help.

Jennifer

 

[Guest]

Thanks Jeff. I guess I was looking at it as a bigger issue than it is. I
will try to remember the KISS method from now on. And I'll work on the
macro in my "spare" time - until then I can do it manually as you said.

Jennifer

If you have a common field between the tables, use a query, joining the two
tables on the common field. Add the fields you want to see from each.

Once your query is returning the information you want, go back to the
database window and use File | Export (with the query highlighted) to export
the info to Excel.

If you want to take on the challenge, you can do this through code, using
automation to open an Excel spreadsheet and "push" the data in -- not a
trivial exercise, though.

If all you really want is an Excel spreadsheet with the results of the query
in it, you can build the query and use a macro to do the File | Export
process...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff.

First off, I put what I thought was two separate questions to two separate
groups. You see it as all one issue. So,

How would you query the entry in the "Tracking" table and the required
information in the "House Plan" table at the same time, and export them
programatically to Excel? Would it be a step by step progression?
First,
to query the last entry in the "Tracking" table, then based on that
result,
Query the "House Plan" table for the Description and SqFt information,
then
export it to Excel? If that is the case, I can make that work and put it
into a Macro, but then the question is how do you programatically send it
to
Excel?

Thanks for clarifying so far. I appreciate the help.

Jennifer

Jennifer

You want to duplicate data in your Tracking table, when it is already
recorded in [House Plans]?

This is absolutely necessary, ... but only if you're using a spreadsheet.

Since Access is a relational database, use the information you already
have
in [House Plans], via a query, to display those two fields. Don't
re-enter
them (manually or automatically).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi. I know that this is possible, but I can't seem to get it to work.

I have a table called "House Plans", which has, among other things the
Plan
number (the Unique key for the table), the Description and the SqFt.

I have built a query - "House Plan Description Query". In this query
is
three fields - Plan, Description, SqFt. I have tried it with the
Criteria
[Plan No] in the "Plan" field and without.

I also have a Table called "Tracking". Along with the other
information
required for each entry, when I put in the Plan number, I would like
the
next
two fields, namely Description and SqFt to fill automatically. This
information is required when I export this transaction/entry to the
Invoice
when it's to be printed out.

How do I connect the query to the Table to make this happen? Ideally,
I
would like to type in the "Plan No" in the tracking table, and have the
next
two columns automatically fill, but I'd be ok with a pulldown list
being
created too - although it shouldn't be necessary since each "Plan No"
is
unique. I have attempted to create this with an autolookup, but it
isn't
initiating the query, so I'm not getting any results back in the
"Tracking"
table's corresponding columns.

Thanks for you help.

Jennifer

 

[Jeff Boyce]

Jennifer

I've been known to ignore the subtleties and just grab a bigger hammer
<g>...

If I can get it working, somehow, I can always look for "enhancements" and
"elegance" as time allows.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff. I guess I was looking at it as a bigger issue than it is. I
will try to remember the KISS method from now on. And I'll work on
the
macro in my "spare" time - until then I can do it manually as you said.

Jennifer

If you have a common field between the tables, use a query, joining the
two
tables on the common field. Add the fields you want to see from each.

Once your query is returning the information you want, go back to the
database window and use File | Export (with the query highlighted) to
export
the info to Excel.

If you want to take on the challenge, you can do this through code, using
automation to open an Excel spreadsheet and "push" the data in -- not a
trivial exercise, though.

If all you really want is an Excel spreadsheet with the results of the
query
in it, you can build the query and use a macro to do the File | Export
process...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff.

First off, I put what I thought was two separate questions to two
separate
groups. You see it as all one issue. So,

How would you query the entry in the "Tracking" table and the required
information in the "House Plan" table at the same time, and export them
programatically to Excel? Would it be a step by step progression?
First,
to query the last entry in the "Tracking" table, then based on that
result,
Query the "House Plan" table for the Description and SqFt information,
then
export it to Excel? If that is the case, I can make that work and put
it
into a Macro, but then the question is how do you programatically send
it
to
Excel?

Thanks for clarifying so far. I appreciate the help.

Jennifer


:

Jennifer

You want to duplicate data in your Tracking table, when it is already
recorded in [House Plans]?

This is absolutely necessary, ... but only if you're using a
spreadsheet.

Since Access is a relational database, use the information you already
have
in [House Plans], via a query, to display those two fields. Don't
re-enter
them (manually or automatically).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi. I know that this is possible, but I can't seem to get it to
work.

I have a table called "House Plans", which has, among other things
the
Plan
number (the Unique key for the table), the Description and the SqFt.

I have built a query - "House Plan Description Query". In this
query
is
three fields - Plan, Description, SqFt. I have tried it with the
Criteria
[Plan No] in the "Plan" field and without.

I also have a Table called "Tracking". Along with the other
information
required for each entry, when I put in the Plan number, I would like
the
next
two fields, namely Description and SqFt to fill automatically. This
information is required when I export this transaction/entry to the
Invoice
when it's to be printed out.

How do I connect the query to the Table to make this happen?
Ideally,
I
would like to type in the "Plan No" in the tracking table, and have
the
next
two columns automatically fill, but I'd be ok with a pulldown list
being
created too - although it shouldn't be necessary since each "Plan
No"
is
unique. I have attempted to create this with an autolookup, but it
isn't
initiating the query, so I'm not getting any results back in the
"Tracking"
table's corresponding columns.

Thanks for you help.

Jennifer