By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,545 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Join Table by Date Match

P: 36
I have 2 tables that are, for the sake of description, nearly Identical.

The only Difference is that 1 has [Test Date] and the other has [Work Date] which are always different.

Is there a way to combine the information where if [Work Date] < [Test Date]?

So that I can have both [Test Date] and [Work Date] in the same Row of Data.

For Instance:

Table 1:

[Fish][2:00PM][Bob]
[Fish][3:00PM[Mike]

Table 2:

[Fish][2:45PM][Angel]
[Fish][3:15PM][Jose]

To Create a 3rd Table:

[Fish][2:00PM][Bob][2:45PM][Angel]
[Fish][3:00PM[Mike][3:15PM][Jose]

Any suggestions would be appreciated.
May 27 '08 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
Hi. In SQL there is no simple solution to what you require, which is a form of pivoting of multiple columns from row data to become additional columns in particular rows instead.

In Access it is possible to do very limited pivoting using crosstab queries, but these operate on just one column pivoted from the row data, not multiple columns as you show, and column headers are created from all of the pivoted columns, not just those that are in one particular group at a time. That is, to take the times as an example, an Access crosstab query would create columns for every distinct time in the row data, not just the times which match the particular group involved.

It would be possible to do what you ask using VBA code to read two tables simultaneously and pivot the data according to your requirements, but specifying, building and testing this is a fairly involved process and would require much thought as to the output format (into a custom table, or exported to Excel, or text-delimited - there are many choices).

When faced with something like this I tend to look at what is required and rethink the problem, as there are often simpler and better solutions available by thinking afresh on the problem.

-Stewart
May 28 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.