Helpful Information
 
 
Category: Database Management
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

"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

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

GROUP BY LName ?

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

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.

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)










privacy (GDPR)