If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Birthday Querie (For multiple Birthdates in each record
Hi I am in the process of setting up a contacts database where for some
contacts i am wanting to store Spouse name and birthdate and where applicable the same for the children all in one record. The fields are as follows (The assumption is made that everyone shares the lastname field). At current I do not have contacts where I want to store childrens details for more then 2 children. Primary Firstname Lastname Birthdate SpouseFirstname SpouseBirthdate 1stChildFirstname 1stChildBirthdate 2ndChildFirstname 2ndChildBirthdate Now what I want to be able to do is print out a list with everybodies names and their date of birth (sorted by month then day which I will then format into age also). The only way I can see I can do this is to create 4 seperate queries and output 4 seperate reports (or subreports) but I want to be able to merge it all into one list so it is all ordered rather than broken into Primary, spouse, 1st child, 2nd child. Is this doable and if so can someone point me in the right direction. Thanks. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200611/1 |
#2
|
|||
|
|||
Birthday Querie (For multiple Birthdates in each record
at current? and if the next contact has 10 children?
Can contacts be married to one another? Not be married and have a partner with a diiferent name, and share parenting of children with different surnames? Time and the world is going to overwhem your design, and you might be better advised to design it right now. You are dealing with relations, and I would advise you to change your design to be relational. ...The only way I can see I can do this is to create 4 seperate queries and output 4 seperate reports (or subreports)... You are straying from the highway, with only 2 out of N children, and are already getting tangled in the undergrowth. ... Now what I want to be able to do is print out a list with everybodies names and their date of birth (sorted by month then day which I will then format into age also)... On the highway that is one query. "naigy via AccessMonster.com" u17625@uwe wrote in message news:69d4afa673ae0@uwe... Hi I am in the process of setting up a contacts database where for some contacts i am wanting to store Spouse name and birthdate and where applicable the same for the children all in one record. The fields are as follows (The assumption is made that everyone shares the lastname field). At current I do not have contacts where I want to store childrens details for more then 2 children. Primary Firstname Lastname Birthdate SpouseFirstname SpouseBirthdate 1stChildFirstname 1stChildBirthdate 2ndChildFirstname 2ndChildBirthdate Now what I want to be able to do is print out a list with everybodies names and their date of birth (sorted by month then day which I will then format into age also). The only way I can see I can do this is to create 4 seperate queries and output 4 seperate reports (or subreports) but I want to be able to merge it all into one list so it is all ordered rather than broken into Primary, spouse, 1st child, 2nd child. Is this doable and if so can someone point me in the right direction. Thanks. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200611/1 |
#3
|
|||
|
|||
Birthday Querie (For multiple Birthdates in each record
Okay so basically you are telling me this can't be done is that correct.
I believe going down the relational database line is going to make it more complex then it needs to be for my purpose (I have done it in the past for other projects) but I can see your point about thinking of the future. I will attempt to map this out and go down this route with hopefully more success. Thanks for your help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200611/1 |
#4
|
|||
|
|||
Birthday Querie (For multiple Birthdates in each record
No, if I thought it could not be done I would have said so.
You can get several queries to output their data in a common format, and then create a UNION query in SQL view. It is not that difficult. Something like:- (SELECT Name1, Name2, DOB, Age FROM .......) UNION (SELECT Name1, Name2, DOB, Age FROM .......) UNION (SELECT Name1, Name2, DOB, Age FROM .......) and then use that query as an input to your query to sort the data. In a properly designed database I believe that it would only take 1 query to do the whole job. Good Luck. "naigy via AccessMonster.com" u17625@uwe wrote in message news:69d5ed2f950e7@uwe... Okay so basically you are telling me this can't be done is that correct. I believe going down the relational database line is going to make it more complex then it needs to be for my purpose (I have done it in the past for other projects) but I can see your point about thinking of the future. I will attempt to map this out and go down this route with hopefully more success. Thanks for your help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200611/1 |
#5
|
|||
|
|||
Birthday Querie (For multiple Birthdates in each record
To expand just a little on David Cox's response. The union query should look like
SELECT LastName , [Primary FirstName] as FirstName , Birthdate , "Primary" as FamilyPosition FROM [YourTable] UNION ALL SELECT LastName , [SpouseFirstName] , SpouseBirthdate , "Spouse" FROM [YourTable] UNION ALL SELECT LastName , [1stChildFirstName] , [1stChildBirthdate] , "Child1" FROM [YourTable] UNION ALL SELECT LastName , [2ndChildFirstName] , [2ndChildBirthdate] , "Child2" FROM [YourTable] Save that query as qNormalFamily, Then you can use this as the basis of other queries instead of a table. One shortcoming with using a union query is that nothing can be updated, deleted, etc from a Union query. It is read only. Better design would be to have a familyTable where you would store information relevant to the entire family (home phone, home address, record identifier) and then another table for family members where you store information on each member of the family - such as name, dob, gender, type of family member, etc. David F Cox wrote: No, if I thought it could not be done I would have said so. You can get several queries to output their data in a common format, and then create a UNION query in SQL view. It is not that difficult. Something like:- (SELECT Name1, Name2, DOB, Age FROM .......) UNION (SELECT Name1, Name2, DOB, Age FROM .......) UNION (SELECT Name1, Name2, DOB, Age FROM .......) and then use that query as an input to your query to sort the data. In a properly designed database I believe that it would only take 1 query to do the whole job. Good Luck. "naigy via AccessMonster.com" u17625@uwe wrote in message news:69d5ed2f950e7@uwe... Okay so basically you are telling me this can't be done is that correct. I believe going down the relational database line is going to make it more complex then it needs to be for my purpose (I have done it in the past for other projects) but I can see your point about thinking of the future. I will attempt to map this out and go down this route with hopefully more success. Thanks for your help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200611/1 |
#6
|
|||
|
|||
Birthday Querie (For multiple Birthdates in each record
Thanks for your help. I have decided to go with the indivual record approach
and have a common link number which combines common addresses, phone number etc. Thanks for your assistance. John Spencer wrote: To expand just a little on David Cox's response. The union query should look like SELECT LastName , [Primary FirstName] as FirstName , Birthdate , "Primary" as FamilyPosition FROM [YourTable] UNION ALL SELECT LastName , [SpouseFirstName] , SpouseBirthdate , "Spouse" FROM [YourTable] UNION ALL SELECT LastName , [1stChildFirstName] , [1stChildBirthdate] , "Child1" FROM [YourTable] UNION ALL SELECT LastName , [2ndChildFirstName] , [2ndChildBirthdate] , "Child2" FROM [YourTable] Save that query as qNormalFamily, Then you can use this as the basis of other queries instead of a table. One shortcoming with using a union query is that nothing can be updated, deleted, etc from a Union query. It is read only. Better design would be to have a familyTable where you would store information relevant to the entire family (home phone, home address, record identifier) and then another table for family members where you store information on each member of the family - such as name, dob, gender, type of family member, etc. No, if I thought it could not be done I would have said so. [quoted text clipped - 29 lines] Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200611/1 -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|