relationship between query and table

[dumbledown]

I have a table (tblALL) that has many fields in it.
I have another (querySOME) that has many fields in it.
Both have fields that correspond (ALL_CODE and SOME_CODE).

Can anyone point me in the right direction in working out how to adapt tblALL to include all records from querySOME where ALL_CODE=SOME_CODE? querySOME will change from use to use and it would be great if tblALL could be updated accordingly.



Many thanks

Hugh

[June7]

Don't understand. Sounds suspiciously like duplication of data. Why would querySOME change per user? This is not a split db and each user has their own copy of project to work with? How will you manage design edits?

[dumbledown]

Thanks June7
Yes, it is duplication of data I guess.
I want to produce a final table with all the records in tblALL containing all the measurements or outputs for querySOME. This is so I can link this final table to another program and use all this data.

So, if tblALL is:

ID	ref
1	2
2	43
3	12z

and querySOME is something like:

ref	output1	output2	output3	output4
43	yes	100	0.87	ad
2	no	150	0.83	po
17	no	872	0.65	ra
12z	yes	211	0.98	qt

I want to have a table ready to link to another program as:

ID	ref	output1	output2	output3	output4
1	2	no	150	0.83	po
2	43	yes	100	0.87	ad
3	12z	yes	211	0.98	qt

Hope that's a little bit clearer?
Thanks

[June7]

Ref 17 is not in tblALL so you don't want it in the final output? If it is called tblALL why doesn't it have all?

You need to have a table for linking to what program?

If tblALL doesn't have the Output fields, how can it be 'updated' with data from querySOME? A query joining the tables will effectively giving the output shown in the third table. That query can be exported or perhaps the other program can link to the tables and build its own query.

[dumbledown]

Forget about Ref17 and the naming convention, it's not an issue.
The table will be linked to a GIS mapping piece of software - I've successfully linked other tables and queries from access so this is not a problem.
A query joining the tables would be ideal, it could be called queryOUTPUT.
I guess this is my question really, how do I make this queryOUTPUT joining the two tables?
thanks
June7

[recyan]

Till June's back, try an INNER join query on the two tables, with the join on field ref.

Thanks

[dumbledown]

Thanks recyan - I was trying to be too complicated, that's great for now.
Cheers