I have data on a large number of samples that I want to track with Access.  
To start I created a table (the Sample Table) with each sample as a separate 
record, including some hyperlink fields that point to the location of the 
data on the sample.  So far so good.  Now I need to do pairwise comparisons 
of the sample data.  My plan was to construct a second table (the Comparison 
Table) listing pairs of sample numbers of the samples to be compared.  I 
thought that I would be able to lookup the required information in Sample 
Table, copy it to Comparison Table, and then write a macro to do the 
comparative analysis.  But I haven't found a way to do this.  I can find a 
way to create a table that is a subset of Sample Table using a query, but I 
haven't yet found a way to create a record in Comparison Table that contains 
data from two records of Sample Table.  It seems to me that this sort of 
lookup function should be simple to implement in a relational database 
program, but I can't find any examples of this type of sorting in the online 
help or the tutorials.  Any suggestions?

On Wed, 13 Feb 2008 14:30:03 -0800, JFK <JFK@discussions.microsoft.com> wrote:

>I have data on a large number of samples that I want to track with Access.  
>To start I created a table (the Sample Table) with each sample as a separate 
>record, including some hyperlink fields that point to the location of the 
>data on the sample.  So far so good.  Now I need to do pairwise comparisons 
>of the sample data.  My plan was to construct a second table (the Comparison 
>Table) listing pairs of sample numbers of the samples to be compared.  I 
>thought that I would be able to lookup the required information in Sample 
>Table, copy it to Comparison Table, and then write a macro to do the 
>comparative analysis.  But I haven't found a way to do this.  I can find a 
>way to create a table that is a subset of Sample Table using a query, but I 
>haven't yet found a way to create a record in Comparison Table that contains 
>data from two records of Sample Table.  It seems to me that this sort of 
>lookup function should be simple to implement in a relational database 
>program, but I can't find any examples of this type of sorting in the online 
>help or the tutorials.  Any suggestions?

Well... I think you have it backwards. How on Earth could Access guess which
record you want to compare with which other record????

I would suggest two tables, and what's called a "many to many self join". The
comparison table should contain only two fields, both of the same datatype as
the Sample table's Primary Key. You could then use a Form based on the
comparison table, with two combo boxes to select the two samples to be
compared. Or, you could use a Form based on Samples, with a Subform based on
comparison, in which you could select (again using a combo box) which record
you want to compare.

You also do not need a macro to do the comparison. A Query would be the
appropriate tool.

Perhaps if you could indicate what kind of comparison you want to do, and
indicate how you choose which records are involved in the comparison, we might
be able to make some more useful suggestions.
-- 
             John W. Vinson [MVP]

"John W. Vinson" wrote:

> On Wed, 13 Feb 2008 14:30:03 -0800, JFK <JFK@discussions.microsoft.com> wrote:
> 
> >I have data on a large number of samples that I want to track with Access.  
> >To start I created a table (the Sample Table) with each sample as a separate 
> >record, including some hyperlink fields that point to the location of the 
> >data on the sample.  So far so good.  Now I need to do pairwise comparisons 
> >of the sample data.  My plan was to construct a second table (the Comparison 
> >Table) listing pairs of sample numbers of the samples to be compared.  I 
> >thought that I would be able to lookup the required information in Sample 
> >Table, copy it to Comparison Table, and then write a macro to do the 
> >comparative analysis.  But I haven't found a way to do this.  I can find a 
> >way to create a table that is a subset of Sample Table using a query, but I 
> >haven't yet found a way to create a record in Comparison Table that contains 
> >data from two records of Sample Table.  It seems to me that this sort of 
> >lookup function should be simple to implement in a relational database 
> >program, but I can't find any examples of this type of sorting in the online 
> >help or the tutorials.  Any suggestions?
> 
> Well... I think you have it backwards. How on Earth could Access guess which
> record you want to compare with which other record????
> 
> I would suggest two tables, and what's called a "many to many self join". The
> comparison table should contain only two fields, both of the same datatype as
> the Sample table's Primary Key. You could then use a Form based on the
> comparison table, with two combo boxes to select the two samples to be
> compared. Or, you could use a Form based on Samples, with a Subform based on
> comparison, in which you could select (again using a combo box) which record
> you want to compare.
> 
> You also do not need a macro to do the comparison. A Query would be the
> appropriate tool.
> 
> Perhaps if you could indicate what kind of comparison you want to do, and
> indicate how you choose which records are involved in the comparison, we might
> be able to make some more useful suggestions.
> -- 
>              John W. Vinson [MVP]
> 
Okay let me give you more details.  The primary key in the Sample Table is 
the sample number.  The Comparison Table has only two fields, both of the 
same type as the sample number.  I tried making a many-to-many type 
relationship in a variety of ways, but none of them will produce a table, or 
at least I haven't learned how to do this yet.  I don't want to do the 
comparison in a form. I need a report describing which samples have been 
compared in tabular format, and the analysis needs to be done in Excel as 
described below.

As I mentioned, the Sample Table has hyperlinks to the location of the data. 
 The data for each sample consists of 10 spectral files, each of which 
contains about 1000 data points.  I need to compare 2 sets of 10 spectra 
using multivariate statistics.  I do this with existing VBA code in Excel.  
My problem is that the data has become so extensive that I need a database to 
keep track of it.  My plan is to have a record for each sample in the Sample 
Table, including hyperlinks to the location of the data for the sample.  The 
Comparison Table specifies which pairs of data need to be compared.  (These 
are specific pairs, it's not like a permutation problem.  So I enter the 
sample numbers into the Comparison Table manually.)  Ideally this will look 
like a table whose records include two sample numbers (samples to be 
compared) and two hyperlinks to the location of the data along with some 
other information.  Then I plan to create an interface between Access and 
Excel so that I can simply select a record from the Comparison Table and 
initiate the statistical analysis with a dropdown menu or a command button.  
At the moment I have to navigate through lots of folders to find the two data 
sets to compare.

Maybe I can't generate a table within Access, and I need to be satisfied 
with creating tabular reports in Excel, but if there is a way to do it in 
Access, I'd like to know about it.

On Wed, 13 Feb 2008 18:34:01 -0800, JFK <JFK@discussions.microsoft.com> wrote:

>Okay let me give you more details.  The primary key in the Sample Table is 
>the sample number.  The Comparison Table has only two fields, both of the 
>same type as the sample number.  I tried making a many-to-many type 
>relationship in a variety of ways, but none of them will produce a table, or 
>at least I haven't learned how to do this yet.  I don't want to do the 
>comparison in a form. I need a report describing which samples have been 
>compared in tabular format, and the analysis needs to be done in Excel as 
>described below.

Ok, that's a bit clearer.

>As I mentioned, the Sample Table has hyperlinks to the location of the data. 
> The data for each sample consists of 10 spectral files, each of which 
>contains about 1000 data points.  I need to compare 2 sets of 10 spectra 
>using multivariate statistics.  I do this with existing VBA code in Excel.  
>My problem is that the data has become so extensive that I need a database to 
>keep track of it.  My plan is to have a record for each sample in the Sample 
>Table, including hyperlinks to the location of the data for the sample.  The 
>Comparison Table specifies which pairs of data need to be compared.  (These 
>are specific pairs, it's not like a permutation problem.  So I enter the 
>sample numbers into the Comparison Table manually.)  

That's what I was wondering. If you enter the records manually then you...
just create a table with the two fields, and enter the record numbers
manually! You don't need to have Access create the table from the sample table
(it CAN'T, for that very reason - *you* know which pairs you want, Access
doesn't). You may be using the term "table" in a way that is different than
usual in Access. 

>Ideally this will look 
>like a table whose records include two sample numbers (samples to be 
>compared) and two hyperlinks to the location of the data along with some 
>other information.  

Create a Query by adding the Sample table, the Comparison table (joining the
sample number in the Sample table to the first comparison table field), and
then adding the Sample table a second time, joining to the second comparison
table field. Select the Hyperlink fields from both instances of the sample
table, along with whatever other information you want to see. It is neither
necessary nor appropriate to copy the hyperlinks into the comparison table;
that's what queries are for.

>Then I plan to create an interface between Access and 
>Excel so that I can simply select a record from the Comparison Table and 
>initiate the statistical analysis with a dropdown menu or a command button.  
>At the moment I have to navigate through lots of folders to find the two data 
>sets to compare.

You can then base a Report on the query, showing both hyperlinks or the files
referenced by the hyperlinks; or, if you prefer, you can use File... Export to
export the Query to a workbook in Excel.

>Maybe I can't generate a table within Access, and I need to be satisfied 
>with creating tabular reports in Excel, but if there is a way to do it in 
>Access, I'd like to know about it.

You can do either, very easily.
-- 
             John W. Vinson [MVP]