How do I restrict my results?

[Alex L Pavluck]

Hello. I have my data setup as follows:

Main
MEMNAME NAME TYPE LABEL

Variable
MEMNAME VARNAME NUMBER

Format
MEMNAME FMTVALUE NUMBER

The MEMNAME variable is the source dataset. The 3 tables are linked by
MEMNAME and MAIN.NAME -->Variable.VARNAME --> Variable.NUMBER-->
Format.NUMBER.

Now I have a query that will search the Main table and return the value
based on a "Like" query. I have a subquery that will display the
format for that variable. The problem is that I can't get it so that
the subquery only displays the FMTVALUE if it matched by name **AND BY
MEMNAME**

Here is a screenshot of results:
http://www.mindspring.com/~a.pavluck/access.JPG

[Ed Robichaud]

Well for starters, lets rename ALL your tables and fields. "Variable",
"Format", "Number", etc. are all reserved words in Access/VBA that means
something and should NOT be used as object names. You're courting disaster
(or at very least deep confusion) by using them for your own names. Try
something like:

tblSurvey (old "Main")
SurveyID -autonumber (primary key)
Memname - text (whatever this is)
SurveyName - text (old "Name")
MyType -text (old "Char")
etc.

tblAnswers
AnswerID - number (0-99) or text (A-ZZ) your choice -primary key
AnswerDescription - text

tblResults
ResultID -autonumber -primary key
SurveyID -foreign key (related to tblSurvey)
AnswerID -foreign key (related to tblAnswers)
ResultDate
etc.

A normalized database eliminates repeating groups and redundant data and
then makes each record dependant on the key.

Now to answer your question: Look at the Access help for the "Like"
operator syntax. You'll need something like:

Like("myText*")

-Ed

"Alex L Pavluck" <ap******@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Hello. I have my data setup as follows:

Main
MEMNAME NAME TYPE LABEL

Variable
MEMNAME VARNAME NUMBER

Format
MEMNAME FMTVALUE NUMBER

The MEMNAME variable is the source dataset. The 3 tables are linked by
MEMNAME and MAIN.NAME -->Variable.VARNAME --> Variable.NUMBER-->
Format.NUMBER.

Now I have a query that will search the Main table and return the value
based on a "Like" query. I have a subquery that will display the
format for that variable. The problem is that I can't get it so that
the subquery only displays the FMTVALUE if it matched by name **AND BY
MEMNAME**

Here is a screenshot of results:
http://www.mindspring.com/~a.pavluck/access.JPG

[pietlinden]

First of all, get rid of the Varname rubbish. Assign at least
meaningful names to primary keys and foreign keys. Why do you need a
subquery at all? Why not join the two tables?
I think this is only hard because you're making it that way...

[Alex L Pavluck]

I used these names just to illustrate the problem. If you don't have a
solution to provide then please don't respond.

Again the problem is that I need to build a sub-query that will join on
*2* variables not just 1 from the master and child.

[Ed Robichaud]

The point is, you won't get any responses if you insist on your own
confusing terminology as no one will understand what you want. The fact
that you want to filter on two fields (not "variables", by the way -those
are values held in memory) whose values are identical suggests that your
data is not structured and related correctly - as I tried to point out.

But ignoring all that, to set the query criteria you request, use the AND
operator.
An example, using data from two separate table to filter data in a third :

SELECT tblAgencies.Agency, tblAgencies.AgencyID
FROM tblAgencies, tblAgencyDonation, tblAgencyPledge
WHERE (((tblAgencies.AgencyID)=[tblAgencyDonation].[AgencyID] AND
(tblAgencies.AgencyID)=[tblAgencyPledge].[AgencyID]));
The SQL to get the same result from three related tables:

SELECT tblAgencies.Agency, tblAgencies.AgencyID
FROM (tblAgencies INNER JOIN tblAgencyDonation ON tblAgencies.AgencyID =
tblAgencyDonation.AgencyID) INNER JOIN tblAgencyPledge ON
tblAgencies.AgencyID = tblAgencyPledge.AgencyID;

-Ed

"Alex L Pavluck" <ap******@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

I used these names just to illustrate the problem. If you don't have a
solution to provide then please don't respond.

Again the problem is that I need to build a sub-query that will join on
*2* variables not just 1 from the master and child.

[Alex L Pavluck]

Ok, your point is well taken.

I can not figure out how to view my subquery in SQL. I have a query
and I used the insert menu to insert a subquery (which is just another
query). At that time it asked to to link the query results with 1
variable. That is the problem, I need to link on 2 variables.

[Alex L Pavluck]

Here is the solution for anyone who is interested:

Modify Subdatasheet:
Note If you want to specify two or more fields in the Link Child
Fields or Link Master Fields box, enter the field names separated by a
semicolon (;)- for example, Last Name;First Name.

[Ed Robichaud]

I think we're still confused.

The http://www.mindspring.com/~a.pavluck/access.JPG
image you sent shows a TABLE in datasheet view with a SUBDATASHEET open.
This is an Access "feature" that lets you link data tables for viewing in
this spreadsheet style view. Nearly all developers and good applications
ignore this feature (for good reasons) and depend on well designed forms
(based on queries) to show related information.

Contrast that to a QUERY which is a dataset of one or more related tables
and/or other queries. It allows you to filter and sort (Select query) and
add records (Append query), Update, etc. Open/create a new query in design
mode. Add the tables or (queries) you want; create links (joins) between
them by drag & drop of the related fields.
Enter the filter criteria and/or sort order you need. View the generated
SQL statement or datasheet results by using the View menu. If all this is
strange to you, use the query wizard and read through the Access Help to get
you started in query design.

If you still need/want your linked subdatasheet,(and I can't recommend it)
then link to your new query that has already done the selection on multiple
criteria/ that you needed.

Good luck
-Ed
"Alex L Pavluck" <ap******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Ok, your point is well taken.

I can not figure out how to view my subquery in SQL. I have a query
and I used the insert menu to insert a subquery (which is just another
query). At that time it asked to to link the query results with 1
variable. That is the problem, I need to link on 2 variables.