By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,289 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,289 IT Pros & Developers. It's quick & easy.

Move records to columns???

P: n/a
I have a table that contains an SCC # and then locations, Qty, SKU.
As below

SCC Lcoation qty Sku
123 adffa 3 321a1 m
123 adfa 3 32111 l
123 adfa 3 32111 xl
123 adfffa 3 32111 s
321 adfffa 3 32111 m
321 addfa 3 32111 d
321 dfa 3 32111 s

What I need to do is go through the table and for all the same SCC
number, move the location to a column so that the scc contains one
record, but shows all locations, qty, sku on one record. Possible
create location 1, Location 2, Location 3, etc. I have 5000 records
to cycle through?

Any thoughts? Is this even possible?

Dave

Jun 28 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On Thu, 28 Jun 2007 21:51:10 -0000, Starke <st********@hotmail.com>
wrote:

Check out Crosstab query in the Help file.
When you create a new query, there is a wizard to help you.

-Tom.
>I have a table that contains an SCC # and then locations, Qty, SKU.
As below

SCC Lcoation qty Sku
123 adffa 3 321a1 m
123 adfa 3 32111 l
123 adfa 3 32111 xl
123 adfffa 3 32111 s
321 adfffa 3 32111 m
321 addfa 3 32111 d
321 dfa 3 32111 s

What I need to do is go through the table and for all the same SCC
number, move the location to a column so that the scc contains one
record, but shows all locations, qty, sku on one record. Possible
create location 1, Location 2, Location 3, etc. I have 5000 records
to cycle through?

Any thoughts? Is this even possible?

Dave
Jun 29 '07 #2

P: n/a
On Jun 28, 10:26 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Thu, 28 Jun 2007 21:51:10 -0000, Starke <starke1...@hotmail.com>
wrote:

Check out Crosstab query in the Help file.
When you create a new query, there is a wizard to help you.

-Tom.
I have a table that contains an SCC # and then locations, Qty, SKU.
As below
SCC Lcoation qty Sku
123 adffa 3 321a1 m
123 adfa 3 32111 l
123 adfa 3 32111 xl
123 adfffa 3 32111 s
321 adfffa 3 32111 m
321 addfa 3 32111 d
321 dfa 3 32111 s
What I need to do is go through the table and for all the same SCC
number, move the location to a column so that the scc contains one
record, but shows all locations, qty, sku on one record. Possible
create location 1, Location 2, Location 3, etc. I have 5000 records
to cycle through?
Any thoughts? Is this even possible?
Dave
I tried crosstab query before posting but it appeared that it wants to
summarize the records and not display them as the row as a column. I
don't need the sum, or count I just need the row in a column(s). I
could be missing something though..

Thanks,

Dave

Jun 29 '07 #3

P: n/a
On Thu, 28 Jun 2007 20:14:33 -0700, Starke <st********@hotmail.com>
wrote:

Indeed it wants to run an aggregate function on the data, because
that's what most people would want. If you are not among them, you're
on your own writing the code yourself.

Are you really sure the data is as you describe, with qty being the
same value for all occurrances of a particular SCC value? If not, how
are you going to decide which qty to use?
That's where crosstab queries would use Sum(qty)

-Tom.

>On Jun 28, 10:26 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
>On Thu, 28 Jun 2007 21:51:10 -0000, Starke <starke1...@hotmail.com>
wrote:

Check out Crosstab query in the Help file.
When you create a new query, there is a wizard to help you.

-Tom.
>I have a table that contains an SCC # and then locations, Qty, SKU.
As below
>SCC Lcoation qty Sku
123 adffa 3 321a1 m
123 adfa 3 32111 l
123 adfa 3 32111 xl
123 adfffa 3 32111 s
321 adfffa 3 32111 m
321 addfa 3 32111 d
321 dfa 3 32111 s
>What I need to do is go through the table and for all the same SCC
number, move the location to a column so that the scc contains one
record, but shows all locations, qty, sku on one record. Possible
create location 1, Location 2, Location 3, etc. I have 5000 records
to cycle through?
>Any thoughts? Is this even possible?
>Dave

I tried crosstab query before posting but it appeared that it wants to
summarize the records and not display them as the row as a column. I
don't need the sum, or count I just need the row in a column(s). I
could be missing something though..

Thanks,

Dave
Jun 29 '07 #4

P: n/a
On Jun 28, 11:51 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Thu, 28 Jun 2007 20:14:33 -0700, Starke <starke1...@hotmail.com>
wrote:

Indeed it wants to run an aggregate function on the data, because
that's what most people would want. If you are not among them, you're
on your own writing the code yourself.

Are you really sure the data is as you describe, with qty being the
same value for all occurrances of a particular SCC value? If not, how
are you going to decide which qty to use?
That's where crosstab queries would use Sum(qty)

-Tom.
On Jun 28, 10:26 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Thu, 28 Jun 2007 21:51:10 -0000, Starke <starke1...@hotmail.com>
wrote:
Check out Crosstab query in the Help file.
When you create a new query, there is a wizard to help you.
-Tom.
I have a table that contains an SCC # and then locations, Qty, SKU.
As below
SCC Lcoation qty Sku
123 adffa 3 321a1 m
123 adfa 3 32111 l
123 adfa 3 32111 xl
123 adfffa 3 32111 s
321 adfffa 3 32111 m
321 addfa 3 32111 d
321 dfa 3 32111 s
What I need to do is go through the table and for all the same SCC
number, move the location to a column so that the scc contains one
record, but shows all locations, qty, sku on one record. Possible
create location 1, Location 2, Location 3, etc. I have 5000 records
to cycle through?
Any thoughts? Is this even possible?
Dave
I tried crosstab query before posting but it appeared that it wants to
summarize the records and not display them as the row as a column. I
don't need the sum, or count I just need the row in a column(s). I
could be missing something though..
Thanks,
Dave
Here is what I need to do, and maybe Im going about it incorrectly.

The table is as stated above. I am trying to print an SCC content
label in Bartender. For all the records that have the same Container
ID, PO, Store # etc it will need to be printed on the header part of
the label, then for all the same header ifno will have SKU, Customer
SKU, QTY etc as line detail that will print in the details section of
the label. The only way I know how to do this is to have one line per
record and map each column to text or barcode in Bartender.

There is probably a better way of doing this in bartender, but I can't
seem to find a group for bartender. So I thought if I could get a
query to populate the data in one record then I can take it from
there.

Confusing huh.. I think im stuck :-(
Jul 2 '07 #5

P: n/a
On Jul 2, 9:33 am, Starke <starke1...@hotmail.comwrote:
Confusing huh.. I think im stuck :-(
Instead of trying to explain it, draw it out like you did the original
table - how you want it to look.
Jul 2 '07 #6

P: n/a
On Jul 2, 3:36 pm, "christianlo...@yahoo.com"
<christianlo...@yahoo.comwrote:
On Jul 2, 9:33 am, Starke <starke1...@hotmail.comwrote:
Confusing huh.. I think im stuck :-(

Instead of trying to explain it, draw it out like you did the original
table - how you want it to look.
SCC Lcoation qty Sku Loc2
qty2 Sku2 Loc3 qty3 Sku3
123 adffa 3 321a1 m
afdfa 2 212512 adfa 5 542125

So if scc 123 has 7 records, there would be columns upto Loc7 and if
the next scc only had three records then columns upt o loc 3 would be
filled in. This is a BS way of doing this, but I call Seagull
Bartender Tech support and they stated bartender can not run through
records and print a header and then details that it prints only one
record at a time and I need all the data I want on one record to be in
one record.

Thanks,

Dave


Jul 5 '07 #7

P: n/a
On Jul 5, 2:59 pm, Starke <starke1...@hotmail.comwrote:
So if scc 123 has 7 records, there would be columns upto Loc7 and if
the next scc only had three records then columns upt o loc 3 would be
filled in. This is a BS way of doing this, but I call Seagull
Bartender Tech support and they stated bartender can not run through
records and print a header and then details that it prints only one
record at a time and I need all the data I want on one record to be in
one record.
Yeah, that's bogus. You can cycle through the records and print it
like that using the Recordset object. Having it show up in a table
though... I don't know.

-------------------------------------------------------------------------------------------
The table is as stated above. I am trying to print an SCC content
label in Bartender.
-------------------------------------------------------------------------------------------
I really don't understand what a 'content label' is. Are you referring
to the columns in a table?

Your first statement:
-------------------------------------------------------------------------------------------
What I need to do is go through the table and for all the same SCC
number, move the location to a column so that the scc contains one
record, but shows all locations, qty, sku on one record. Possible
create location 1, Location 2, Location 3, etc. I have 5000 records
to cycle through?
-------------------------------------------------------------------------------------------
"What I need to do is go through the table and for all the same SCC
number"

Is this the many part of a one to many relationship?
Jul 6 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.