| Author |
Message |
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
  
Topic: Query data and then display on main form
Posted: 10 Feb 2014 at 3:23am |
|
Hi,
Firstly apologies if this is confusing, Ive written and re-written it a million times to attempt clarity.
Im having problems displaying my data as I think I need to create some sort of query which will then be used in my main report - is this possible?
Basically what happens is, if I create a report all my values are returned except one or two. If I try and modify my report to show these values it removes all my other results and shows the ones Im missing.
Can I use an sql query to look up the value(s) and then add that to my report? If so how - as when I try to add my sql I get error in compiling sql expression.
This is my code
[CODE]
SELECT `PO_Detail`.`PO`, `Source`.`Description`
FROM `Source` `Source` INNER JOIN `PO_Detail` `PO_Detail` ON `Source`.`PO_Detail`=`PO_Detail`.`PO_Detail`
[\CODE]
Edited by shabbaranks - 10 Feb 2014 at 3:23am
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 10 Feb 2014 at 5:21am |
|
you could create a command and add you sql in there. Then you could link the command to other tables, or just get all the data from the command.
Me, I prefer stored procedures to do all the lifting in getting report data.
It's up to you.
|
| IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 10 Feb 2014 at 5:34am |
|
The problem is that the current backend of the DB is access so I wont be able to use stored procedures. Also after a little reading - am I correct in thinking to create a command this is done when choosing the tables? As I don't have that functionality available for some reason?
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 10 Feb 2014 at 5:37am |
|
if you go into Database/Database Expert, you should be able to add a Command on the connection being used for the report.
I haven't reported off of an Access database, but i would expect that the option would still be there.
Though I have been wrong before :(
HTH
|
| IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 10 Feb 2014 at 5:50am |
Originally posted by lockwelle
if you go into Database/Database Expert, you should be able to add a Command on the connection being used for the report.
I haven't reported off of an Access database, but i would expect that the option would still be there.
Though I have been wrong before :(
HTH
You my friend are a star - works a treat :)
Hmm its reverting back to changing the output of the report :( Edited by shabbaranks - 10 Feb 2014 at 5:57am
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 10 Feb 2014 at 7:08am |
|
If I understand you correctly, you're trying to add a couple of values to a report that is working correctly, but adding the tables to get the values caused no data to be returned.
In your original report (without the command), join TO these tables FROM the tables that are already in the report and make the joins "left outer" joins by right-clicking on them and selecting "Join Options". This way, if there's no data for the record in the new tables, the joins won't keep data from appearing.
Also, if you have a filter in the Select Expert on any of the fields in these tables, take it out of the report prior to testing it to make sure that the links are working correctly. If you have this kind of filter, let me know as there are a couple of things you need to do to filter on tables that are left-joined into the report.
-Dell
|
|
|
| IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 10 Feb 2014 at 10:27pm |
|
Thanks again it was down to the joins on the tables - I now have it working perfectly. Apart from duplicate results, so this is my next problem.
I have 7 results in the report
PO--OrderDate--OrderBy--Description--Cost--Supplier--Code
My problem is I don't want to use suppression if duplicated as there will be duplicate results (but not ALL the same) if that makes sense?
For example the PO and OrderDate would be the same but the description and cost and possibly even the CostCode will be different.
Is there a way to remove duplicates based on this? Im loving learning making these reports (sad huh:))
Thanks!
|
| IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 10 Feb 2014 at 10:33pm |
|
I think Ive got it sorted by using Previous({table.record})={table.record} within section expert
|
| IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 10 Feb 2014 at 11:05pm |
Me again :)
I thought I had it but I'm still getting duplicates, is it possible to use the section expert formula as Ive put above - but on multiple fields?
Managed to sort it - I grouped it first and then used the section expert jobby  Edited by shabbaranks - 10 Feb 2014 at 11:50pm
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 11 Feb 2014 at 4:48am |
|
you could add grouping so that only unique records are displayed...ok, you group on everything that you think makes a record unique, then suppress the detail and only display the group footer...since there is only 1 footer per group you will have a distinct display.
the caveat is that any aggregate function like SUM AVG COUNT will include all the records, not just the records displayed.
HTH
|
| IP Logged |
|
|
|
