#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    16
    Rep Power
    0

    Grouping Query with MDB


    I hope I am in the right forum, but I have a simple question.
    Lets say I have three tables:
    ID, Address and Phone
    the like from ID to Address and ID to Phone is Field: IDNumber
    I want to pull a query that will pull:
    PersonI address1 PhoneA
    address2 PhoneB
    PersonII address1 PhoneA
    address2 PhoneB
    etc...
    It appears that with a groupby clause I have to be doing calculations I really do not want all that.
    Is there a way to do this?
    All I can get is a query where it pulls an individual record for each address and phone number so if a person has three addresses and phone numbers he gets 6 results
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    13
    "Group By" will create a subset of your returned values. If you want to sort your results you should use "Order By"

    SELECT Fname, Lname, Address, Phone FROM Contacts ORDER BY Lname;

    If you shared your table setup I could give a more detailed reply.

    Walt
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    16
    Rep Power
    0
    I do not simply want it ordered.
    Let me re-explain:
    First TableSetup
    I have the following linked tables (all linked on IDNumber)
    Table: ID
    Fields: IDNumber, LName, FName
    Table: Address
    Fields: IDNumber, Type, StreetL1, StreeL2, StreetL3, City, State, Zip
    Table: Phone
    Fields: IDNumber, Type, PhoneNumber, Ext

    I want a query that outputs:
    LName, FName Address Phone
    Address Phone
    LName, FName Address Phone
    Address Phone
    LName, FName Address Phone
    Address Phone
    Assuming everyone has multiple addresses and phone numbers
    The output I get is
    LName, FName Address Phone
    LName, FName Address Phone
    LName, FName Address Phone
    LName, FName Address Phone
    So if someone has multiple addresses and phone numbers they are listed multiple times, whereas I am wanting them and all of their addresses and phonenumbers and then the next person with that information.
    I know I could do this on the report side but I am wanting to set this up to output to another program
  6. #4
  7. #5
  8. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    13
    When you query the database you're retrieving all matching information, and that is what's displayed. What you want to do is hold this information in a record set so you can loop through it and format the displayed information as desired.

    The details of how to format it vary some, but the logic is basically the same.

    Where and how are you trying to report this?

    As far as I can see you need help displaying the information, not retrieving it.

    Walt
  9. #6
  10. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    16
    Rep Power
    0
    pabloj-If you can tell me a group by statement that would work that would be great. But it seems that group buy demands to many peramiters for this simple query and does not therefor function

    waltjp-You are partially right getting a report with the information would help, but I am trying to port the information to a Palm.
    And for multiple users and allow them to later sync by themselves so creating a report and then taking that to CSV is not a great option.
  11. #7
  12. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    16
    Rep Power
    0
    Thanks for your help, it seems the best answer is to go with software that does the work for you, because the query I am looking for simply does not exist and I'll have to do it all software side with the for loops. But thanks oh but here is some interesting stuff
    http://www.consult-us.cc/html/handjetdemo_us.htm
    software for palms that is suppose to do this and then there is "satellite forms" a software for straight palm development. Both are suppose to be able to accomplish this task. Reply back here if you want know the outcome, say for edification purposes. I am probably going to go with the handjet (its much less expensive).
    Satellite forms is more for software development on the palm. Handjet is more for db passing (they say)

IMN logo majestic logo threadwatch logo seochat tools logo