Join not Producing the Desired Output

geeta_m9
geeta_m9 used Ask the Experts™
on
I am joining two tables, applications and references, using an outer join on the first name and last name (see attached). The relationship between the two tables is one to many, i.e., for each record in the applications table, there can be zero to two records in the references table. I would like to obtain all the columns from the applications table and the title, first name and last name of the reference and their comments from the references table. Currently i am getting the output as two rows for the same applicant  if they have two references, i.e., one row that has all the applicant's information, their first reference and the reference's comments, and another row that has the same applicant's information again with their second reference and their comments. I would like all the output to appear as a single row. What should I do to  accomplish this?
Join-Condition.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent DyerIT Security Analyst Senior

Commented:
Couple of ways to approach this..

(1) Use Phone numbers
(2) Use SSN Numbers - if possible

If memory serves me right, candace-tripp,com had something alonge these lines..  Or, was it mvps.org/access ?

Try this..
http://www.candace-tripp.net/download/parsenpopulate.zip

HTH,

Kent
To do this in a query you will have to use VBA code to get the references data. There isn't any Join condition you can use in this case.
But it's very messy because of the need to get 10 separate fields (2 X 5) into the result.
It would be possible to create the output in a query by calling the VBA function 10 times (once per field) but I think my inclination would be to create a predefined target table, and then use VBA to update that table. Then use the table rather than a query for whatever your next step is.

It might help the discussion if you would explain what you want to do with the resulting data.
Most Valuable Expert 2012
Top Expert 2013

Commented:
What are you using the query results for?  Are you simply displaying this data in a form?

 If so have you considered using a main form and subform?

 This would display of the data you need without a complicated query.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Yes, I would like to display the data In a main form and subform. How could I accomplish this?
Most Valuable Expert 2012
Top Expert 2013
Commented:
In very simple terms, you need

- a query or table for your main form's  data (the 'one' side of the relationship)
- a query or table for your subforms data  (the 'many' side of the relationship)
- a field to link your main form to your subform (this is typically the primary key in the mainform's data).


This tutorial eaplains/shows in detail how to set this up:
http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx

Author

Commented:
Thanks. Would I be able to edit or make changes to the data in the form or subform or could I only view the data?
Most Valuable Expert 2012
Top Expert 2013

Commented:
If it is set up correctly, you should be able to:

- Edit both mainform and subform data
- Add new subform records, with the parent ID ("Foreign key") automatically included in subform records.
- Use form and/or subform properties to restrict data to read-only if desired

Author

Commented:
I followed your suggestions and got the form and subform to work, thanks. I am also able to edit the records. I have one final question. I do not like the default layout of the subform where I need to scroll to the right to view all fields in it (see attached). I changed the position of the fields in the subform by stacking them vertically in design view. I also tried to make the comments field larger so that more of the content is visible.However when I opened the form in form view, it still shows the default display. Is it possible to adjust the layout so that it  all fits within the screen?
Subform.png
To do that you must use Continuous forms as the default display mode for the subform.

Author

Commented:
Ok, but I don't see it anywhere in the property sheet.
The Format tab of the (sub)form has a Default View property.

Author

Commented:
I found the default view under the Form properties (not the subform) and tried selecting the continuous forms, but it doesn't allow me to do it. It gives me an error message stating that I can't view a form as a continuous form if it contains a subform.

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial