Combine data from related fields into a row "de-atomic forming"? 1

[AndrewRW]

Hi,
I've had a look around, but can't find out how I might be able to do this.
I have data that looks a bit like this:

PersonUniqueID Date Item
123 12/05/2005 1
123 12/05/2005 2
456 12/05/2005 1
456 12/05/2005 2
567 13/05/2005 3
567 13/05/2005 4
567 14/05/2005 1
567 14/05/2005 2

I want the data to look like this:
PersonUniqueID Date Items
123 12/05/2005 1/2
456 12/05/2005 1/2
567 13/05/2005 3/4
567 14/05/2005 1/2

But ultimately, I want to be able to report this:

Combination No.ofTimes No.ofPersons
1/2 3 3
3/4 1 1

(If I don't need the intermediary step, that's great....but I need to report the number of times each combination occurs (where it occurs on the same date for the same person) and how many people it occurs for))

Hopefully, this makes some sort of sense to someone out there...
cheers,
Andrew

 

[dhookom]

There is a generic concatenate function with sample usage at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

This would slowly create your intermediate step. There are methods that use sql and a temporary table that might be quicker.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[OhioSteve]

It depends upon how many combinations of items exist. If there are literally just four items, then these two-item combinations exist~

1,2
1,3
1,4
2,3
2,4
3,4

I could easily write a macro that summed fields with these combinations.

But I suspect that you actually have alot more than four items. In that case, it would be alot more complex.

 

[AndrewRW]

Yes indeed...and the numbers are just examples - the actual item numbers are between about 23 and 44000...

Thanks too, dhookum, I'll have a look at that site. Funny how simple the problem looks....

cheers
Andrew

 

[AndrewRW]

Duane,
I keep getting errors (missing operators), despite the fact that your database works fine, but when I use the same syntax in a query in my database it doesn't work at all...argh.

The operator errors relate to the WHERE part of the SQL statement...and I get them for the SELECT part if I don't put square brackets around the field name...anyway, if you can point me in the direction of the quicker fixes I'll have a go at them too

What you've done in the example is exactly what I want to do in my database! I just can't figure out why it won't transfer across...

Ah well, enough hair-tearing for one day...
cheerio
Andrew

 

[dhookom]

Andrew,
You need to provide your attempt at my solution so that I can see what might have caused the error. I also need to know the field types involved.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AndrewRW]

Hi,
This is my attempt...Item number is a number, PID is text, Date is a date/time field.

SELECT qryGroup.PID, qryGroup.Date, Concatenate("SELECT [Item Number] from qryItems WHERE [PID]=" & [PID] & "AND [Date]=" & [Date]) AS Items
FROM qryGroup;

The qryGroup basically just groups PIDs and Dates(like the intermediary step above). qryItems pulls out the records I am interested in (certain ranges of item numbers)

Thanks for your help Duane....very strange...I've tried different variants of square brackets/no square brackets. If I leave out the WHERE statement it kind of works, but puts all the Items from qryItems in each row rather than the ones related to the PIDs and Dates in qryGroup. I've tried creating a table and duplicating the structure of your database but it still doesn't work...

I copied your module across and haven't changed it at all.

cheers,
Andrew

 

[dhookom]

You must delimit your criteria values in your sql string as well as make sure you have some spaces:

SELECT qryGroup.PID, qryGroup.Date, Concatenate("SELECT [Item Number] from qryItems WHERE [PID]= """ & [PID] & """ AND [Date]=#" & [Date] & "#") AS Items
FROM qryGroup;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AndrewRW]

Thanks Duane,
That seems to be a lot closer, just not completing the records where the date is a single digit day (I'm in Australia, so our date format is dd/mm/yyyy (eg 7/12/2005)-but I won't waste any more of your time by expecting you to fix it if it's not very easy

I've now found a few other posts related to this topic (and in which you have replied), so I guess you get a bit tired of saying the same thing over and over again :/

cheers
Andrew

 

[AndrewRW]

Actually it's a bit stranger than that...when the date is from 1st - 12th day it won't complete the records...weird...

Anyway, thanks again.

Cheers,
Andrew

 

[OhioSteve]

I have followed this thread and I have a couple comments. This problem is a combinations/permutations problem. So you may get ideas for solving it by refreshing your mathematical knowledge of those topics. A couple questions to consider are "does order matter?" and "will every set have two members?" Let me give some examples:

Are 1/2 and 2/1 the same or different?
Could null, 1 or 1/2/3 be combinations?

I am also interested in how many combinations exist. You said that the numbers go to 44,000. Does that mean 44,000 squared combinations exist? 44,000 squared is more than one billion, so processing speed will be a concern if that many variations exist.

You should strongly consider doing this in a structured programming language. Since you are using Access, consider vba. You might even find vba math functions that would be helpful.

 

[dhookom]

I am not about to change my regional settings to test this but try the following which might work better:
SELECT qryGroup.PID, qryGroup.Date, Concatenate("SELECT [Item Number] from qryItems WHERE [PID]= """ & [PID] & """ AND [Date]=#" & Format([Date],"mm/dd/yyyy") & "#") AS Items
FROM qryGroup;


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AndrewRW]

That's it Duane!
It worked - you legend!

Cheers
Andrew