Difference between fields of two records with same ID

[Hosacans]

Hi all,

well i am not sure how to describe this in the appropriate terms. so pls forgive me...

here goes..

i have a table off from a query that has IDs and units. How do i group similar records together by IDs and create a difference between units... see illustration.

(query1)

IDs |Units
1111 4
1111 6
1111 2
1111 7
1111 9
2222 8
2222 2
2222 5

if i were to build a query on this, how would i get it to become:

(query2)
ID |Units of Order1| Units of order2| Units of Order3|Units of Order4
1111 4 6 2 7
2222 8 2 5

hope my description is not too confusing...

thanks in advance
HK

 

[MrTBC]

Hi Hosacans. Try creating a 'Crosstab' query - that should achieve this for you.

 

[Hosacans]

Cross query works exactly... but i need to somehow number the records so my cross query will have the appropriate field for the column.

How can i make query1 into this:

IDs |Units |Order #
1111 4 1
1111 6 2
1111 2 3
1111 7 4
1111 9 5
2222 8 1
2222 2 2
2222 5 3

without manually putting them in...

thanks
H

 

[PHV]

How are the original records sequenced ?
In other words, why "1111 6" is #2 and "1111 2" is #3 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Hosacans]

the first column on the query output table is IDs: 1111 the second column is Units: 6... and because different records have same IDs. i wonder if there is a way to number them.

oh, also the records have a reference to time in a seperate field. i.e. 1111 ordered 5 units at 1:00PM, (i want to call that order 1), then 1111 ordered 4 units at 5:00PM (order 2), 1111 ordered 8 units at 8:00PM (order 3). when 2222 ordered 4 units at 2:00PM (since that is 2222's first order, that should be order 1), then 2222 ordered 10 units at 9M (that is order 2).... eventually i want to compare the amount of units among orders by the same IDs.

hope this clarify some confusion
thanks
H

 

[Hosacans]

Hi,
just want to bump this. i really need some guidance...

thanks
H

 

[Crowley16]

so, basically, the ordering of individual ids depends on time?

then why don't you just include the time field in your query? So instead of
IDs |Units |Order #

you have
IDs |Units |Time

and then you can just use the time value to order the results...

--------------------
Procrastinate Now!

 

[Hosacans]

orders doesnt depend on time, it only reference it.

in other words, i guess what i am trying to do is to sub group all the orders by a particular ID and label the orders based on the earliest to the latest within the same ID, time of the order has to relation between different IDs.

the report should be able to say...

1111 first order was placed at 1:00PM with 4 units
1111 second order was placed at 3:00PM with 8 units
1111 third order was placed at 6:00PM with 2 units

2222 first order was placed at 5:00PM with 10 units
2222 second order was placed at 6:00PM with 5 units
etc...

thanks
H

 

[Crowley16]

if the order reference the time, then you can use the reference to get the time of the order right?

you want a query that returns IDs, Units, Time

then just group the query by ids, and then order by time

--------------------
Procrastinate Now!

 

[Hosacans]

I think what i am trying to do is similar to this thread:

http://www.tek-tips.com/viewthread.cfm?qid=909927

except i want to number the occurances based on time, earliest being 1...

thanks
H

 

[PHV]

SELECT A.IDs, A.Units, (SELECT Count(*) FROM yourTable B WHERE B.IDs=A.IDs AND B.TimeField<=A.TimeField) AS [Order #]
FROM yourTable A


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244