UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Query Result Shows Multiple Records, Access 2010    
 
   
Patty2
post Oct 20 2019, 06:49 PM
Post#21



Posts: 432
Joined: 10-August 08



sorry Joe but your query
"See qryAllDataByCaseNo for how I dealt with that in the SimpleCSV"
was not in the database you had to be downloaded
Go to the top of the page
 
RJD
post Oct 20 2019, 07:41 PM
Post#22


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


Hi Patty: I just checked my posted db (downloaded from my post), and the query ...

[qryAllDataByCaseNo]

... looks like it is there.

Please check again.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Patty2
post Oct 22 2019, 10:09 AM
Post#23



Posts: 432
Joined: 10-August 08



Iam sorry Joe but I have no idea what a SimpleCSV” is or how to apply it
Go to the top of the page
 
Patty2
post Oct 22 2019, 11:06 AM
Post#24



Posts: 432
Joined: 10-August 08



I just want a QUERY with all of fields in both tables without the double and triple records
so I think the problem is in the subform frmDriverInfo where when adding drivers, the caseno is
automattly inputted for each driver. If somehow one caseno is added for ALL the drivers, then I think
when the query is created there would be one record for one caseno
Go to the top of the page
 
RJD
post Oct 22 2019, 12:14 PM
Post#25


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


Hi Patty: If you have a 1-to-many relationship, and you want the many side results to be grouped on one row (record output) with the 1 side result, then you will have to use a VBA function to make that happen. SimpleCSV is a generalized function that makes that happen (there are other general functions, such as one from Allen Browne, and there are specific functions written for specific needs). This function is included in the demo I posted. You can look at the code in the function to see how it works, and you could visit theDBguy's SimpleCSV site for further information.

The SimpleCSV is used by calling the function ...

Results: SimpleCSV("SELECT [the field(s) you want concatenated on one row] FROM [your table/query name] WHERE [your connection key in the many-side table/query] =" & [your connection key in the 1-side table/query])

(example is for a numeric connection key)

You can see how this is built in your case in the demo, in the last field. Look at the query in SQL View and you can see the function better. Note that since you wanted many fields from the many-side, they are concatenated in the SELECT code.

I am not at all sure what you meant in your last post, but all drivers associated with a case should have the SAME case number.

Run the query I posted and look to the far right. Is this something like what you wanted (you can certainly rearrange or select the fields differently)? If so, you have an example of how to do this you can apply to your full db. If not, perhaps you could post an example of how the results should look (say, in an Excel spreadsheet like Robert did) so we can get a better handle on the result requirements.

Complex manipulation of data in Access often requires procedures in VBA to accomplish that. So it is important to become familiar with VBA procedures (and how to apply them) and the cases in which this is required or convenient.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
arnelgp
post Oct 22 2019, 12:52 PM
Post#26



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


there are three queries
1. qryFirstDriverInfo - first driver/car involved in accident
2. qrySecondtDriverInfo - 2nd driver/car involved
3.qryCaseDriversInvolved - the final query (tblCaseLocation linked to the 2 above)

Attached File(s)
Attached File  CrashData_4Repair.zip ( 188.61K )Number of downloads: 4
 

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2019 - 06:45 PM