Wedge data into an existing query

[Guest]

Hi, I'm having trouble building a query which will merge two tables together.
Both tables contain a identicle field which contains common numerical data.
How ever the one table contains null fields on lines that are immediaely
below the matching fieeld value. There may be several lines below the
matching field, before the next value common to both tables occurs.

eg.
Table 1 Table 2
Field 1 Field 1 Field 2 Field 3
xxxx aaaa
aaaa zzzz yyyy
xxxx zzzz yyyy

Expected output
Table 3
Field 1 Field2 Field3
xxxx
aaaa zzzz yyyy
zzzz yyyy

So far my queries can not account for the null values in table 2. I've even
tried adding data to try and group data in table 2 so that fields 2 & 3 are
each associated to field1. Unfortunately with hundreds of entries, this has
proven too time consuming.

Any ideas would be much appreciated.
Duncan

 

[John Vinson]

Hi, I'm having trouble building a query which will merge two tables together.
Both tables contain a identicle field which contains common numerical data.
How ever the one table contains null fields on lines that are immediaely
below the matching fieeld value. There may be several lines below the
matching field, before the next value common to both tables occurs.

You're in real trouble.

There is no concept of "above" or "below" in an Access table. A table
*is not a spreadsheet*; there are no line numbers, and there is no
defined order to the table. As phrased, unless you have some other
field in the table which defines an order for the records, there is
basically no way to *reliably* do what you ask!

Is this data coming from some external source, such as a text file or
Excel? If so, it may be possible to fill in the nulls during the
import process. If the data has been manually entered... good luck.

John W. Vinson[MVP]

 

[Guest]

Thank you for the reply. I've come up with a solution that works, how ever
it is still not automated and will need a database administrator. I
duplicated fields in table 1 and called it parent. In Table 2, I copied
Fields 1, 2 & 3 into Excel. I then filled in all the null Field1's with the
value of the filled Field1 immediately above it. The result is that each
Field2 & Field 3 that has data in it, gets associated with the same Field1.
Now every Field2 & 3 line is grouped to its own "parent" Field1. I then
added a new field4 and a number sequence which enabled me to maintain the
data order when copying the data back into my original access table, as well
as a Field5 which i also called "parent". I then used a query to match the
parent in Table1 & Table2 together. To get my null fields back, I used a SQL
filter and used a 2 stage sort to properly sequence my data. Now I have all
my Field1 data, with null fields in front of each associated Field2 & 3.

Thank you once again.
Duncan

 

[John Vinson]

To get my null fields back, I used a SQL
filter and used a 2 stage sort to properly sequence my data. Now I have all
my Field1 data, with null fields in front of each associated Field2 & 3.

If you want the null fields there *for presentation purposes*, I'd
suggest storing the (repeating) actual data in your table, and use a
Report to present the data. You can set the properties of a textbox on
a report to hide duplicates.

John W. Vinson[MVP]