Append data from one query to another

[basabnanda]

Hi,


I am in a specific trouble!
I have one summary query with the following fields (query1):
Request Number, Version, SumOfProcessing, SumOfReview
Another query with (query2):
Request Number, Version, Processor, Reviewer, Processing time, Review time
My objective is to create another query with:
Request Number, Version, Processor, Reviewer, Processing time, Review time, PTime, RTime, Ptime remaining, Rtime remaining

Basically, all data from query2 and additionally PTime, RTime, Ptime remaining, Rtime remaining.
PTime should have the data from SumOfProcessing and RTime should have the data from SumOfReview from query1. PTime remaining and RTime remaining should have the difference between Processing time and PTime and Review time and RTime.

All rows from the query2 should be there. Where there is SumOfProcessing and SumOfReview data for corresponding Request Number and Version the PTime and RTime fields will be populated else, the value would be zero.

Please help me with the best solution.

For an example:

Ticket	Version	Processor	Reviewer		Processing_time	Reviewing_time	Status
USDP-0001	1	Basab,Aditi	Anindya		4	2	Assigned
USDP-0001	2	Anindya,Aditi	Shukla		2	1	Assigned
USDP-0002	1	Basab	Shukla		6	2	Delivered
USDP-0003	1	Ram	Shaym		8	2	Assigned

Ticket	Version	SumOfProcessing	SumOfReview
USDP-0001	1	3
USDP-0001	2	2	1
USDP-0002	1	3	1

Output needed:

Ticket	Version	Processor	Reviewer	Processing_time	Reviewing_time	PSpent	RSpent	PTime remaining	RTime remaining
USDP-0001	1	Basab,Aditi	Anindya	4	2	3	0	1	2
USDP-0001	2	Anindya,Aditi	Shukla	2	1	2	1	0	0
USDP-0002	1	Basab	Shukla	6	2	3	1	3	1
USDP-0003	1	Ram	Shaym	8	2	0	0	8	2

Regards,
Basab

[ItsMe]

Maybe you can create a third query object using your previous two queries. You will have to determine which query to use as the query to return all records in an Outer Join on [Ticket].

[basabnanda]

Hi ItsMe, thanks for your suggestion and sorry for my delayed reply. The suggestion you made to create a new query is my objective. It would be much helpful if you please share the practical way to do this, since I have a very limited experience and knowledge in this area.
If you are online now, please let me know and I will test it right away.
Many thanks in advance!

Addition: I am looking for an update query. I have the third query created.

[ItsMe]

You have three dataset examples in your first post. Use the first two examples as their own Query objects. When you create the third query object, drag or add the first two objects to the design area and create a JOIN. As for the UPDATE, I guess you will need a temp table or other table.

If you need to save the third query's retrieved data, I suspect you do not have a Normalized data structure. You can review Relational Database basics to help design a solid foundation of tables/relations.
https://www.accessforums.net/tutoria...ase-45451.html

[basabnanda]

When using join, it is not showing all records from the Q1. What I need is to have a consolidated data set with all records from Q1 and a new field for PSpent and RSpent with data where the Request Number and Version are matching with Q2.

Can anyone provide me a practical solution? I am a novice in this field.

Thanks!

[June7]

Probably because you are using INNER JOIN. Change the join type to "Include all records for Q1 and only those from Q2 that match". This is an OUTER JOIN (LEFT or RIGHT) as indicated by ItsMe in post 2.

[basabnanda]

Thanks June7. I understood that part. But how can I prevent the request number and version to repeat? When using a join, it is returning all data from Q1 and all data from Q2 where there is a matching with Q1. Hence, I now have repeating request number and version in my resulted Q3. I do not want that to happen. I need the number of rows from Q1 to stay unchanged only the PSpent and RSpent fields populated from the Q2.

My be I am not able to make myself clear. I have done this for now with two tables where I have appended the data and then used update query. Is there a smarter way to do this?

Thanks and regards,

[June7]

What is relationship between these tables - 1 to 1 or 1 to many?

Did you join on both common fields - Ticket (or is that RequestNumber) and Version?