Multiple rows into 1 and creating new columns.

[undefeatedskillz26]

I have a query which is used to pull data from 2 tables. A few things I am looking to do.
1. Only show the records that have 2 or more same Pat#, FName and LName. If it is a single record I would like to hide it.
2. Need to see the different dates amd know the difference.

Currently Query Looks like this.
Pat# FName LName Reason Status Date
00001 John Doe 1 1 11/13/2014 00002 Sally Doe 2 1 11/25/2014
00003 Bill Bates 1 1 11/04/2014
00003 Bill Bates 2 1 11/07/2014
00004 Jenny West 1 1 04/03/2014
00004 Jenny West 2 1 04/10/2014

The Signup reason number 1 represents the signup date and number 2 represents the time they left. SignupStatus number 1 represents a group.

So I am hoping my end goal it would look something like this.
Pat# FName LName Date 1 Date 2 Difference
00003 Bill Bates 11/04/2014 11/07/2014 3 Days
00004 Jenny West 04/03/2014 04/10/2014 7 days

I am afraid at my age I do not know much coding so I am trying to avoid using VBA or SQL. Honestly I do not know where to input it. I have no other choice I guess I am forced to do it as long as I know where to put the code.
I think if use criteria in the query it can help. Maybe expression builder as well. I tried to use the “Find Duplicates” query wizard but this did not help because it finds duplicates like last name first name, but it does not separate the dates. Can someone please help. I can give you my cell number as well just trying to understand this without getting frustrated. Thanks for any help anyone can do. Much appreciated.
P.S - If I am not clear please ask questions I will be online to answer anything I can. I am using MS Access 2010. Many many thanks everyone.

 

[Frothingslosh]

Just for the record, saying you're using Access but avoiding VBA and SQL is much like saying you're playing baseball but avoiding batting and throwing.

What you want to do will involve 'Sorting and Grouping' your query. Also, there is a built-in query wizard that allows you to create a 'Find Duplicates' query.

Looking into the two items I mentioned will get you going in the right direction.

 

[undefeatedskillz26]

Just for the record, saying you're using Access but avoiding VBA and SQL is much like saying you're playing baseball but avoiding batting and throwing.

What you want to do will involve 'Sorting and Grouping' your query. Also, there is a built-in query wizard that allows you to create a 'Find Duplicates' query.

Looking into the two items I mentioned will get you going in the right direction.

Thank you very much Frothingslosh. I am thankful for your reply. I know I need to know SQL and VBA and am trying to read it slowly but right now I do not know how to code. I am trying to learn it and will be signing up for a class in the near future.

I have tried the wizard to find duplicates but the problem is it will combine pat#, fname and lname. As soon as I add something like date into it, the pat# now is not combined becuase there are 2 different dates for the same pat#. I hope this makes sense.

I would like to combine the pat#, fname, and lname, but sont know how to have the date into 2 columns like I showed above. There are 80 records total. I have tried to make 2 seperate queries one showing pat#, fname, lname and date1 showing 30 records and another query with pat#, fname, lname and date 2 showing 50 records. The problem is when I try to combine the queries into 1 it says the date is pulling from the same source and it cant do that.

Any help or suggestions on what to do?

 

[Frothingslosh]

Well, starting from the top, I see one likely structure error in your database that is making this far more difficult than it needs to be:

Why are you storing what appears to be the same data in two different tables? It's quite likely that you're better off storing it all in one table with an extra field listing the differences. (This mistake is usually done to separate things like active/inactive, current/out-of-date, or various locations.)

If it really does turn out to need to be in two different tables at the same time, then you'll HAVE to learn SQL, because you'll need a Union query, and the Access query-builder GUI doesn't support them.

 

[undefeatedskillz26]

Well, starting from the top, I see one likely structure error in your database that is making this far more difficult than it needs to be:

Why are you storing what appears to be the same data in two different tables? It's quite likely that you're better off storing it all in one table with an extra field listing the differences. (This mistake is usually done to separate things like active/inactive, current/out-of-date, or various locations.)

If it really does turn out to need to be in two different tables at the same time, then you'll HAVE to learn SQL, because you'll need a Union query, and the Access query-builder GUI doesn't support them.

Okay so this is what I did to get it to work. I created 3 queries.

For all the queries I am pulling the dates from the same source or same database field.

Query 1 - Date 1 from Due Date database field
Query 2 - Date 2 from Due Date database field
Query 3 - I have for each record Due Date 1 and Due Date 2 pulling from Query 1 and Query 2.

Now for the final part. How do I use DiffDate if I am pulling from the same DataField?

This is my code but am getting error:
How can I differentiate the DueDate database field.

DaysOut: DateDiff("d",[DueDate],[DueDate])

Thanks in advanced. Learning here everything I can.

 

[undefeatedskillz26]

Okay so this is what I did to get it to work. I created 3 queries.

For all the queries I am pulling the dates from the same source or same database field.

Query 1 - Date 1 from Due Date database field
Query 2 - Date 2 from Due Date database field
Query 3 - I have for each record Due Date 1 and Due Date 2 pulling from Query 1 and Query 2.

Now for the final part. How do I use DiffDate if I am pulling from the same DataField?

This is my code but am getting error:
How can I differentiate the DueDate database field.

DaysOut: DateDiff("d",[DueDate],[DueDate])

Thanks in advanced. Learning here everything I can.

Problem solved. Renamed DueDate in design view under Field.
Date1: DueDate
Date2: DueDate

DaysOut: DateDiff("d",[Date1],[Date2])

Thanks again everyone.