Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make table qry from crosstab (2000)

    Is it possible to do a 'make table' query that is also a crosstab query? I have a I have a huge dbase file with an encounter # and one of up to 9 codes. Each code is in a separate row, so each encounter can have up to 9 rows.
    I want a table where each encounter # is followed by code1, code2, etc. But the file is too large to use the copy/paste functions so it will have to be created within Access.

    Any suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make table qry from crosstab (2000)

    Yes, a crosstab query can be turned into a make-table query. Start by designing the crosstab query until it returns the results you want to see, and only then change it into a make-table query. Post back if you have specific problems.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make table qry from crosstab (2000)

    Judy

    Probably getting the wrong end of the stick here but I had a go with my test DB and did it in two steps. First made a crosstab query and then from this resulting query ran a new make table query. So did it in two steps instead of one go. Does that help or am I looking at the problem too simplistically

    Jerry
    Jerry

  4. #4
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make table qry from crosstab (2000)

    How does one change it from crosstab to make table? I tried to click on make table with the crosstab open in design view, and it did make a table, but the table did not have ID1, ID2, Code 1, Code 2, Code 3 etc in the row. Instead it hareplicated the tale underlying the crosstab.

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make table qry from crosstab (2000)

    Jezza- I tried to make a new query from the crosstab, and it just shuts down my system. Perhaps it's because it's a crosstab to a linked table? I really don't want to import the table because dbase files that large cause their own unique complications. I obviously don't know enough. Suggestions?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make table qry from crosstab (2000)

    Judy,

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> You're correct, I didn't look at the result. But creating a make-table query based on a crosstab query based on a linked table should work. I just tried it, and this time I did check the result <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. It is as it should be. Are there any other special circumstances?

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Make table qry from crosstab (2000)

    <hr>. . . and it just shuts down my system.<hr>
    Exactly what do you mean by that? Does it crash Access? Do you get an error message? Or is your entire PC locking up to the point where you have to reboot.

    As an alternative to doing a make-table query, you could copy and paste the crosstab result into Excel, though that would mean having Excel open, and if you are having resource issues, might also result in system problems.
    Wendell

  8. #8
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make table qry from crosstab (2000)

    Hi, Wendell,
    The hourglass just sits there, and nothing happens. If I check task manager, it says that Access is not responding. I tried waiting for 5+ minutes - then gave up and ended task. It has done that every time- I choose new query, based on the crosstab, and it stops the system.

    I can't copy/paste because of the size- way too big for the clipboard.
    Perhaps I'm doing something wrong? As simplistic as it sounds, could you walk me through it?
    I have the table, and I have the cross tab. I assume I should make a new query, based on the crosstab? I know it doesn't work to open the crosstab in designview and try it that way.
    Any help is, of course, greatly appreciated.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make table qry from crosstab (2000)

    If your have a really large database, a complicated crosstab query is a heavy load on the system. It might be too much to handle, but on the other hand, I wouldn't be surprised if it took 15 minutes or even more. What percentage of the processor is used by Access? If it drops to 0% after a while, something is wrong.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Make table qry from crosstab (2000)

    As Hans suggests, your crosstab query may take a very long time to run, and when you try to design a new query based on the crosstab, it doesn't know what the column (field) names are, so it runs the crosstab to find that out. But I'm not sure I understand why you can't do an edit/copy edit/paste from the results of the crosstab - how many rows and columns is your crosstab returning?
    Wendell

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Make table qry from crosstab (2000)

    How many records are in the dbase file?

    Can you email the file to the email address in my profile?

    I would import the dbase table as is into an Access table then do your crosstab and make table from there.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make table qry from crosstab (2000)

    Pat

    Did you get the DB sent to you? If so, did you solve the problem?

    Jerry
    Jerry

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Make table qry from crosstab (2000)

    No I did not get the database. Did you send it to the email address in my profile?

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make table qry from crosstab (2000)

    Pat

    It wasn't me who was sending it to you, it was Judy. I was just being nosy to see if there was an answer to the problem

    Jerry
    Jerry

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Make table qry from crosstab (2000)

    Fair enough to be nosey, that shows you are inquisitive like me.
    She should be getting up about now, so it will be awhile yet till she gets to work. Unfortunately it's about my bedtime, so I will wait till the morning to see if she sends it.

Page 1 of 2 12 LastLast

Posting Permissions

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