Collecting and unifying same data from two tables

[Bresart]

Hi, i have a table in which two of the fields are

- CropGroup
- CropSubgroup

Each crop subgroup belongs to a crop group, which can hold one or more crop subgroups.

The two respective values can be repeated in the table, that is, there can be several records with the same CropGroup value, and that also occurs for the CropSubgroup value.

But each CropGroup and CropSubgroup values can have a development: several meanings from different bibliography (that means several records for each value), kept in two more tables: tblCropGroup and tblCropSubgroup (there can be developed crop subgroups while the respective crop group isn't developed, and viceversa). There's a third table, tblCropGroupSubgroup, with all the group-subgroup pairs in the database and a field named 'IdGrSgr' that is an autonumeric value for each of them which is the main key, and this field has a relationship 'one to several' with both tblCropGroup and tblCropSubgroup tables.

I need to show in a form and a report only the pairs in which either the crop group or the crop subgroup is developed.

For that, i have tried:

- collecting separately the IdGrSgr values from the table tblCropGroup and from the table tblCropSubgroup in a query based in two respective auxiliar queries, but the records given are

tblCropGroup.recordsNumber x tblCropSubgroup.recordsNumber

and the IdGrSgr values are repeated.

- collecting separately the IdGrSgr values in a new table which is filled with two inserting queries.

Is there a better handling or performing way, that is, obtainining in an unique field of a query all the IdGrSgr values corresponding to both developed crop groups and developed crop subgroups?

Thanks in advance.

 

[lespaul]

can you provide some sample data and expected results?
[tt]
TableName
Field1 Field2 Field3
data data data
data data data
data data data
data data data

(any other table that is needed for the calculation)

Expected Results
Field1 Field2 Field3
data data data
data data data[/tt]

it's much easier to figure out what you need when we can see the whole "picture" and not try to decipher the business requirements....

Thanks

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Bresart]

Thanks lespaul. I have gone around it with the SQL query

SELECT tblCropSubgroup.IdGSg
FROM tblCropSubgroup UNION Select tblCropGroup.IdGSg FROM tblCropGroup;