Combining multiple rows for same Id into one row

[marjon18us]

Hello,

I just registered today...

I have a similar problem. this is how my table looks like:

Main Tbl
Org ID CompanyName DateofInc
1234 ABC 1/24
1233 A Com 2/5
1444 TSY 3/53

ValidityTbl
OrgID FieldIssue ValidityConsistency
1444 CompanyName Validity
1444 DateofInc Validity

I want to create a query or table that will lookup all the orgIds in the maintbl in the validity table. it will display a "fail" for a given field if it exist in the validity table and it will blank if it does not exist in validity tbl. Desired output is as follows:

Validityquery
Org ID CompanyName DateofInc
1234 [blank] [blank]
1233 [blank] [blank]
1444 Fail Fail

I have created a query but the output showing is duplicated rows for OAId. I want to display it in a single row for each org id. here is what my query displays:
Validityquery
Org ID CompanyName DateofInc
1234 [blank] [blank]
1233 [blank] [blank]
1444 Fail [blank]
1444 [blank] Fail

I would really appreciate your help. I did this in Excel but the macro was too slow (i will be getting 200 thousand data or more). I believe it is faster in Access.

This is not concatenate. The Fail must be displayed in separate fields or columns.

 

[Anakardian]

Have you tried to use the "group by" option?

If you activate the "totals" function you get the option to group the results. I believe this should give you what you are looking for.

 

[marjon18us]

Thank you. But How will I group the results? to show only "[blank" and Fail for each Org ID? sorry, I only know basics of Access.

 

[marjon18us]

Have you tried to use the "group by" option?

If you activate the "totals" function you get the option to group the results. I believe this should give you what you are looking for.

You gave me an idea. What I did is just to add a condition to show result as 1 if the orgid exist in validity table and 0 if it's not there. Then I grouped it per Org ID and sum the fields. I added a condition statement that if the field is greater than 1 then it will display a Fail.

Thanks a lot!

 

[Anakardian]

Glad to have sparked an idea