Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2005
    Posts
    333

    Unanswered: Crosstab Query???

    I have a table which contains one column (time samples) and another table that contains data samples. The second table con have one or more data fields but each row is a sample taken at the time given in the first table.


    Table1
    Time
    time1
    time2
    time3

    Table2
    Point1 Point2 Point3
    data1 data1 data1
    data2 data2 data2
    data3 data3 data3


    I need to create a new table with the following format. I actually don't need the new table but I need to create a CSV file that is the equivalent.

    New Table
    Time Point1 Point2 Point3
    time1 data1 data1 data1
    time2 data2 data2 data2
    time3 data3 data3 data3

    I've tried to use a query to join the two table but I keep getting duplicate records. Each table has 600 rows, when I make my query I get 360000 rows.

    Is this a case that I should use a crosstab query? I can force the output by limiting th query to the top 600 rows but this number is going to change so I don't want to have it hard coded.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What is the query you are using?

    Sounds like you want a crosstab to me!
    George
    Home | Blog

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You're getting all those records because you don't have a join between the two tables. In that instance, you'll get a Cartesian product, or every possible combination (600x600=360,000). The solution is a join on the appropriate field(s). With what you've posted, I don't see which field(s) they have in common?
    Paul

  4. #4
    Join Date
    Feb 2005
    Posts
    333
    With what you've posted, I don't see which field(s) they have in common?
    That's the problem. The tables are generated from third party software and I have no control over their structure. They use the data to plot charts but their charts are not very useful so I want to utilize their data to generate a customized chart. The really difficult thing is that every time data is collected a new database is created with a time stamp included in the name so doing a linked table is out of the question unless I promt for a name and link dynamically.

    I may have to open both tables with VBA and read in one line at a time from each table and place the combined data in a new table. Seems like a lot of work though.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I think that's exactly what you'll have to do, and even that may be dicey. If there is no field to order the recordsets by, You're hoping they stay in the correct order so you can join the data up.
    Paul

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi all

    So
    the First Row of Tabl1 should be joined to the First Row of Table2
    the Second Row of Tabl1 should be joined to the Second Row of Table2
    the Third Row of Tabl1 should be joined to the Third Row of Table2
    ...
    the 600th Row of Tabl1 should be joined to the 600th Row of Table2

    If so, then would adding an autonumber field to both tables and joining thoes do the job !!??


    They would both be numbered 1 to 601 in their current order ??


    Just a thought.


    MTB

  7. #7
    Join Date
    Apr 2005
    Posts
    2
    I think you need an intermediate step before you get to the cross-tab. Your Table2 needs to be normalized (and as indicated in some other replies, a field added to join it back to the parent table).

  8. #8
    Join Date
    Feb 2005
    Posts
    333
    If so, then would adding an autonumber field to both tables and joining thoes do the job !!??
    Unfortunatley, The tables are generated by someone else's software and all that I have is the executable. Each time I collect data, a new database is created. They don't create a new table in an existing database, they actually create a new database. The table is populated before I has access to the the design so I would have to try to add an autonumber field to a table that already has data.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So there's NO relationship in the tables at current?
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2005
    Posts
    333
    So there's NO relationship in the tables at current?
    Unfortunatley not. As I mentioned earlier, this database is being created by an executable that is provided by a vendor. I can see logic in putting time in one table and the corresponding data in a nother table. The only relationship is that if there are 600 time values, there are also 600 data values. Of course this means that the first time value is associated to the first data value and so on.

    I don't have access to the data today but I'll upload on of the data base files tomorrow from work.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Question:

    Can you therefore use the rownumber as the key field?
    If so - experts, get cracking
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If the rows are uniquely identifiable you can generate row numbers in a query. I would rather insert them into a local table (say with SELECT * INTO myTable FROM MyHeterogeniousTable) & add an autonumber in code (you can do this with ADO). Then join the autonumbers. More efficient anyway.

    BTW - there is no such thing as first, second, third rows in a relational database. It might be dangerous to work on that basis.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2005
    Posts
    333
    Here's the database that gets generated when I collect data. You'll notice that the the field names are generic names like point_62, point_80, etc. There's actually another database that is used to store the "real" name of the data points.

    Modifying this data is pretty low priority so for now I'm just collecting ideas then if I have some extra time at the end of this project I'll try to implement some of your ideas.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •