Joining 2 queries.

[Gkynard]

Kind of new to access. I have 2 queries created from the same table. One query is pertinent patient info name, acct, etc... about 12 fields. The second query is patient name and subtotals for each individual patient. I am trying to join these queries to create one recordsource for my report where I can grab fields from both queries.

I have tried to make it simple but I have been working and seaching for 2 weeks to no avail.

Table(rejections)
query(rejections "source table rejections") (subtotals "source table rejections")
form(rejections "source rejections query")
report(all rejections "source rejections query")

Thanks for any help.

 

[FluffyKitten]

First of all, I don't understand why you are unable simply to use the table as the source for your report, if the queries have gathered their information it will be stored right back into the originating table.

If you really want to go down the road of creating a new query, as both queries use the same table it shouldn't be too difficult to add a common field that can be used as the relationship for your new query. If it happens that the field doesn't need to be displayed just untick the display box in the query.

 

[SimonB1978]

Hi,

Do you have some kind of ID in both queries? Something like patient ID or number? If so the syntax for your record source would look like this:

SELECT * FROM query_1 INNER JOIN query_2 ON query_1.ID = query_2.ID

Hope that helps,

Simon B.

 

[Gkynard]

Fluffy the subtotal query doesn't store the subtotal back into the table. My objective is to sort by subtotal in a descending fashion in my report. From what I have researched the only way to accomplish this is by creating a subtotal query joining it to my rejection query. When I go to sorting and grouping I can then select (Sum of Total field) from the drop down because it is included in the recordsource. **Please if there is an easier way let me know.

Simon thanks I can navigate my way around a little bit in access. When I go into the SQL editor for a new query and post this code.

SELECT * FROM [All Rejections Query] INNER JOIN [Totals Query] ON All Rejections Query.ID = Totals Query.ID

I get an error message Syntax Error missing operator in query experssion 'All Rejections Query.ID = Totals Query.ID' am I not writing the code correctly.

Thanks Again

 

[SimonB1978]

Hi,

Missing brackets (in red)

SELECT * FROM [All Rejections Query] INNER JOIN [Totals Query] ON [All Rejections Query].ID = [Totals Query].ID

BTW, a good practice is to NOT use spaces in object names.

Simon B.

 

[Gkynard]

Thanks for the tip Simon. Also thank you for helping me accomplish that task as it has worked. But it has created a different problem. Everything on the report is displaying correctly, the problem is now when I go to the form to edit a record in my form it tells me I can't update the record. Any Ideas?

Access seems to be harder than I thought it would be.

 

[Galaxiom]

Recordsets which incorporate Totals queries are not updateable.

 

[Gkynard]

Didn't know that thanks for all your help.

Glenn

 

[Galaxiom]

Access seems to be harder than I thought it would be.

Welcome to Access, Glenn. Yes it is hard and you have just begun to scratch the surface. It continues to be hard for a long while yet. But it can be very rewarding because almost anything (and by this I mean including controlling Windows itself) can be done.

I remember well the first time I discovered non updateable recordsets. A whole new world of stuff had to be learnt. But keep at it and eventually the little pieces of knowledge begin to fit together and one day the patterns merge into a deeper understanding.

 

[Gkynard]

Funny thing Galax is I took Access in college and figured I could do something simple to be more efficient at the job. It feel that I have learned more in these past 2 weeks about Access than in that whole semester I took. I am learning though and enjoying it gives me an opportunity to hone my skills and become better just addeding another skill set. Thanks