Make table from table

[JFK]

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?

 

[John W. Vinson]

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.

 

[JFK]

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.

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.

 

[John W. Vinson]

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.