Combining queries and expressions to populate and select info

[TravelingHT]

Dear All
I have essentially customers who have pets and go on trips and each trip has
many visits and at the visits the pets may have different food or medicaiton
at each visit.

So my end problem is how do I make the subform bring back information on
only pets that are owned by the customer, when the ID number for the customer
is not propergated throug all the tables. Do I do it Like this.

From: Entering Customer Information
Based onTable: Customer

From: Entering Pet Information
Based on Query: Customers Pets Information
Based on Froms: Customer informtaion & Pets Information

Form: Entering Trip Information
Based on a Query: Customer Trip
Based onTables: Customer & Table Trip

Form: Entering Visit Info
With Subform: Entering (or rather retreving) Pet Information
With SubSubform: Entering Food at Visit
Based on a Query?
Based on Tables: Pets Information & Visit Information

How do I create a query, if a query is what I need, to bring together only
the info from two separate branches of the tree of info sprouting from the
Customer ID value.

What I want to do is two fold:
1.Only bring up ONLY the pet information from the customer. (This is where I
want to use an expression in a combo box.
2. Populate the other information fields in the forms and sub forms.(This is
where I want to use the querys)

Am I on the completly wrong track. I have been reading but I am none the
wizer on the best way to go:

1. A Query or set of queries based on the tables.

2. An expression in a combo box to reference the Customer ID that is in the
Trip From that contains all of the other sub and sub sub forms

Also if there are articels coving this info online I would LOVE to get the
link.

I have read access 2003 inside out on queries and tables but I still have
trouble conseptualizing this problem and no experience to help decide on best
solution.
Thanks in advance.

 

[bhicks11 via AccessMonster.com]

Here is a link to a pretty good overview of queries:

http://www.ieor.berkeley.edu/~ieor115/labs/Lab7-MSA-Queries-SQL.doc

You have to have something in tables to join on - whether it be an interim
table that two other tables have something in common with or two tables
directly having a field to join on. So if you have:

CustomerID in the Customers Table

PetID in the Pet Table

CustomerID and PetID in the Vacation Table can be in the middle to relate
CustomerID and PetID.

To simplify - you could create a query with two tables that join on a related
field and then use the query as a data source for a second query that
includes another table that is related to one of the field in the first query.


Types of joins:

http://office.microsoft.com/en-us/access/HA010345551033.aspx

Bonnie
http://www.dataplus-svc.com

 

[TravelingHT]

Dear Bonnie:

Thanks for your help, thougth you where a bill Hicks fan from your email
adress then saw the obvious.

So thanks for your help, I understand that I need foreign keys and primary
keys and I have read about meta data and expressions. My problem is that I
have two streams of info comming from customers table.

All I want to do is put a filter on a form combobox that brings pets ID,
wich actually shows the pets name (I can do that part), but filter it so I
only see the pets that are owned by the customer. The sub sub form that I
want to do this on has no CustomerID field and so I want to know.

1. Do I add the expression or Query on the properties of the text box on the
SubSubForm or do I add the expression to the field properites in the
underlying table where I added the info for the combobox.

2. Once I know where and why where to add the query or expression, how do I
tell the computer that depending on the Customer ID entered in the Main form,
I want to only see pets in the sub sub form that belong to this customer.

Thanks in advance.

Traveling Tech(Tech is Histotech, not Tech, Tech)

 

[bhicks11 via AccessMonster.com]

Nope, the only Bill Hicks I know was my father in law.

Sorry, I must have misunderstood your issue.

As the query for the combobox refer to the CustomerID on the form - you would
put this in the customerID criteria:

forms!WhateverForm.customerID

(put your form and whatever the control name is that you input the customerID)


If you your combobox is on a subform of the form that has the customerID, you
refer to it like this:

forms!WhateverForm.WhateverSubform!Form.controlname

This will filter the Pets for only that customerID.

Bonnie
http://www.dataplus-svc.com

Dear Bonnie:

Thanks for your help, thougth you where a bill Hicks fan from your email
adress then saw the obvious.

So thanks for your help, I understand that I need foreign keys and primary
keys and I have read about meta data and expressions. My problem is that I
have two streams of info comming from customers table.

All I want to do is put a filter on a form combobox that brings pets ID,
wich actually shows the pets name (I can do that part), but filter it so I
only see the pets that are owned by the customer. The sub sub form that I
want to do this on has no CustomerID field and so I want to know.

1. Do I add the expression or Query on the properties of the text box on the
SubSubForm or do I add the expression to the field properites in the
underlying table where I added the info for the combobox.

2. Once I know where and why where to add the query or expression, how do I
tell the computer that depending on the Customer ID entered in the Main form,
I want to only see pets in the sub sub form that belong to this customer.

Thanks in advance.

Traveling Tech(Tech is Histotech, not Tech, Tech)

Here is a link to a pretty good overview of queries:

[quoted text clipped - 73 lines]

 

[TravelingHT]

Dear Bonnie:

Thanks for the help.

Here is where I currently am

The record source for the combo box Pets in the Form frmPetsAtVisit is a
query based on my tlbPets.

The query has 3 fields in it PetID PetName and CustomerID:
In the Criteria Row of the CustomerID colum I have:
[Forms]![frmlTrips]![CustomerID] (the misspell of frmlTrips is
intentional)
As built for me by the expression builder by going through and choosing the
form and then the control.

This is not working.
I have also put exactly what you wrote on the post in and I still am not
getting it to work.
The customerID field in the table we are trying to reference is a drop down
list and actually visually shows the customers name but the bound column is
the customerID value i.e.
This value being a foreign key value for the table Customer but is in the
table Trips

In the QUERY
Do I need to put an “=†before the [Forms] in the Criteria Row?
If I add the [Form] after the last form name I get the message essentially
telling me that the computer can not find what I am telling it to find.
Is there something to do with primary keys that is causing a problem?
Do I need to add “on update†or some other action to the drop down list
where the pets name is to be populated.
Should I be messing with the query properties "Unique Values" or "Unique
Records"


Thank you again for all your kind help. I feel as thought I am on the edge
of understanding, but I am rather disappointed with Access2003 inside out. I
have spent a lot of time on that book and something as important as
referencing a value in a field of a control on a form and using the value in
a query is not even glimpsed at there, unless I have missed it. It's not such
a bad book.

Nope, the only Bill Hicks I know was my father in law.

Sorry, I must have misunderstood your issue.

As the query for the combobox refer to the CustomerID on the form - you would
put this in the customerID criteria:

forms!WhateverForm.customerID

(put your form and whatever the control name is that you input the customerID)


If you your combobox is on a subform of the form that has the customerID, you
refer to it like this:

forms!WhateverForm.WhateverSubform!Form.controlname

This will filter the Pets for only that customerID.

Bonnie
http://www.dataplus-svc.com

Dear Bonnie:

Thanks for your help, thougth you where a bill Hicks fan from your email
adress then saw the obvious.

So thanks for your help, I understand that I need foreign keys and primary
keys and I have read about meta data and expressions. My problem is that I
have two streams of info comming from customers table.

All I want to do is put a filter on a form combobox that brings pets ID,
wich actually shows the pets name (I can do that part), but filter it so I
only see the pets that are owned by the customer. The sub sub form that I
want to do this on has no CustomerID field and so I want to know.

1. Do I add the expression or Query on the properties of the text box on the
SubSubForm or do I add the expression to the field properites in the
underlying table where I added the info for the combobox.

2. Once I know where and why where to add the query or expression, how do I
tell the computer that depending on the Customer ID entered in the Main form,
I want to only see pets in the sub sub form that belong to this customer.

Thanks in advance.

Traveling Tech(Tech is Histotech, not Tech, Tech)

Here is a link to a pretty good overview of queries:

[quoted text clipped - 73 lines]

solution.
Thanks in advance.

 

[bhicks11 via AccessMonster.com]

I didn't realize it was a listbox. Try using:

[Forms]![frmlTrips].[CustomerID].Column(0)

This refers to the first item in the data source of listbox or comobox.

Don't need the =.

Bonnie
http://www.dataplus-svc.com

Dear Bonnie:

Thanks for the help.

Here is where I currently am

The record source for the combo box Pets in the Form frmPetsAtVisit is a
query based on my tlbPets.

The query has 3 fields in it PetID PetName and CustomerID:
In the Criteria Row of the CustomerID colum I have:
[Forms]![frmlTrips]![CustomerID] (the misspell of frmlTrips is
intentional)
As built for me by the expression builder by going through and choosing the
form and then the control.

This is not working.
I have also put exactly what you wrote on the post in and I still am not
getting it to work.
The customerID field in the table we are trying to reference is a drop down
list and actually visually shows the customers name but the bound column is
the customerID value i.e.
This value being a foreign key value for the table Customer but is in the
table Trips

In the QUERY
Do I need to put an “=†before the [Forms] in the Criteria Row?
If I add the [Form] after the last form name I get the message essentially
telling me that the computer can not find what I am telling it to find.
Is there something to do with primary keys that is causing a problem?
Do I need to add “on update†or some other action to the drop down list
where the pets name is to be populated.
Should I be messing with the query properties "Unique Values" or "Unique
Records"

Thank you again for all your kind help. I feel as thought I am on the edge
of understanding, but I am rather disappointed with Access2003 inside out. I
have spent a lot of time on that book and something as important as
referencing a value in a field of a control on a form and using the value in
a query is not even glimpsed at there, unless I have missed it. It's not such
a bad book.

Nope, the only Bill Hicks I know was my father in law.

[quoted text clipped - 48 lines]