By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Concatenate query results access

P: 26
Hi,
first select query returns always 1 record (8 fields)
The second (crosstab) returns 1 row (6 fields)

both query work fine on their own. Can I create 1 query to return 14 fields on 1 row? How to concatenate the results?


SELECT tblParty.PartyID, tblParty.PartyDate, tblParty.Deposit, tblParty.PricePainter, tblParty.PartyStart, tblParty.PartyEnd, tblParty.PaintersNo, tblParty.ChildName, tblParty.ChildAge, tblParty.Notes, tblLocations.LocationName, tblParty.CustomerID
FROM qry_Sys, tblLocations INNER JOIN (tblCustomers INNER JOIN tblParty ON tblCustomers.CustomerID = tblParty.CustomerID) ON tblLocations.LocationID = tblParty.LocationID
WHERE (((tblParty.PartyID)=[Forms]![frmPartySbf]![PartyID]));



TRANSFORM First(tblSys.VarValue) AS FirstOfVarValue
SELECT "All fields" AS exp
FROM tblSys
GROUP BY "All fields"
PIVOT tblSys.VarName;
Mar 22 '17 #1

✓ answered by jforbes

  • A Form, for the most part, is or should be based on a Single Table. Use this Table as the RecordSource for the Form and Filter the Form based on what you want to show for that Table.
  • For the Form, if there is related information in another Table or Query, and it's strictly a one to one relationship between the Form's Table and the related information, you can change the Forms RecordSource to a Query based on the Original Table and include the additional Tables with a Left Join.
  • If there is additional information in another Query or Table that is a one to many or a many to many relationship to the original Table, it's best create a SubForm based on the additional information and then place the SubForm on the Main Form. Sometimes in this scenario, Access can manage the link between the MainForm and the SubForm quite well, other times is best to manage what is displayed on the SubForm through Custom Code on the MainForm, usually in the OnCurrent Event.
  • If there is additional information to be displayed that is not related in anyway to the dataset of the MainForm:
  1. Then a SubForm can still be used if it's beneficial to make a certain view of the data that is consistent across multiple Forms. Like a little status window that can be placed on any Form that reports back some kind of critical number, like how many telephone callers are in the Queue, how many workers are logged on to help them, and a calculated wait time.
  2. If it's just a few fields and it's a unique situation, then TextBoxes can be placed on the Form and have their ControlSource set to something like =DMax("SomeField", "qrySource", "ID=12"). This is a Quick and pretty Easy way to go out to the database and grab some values.
  3. A similar option to the Previous option of setting the ControlSource of a TextBox, is to again use TextBoxes but set their values from Code in the Form_Load or OnCurrent Event. This is usually done with DLookups() or by using OpenRecordset to grab a bunch of values at once.

Share this Question
Share on Google+
4 Replies


jforbes
Expert 100+
P: 1,107
You could save both Queries. If the First was Named qryPartyDetails and the Second was Named qrySysDetails. You could then write a Query like this to get both sets of data into one:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   qryPartyDetails.*
  3. , qrySysDetails.*
  4. FROM 
  5.   qryPartyDetails
  6. , qrySysDetails
But even though something like that can be done, it's a bad habit and you probably should not pick it up.

What are you actually trying to accomplish with the Record Set? Are you attempting to get both sets of data on to the same Form? If so, there are better ways.
Mar 23 '17 #2

P: 26
You are absolutely right. Was trying to get 2 recordsources on 1 form. Wanted to avoid creating a subform. What else could I do instead?
Mar 23 '17 #3

jforbes
Expert 100+
P: 1,107
  • A Form, for the most part, is or should be based on a Single Table. Use this Table as the RecordSource for the Form and Filter the Form based on what you want to show for that Table.
  • For the Form, if there is related information in another Table or Query, and it's strictly a one to one relationship between the Form's Table and the related information, you can change the Forms RecordSource to a Query based on the Original Table and include the additional Tables with a Left Join.
  • If there is additional information in another Query or Table that is a one to many or a many to many relationship to the original Table, it's best create a SubForm based on the additional information and then place the SubForm on the Main Form. Sometimes in this scenario, Access can manage the link between the MainForm and the SubForm quite well, other times is best to manage what is displayed on the SubForm through Custom Code on the MainForm, usually in the OnCurrent Event.
  • If there is additional information to be displayed that is not related in anyway to the dataset of the MainForm:
  1. Then a SubForm can still be used if it's beneficial to make a certain view of the data that is consistent across multiple Forms. Like a little status window that can be placed on any Form that reports back some kind of critical number, like how many telephone callers are in the Queue, how many workers are logged on to help them, and a calculated wait time.
  2. If it's just a few fields and it's a unique situation, then TextBoxes can be placed on the Form and have their ControlSource set to something like =DMax("SomeField", "qrySource", "ID=12"). This is a Quick and pretty Easy way to go out to the database and grab some values.
  3. A similar option to the Previous option of setting the ControlSource of a TextBox, is to again use TextBoxes but set their values from Code in the Form_Load or OnCurrent Event. This is usually done with DLookups() or by using OpenRecordset to grab a bunch of values at once.
Mar 23 '17 #4

P: 26
Decided to put a subform.
I would have to have too many Dlookups if I go without.

Thanks for all the info, that clears once and for all my uncertainties about what a form should be ideally used for.
I love experimenting but sometimes I got to ask questions before I go nuts trying to do something I shouldn't be doing.
Mar 23 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.