Combining two row into one

R

Rajat

hi,

iam trying to make a query which will return 1 row with n+n column
from 2 rows(same table) with n columns each.

the initial table structure would be like this-

Code
-------------------
date | 12 am | 1 am | 2am | ....... | 11 p
-------------------


& finally the table should be like

Code
-------------------
date | 12 am | 1 am | 2am | ....... | 11 pm | x12am | x1 am | x2am | ....... | x11 pm
-------------------


so basically combining two rows from 1st table to 1 row (in ne
table/or otherwise)...
i am using VB to execute this query.....so i can construct the query i
VB & then execute it....

-edit:-
actually dont care by column names in 2nd table


sub topic:
the main problem is that....sometimes i need few columns from 1 row an
few from another......
for eg -
on 2/1/2006 - need columns 21pm | 22pm | 23pm <- this is 1 row
while on 2/2/2006 - need columns 12 am | 1 am | 2am | 3am | 4am | 5am
<- 2nd row
i need to combine both these rows & display 1 row with only thos
columns....

Trust iam clear. :confused:

TI
 
B

BruceM

If you are looking at a query in datasheet view, each row is a record. You
can't change that in a query. You can arrange fields on a form or report in
any way you choose. However, combining two records is going to give you
some problems, even if it is possible to induce the records to appear
together. If you are relying on combining records, your database design is
probably inadequate to your needs.
Rather than saying what the table structure would be like, you should
describe the real-world situation you are trying to organize. I can't make
out what you are doing or how your database is organized. Are 12am, 1am,
etc. records, or are they the names of fields in the table? What is the
table's purpose? I suspect the database's design is not answering your
needs very well, but without knowing anything about the database it is
impossible to say.
 
J

John Spencer

Your request was hard to figure out, but I think I have a start for a
solution.

SELECT DISTINCT A.*, B.*
FROM TheTable As A Inner Join the Table as B
ON A.Date = B.Date

If you have a primary key in the table then you will get multiple rows in
the match. So you will have to remove the primary key from the SELECT
clause by specifying the fields you want.
 
B

BruceM

Are you saying records from two tables that share a criterium such as date
can be combined into a single query record? If so, I misspoke in my attempt
to reply.

John Spencer said:
Your request was hard to figure out, but I think I have a start for a
solution.

SELECT DISTINCT A.*, B.*
FROM TheTable As A Inner Join the Table as B
ON A.Date = B.Date

If you have a primary key in the table then you will get multiple rows in
the match. So you will have to remove the primary key from the SELECT
clause by specifying the fields you want.
Click to expand...
 
J

John Spencer

Yes, you can show records from the same table joined to itself. Just like
you can join table a to table b and get all fields from the two tables in
one record.

BruceM said:
Are you saying records from two tables that share a criterium such as date
can be combined into a single query record? If so, I misspoke in my
attempt to reply.
Click to expand...
 
B

BruceM

Thanks for the reply. Upon further reflection I guess I knew that, although
I would think it could become rather awkward if there are variable numbers
of records with the same date from either table.
 
R

Rajat

thanks for ur replies guys....

think i shall describe the table in more detail

there is only 1 table and 12am,1am are names of fields in the table.
it actually has a composite primary key which includes a store numbe
and date.

i need to extract a particular "time" range for a given store whic
could be in two rows of the table.
eg:

Code
 
J

John Spencer

Again my best guess on getting the results you want is to add the same table
twice to your query and then join on the appropriate fields. It looks like
you need to join the two references to the table on the StoreNo and Date.

Of course the main problem with this whole thing appears to be your table
structure. Your table should probably look more like
ActivityDate
TimePeriod
StoreNo
SomeValue

And there is a good chance that the TimePeriod could be combined with the
ActivityDate.
 
Top