How to calucalte tow records in on record..

[Alwesam4u]

Hello all,

I am trying to run a query in Access 2007 that will return the record that has same field from tow record for a certain field.

For example, I have the following data:

Field 1
Field 2


Field 3
Field 4

In Record #1 I have the following data:

Field 1 - 222
Field 2 - A
Field 3 - empty ''in this field now is empty
Field 4 - 75

In Record #2 I have the following data:

Field 1 - 222
Field 2 - A
Field 3 - ABC
Field 4 - empty ''in this field now is empty

i need the result like this

In Record #3 I have the following data:

Field 1 - 222
Field 2 - A
Field 3 - ABC
Field 4 - 75

How would I do this in the query design?

Any assistance would be greatly appreciated. Thank you for taking the time to read this.

Alwessam

[pkstormy]

If Field1 in both tables has 'unique' values, you could create a query and join these 2 fields (they'll need to be the same data type/length). Then it's a matter of adding the fields to show. I'd run a 'Check for Duplicates' (via the query wizard) on this field for each table to make sure you have unique values only. If any values are duplicated, it would not show accurately in your query.

If Field1 isn't a good field to join on (it isn't unique in both tables), then you'll need to figure out some way in which to create a unique type field in both tables to join on in a query. This would involve creating a new field in both tables (ex: a text data type called: ConcatenatedValue). And then run a query to update this field in both tables where you would update the field with an expression to concatenate whatever values are needed to create a unique value that could then be used to join both tables on. An example of an expression to concatenate in the update query might look like this: [Field1] & [Field2]. You'll need to determine what fields to concatenate to update the unique field. As a safety measure that they are unique, when adding the fields to the tables, I'd set the Primary Key to this field or for the field's property, set it to 'unique values only (no duplicates)'.