Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

complicated query for report

Posted on 2006-03-31
5
Medium Priority
?
222 Views
Last Modified: 2006-11-18
I like to refer to my question:
www.experts-exchange.com/Databases/MS_Access/Q_21782505.html

This is the code for my form to select different sources (tables).



SELECT *
FROM (
     SELECT Funds.Fund_Number AS FundNumber, Funds.Fund_Name AS FundName, Funds.Reg_Fund AS RegFund
     FROM Funds
     WHERE [Funds].[Reg_Fund] = [Investments].[IG_yes]
     UNION
     SELECT Funds_Other.Fund_Number, Funds_Other.Fund_Name, Funds_Other.Reg_Fund
     FROM Funds_Other
     WHERE [Funds_Other].[Reg_Fund] = [Investments].[IG_yes]) AS Q
ORDER BY StripOutCharType([Q]![FundNumber],True ," , " "), Val(StripOutCharType([Q]![FundNumber], False))

I have to create a report based on this form and more information. The report was working with my old query (one source only). With the new constellation of data, -different sources, I get a Type mismatch as a fault in the report.

The query I am using is untached and I am aware of problems of the old query. How can I integrate the new functions?

Thanks for your support.
Josh
0
Comment
Question by:Bergkamen
  • 3
  • 2
5 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16344642
Hi Josh,

You threw a new table into the mix, Investments.  I am guessing you need something like this:

SELECT *
FROM (
     SELECT Funds.Fund_Number AS FundNumber, Funds.Fund_Name AS FundName, Funds.Reg_Fund AS RegFund
     FROM Funds INNER JOIN Investments ON [Funds].[Reg_Fund] = [Investments].[IG_yes]
     UNION
     SELECT Funds_Other.Fund_Number, Funds_Other.Fund_Name, Funds_Other.Reg_Fund
     FROM Funds_Other INNER JOIN Investments ON [Funds_Other].[Reg_Fund] = [Investments].[IG_yes]
    ) AS Q
ORDER BY StripOutCharType([Q]![FundNumber],True ," , " "), Val(StripOutCharType([Q]![FundNumber], False))


Regards,

Patrick
0
 

Author Comment

by:Bergkamen
ID: 16344849
Mmmh.

The table investments contains a number/text for a fund from either Funds or Funds_Other. For the report I need more information about the person that does the investments. The table Investments contains a column called Client_Number. The same number is in the Client-table.

All worke out nicely, until I mixed the text from the Funds_Other table with the number from the Funds table.......

The Form is OK! But, on the Form I have a button to call the report. This report contains most of the data shown on the form. The data are different organized (sorted)., means from my one Form I renerate few report sheets.

Regard,
Josh
0
 

Author Comment

by:Bergkamen
ID: 16345072
Just a thought.....
Maybe I should copy the data from table Fonds and Fonds_Other into my table Investments. This way the data doesn't need to be looked-up.
Currently I keep a number/text in the Investment table and look-up the Comment and other data fron the Funds or Funds_Other table.
What do you think about that?
Josh
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1500 total points
ID: 16345092
Josh,

I think it would be useful for you to take a step back, identify what needs to be on the report, what tables are
involved, and what fields and data types those tables have.

Patrick
0
 

Author Comment

by:Bergkamen
ID: 16354783
Hi Patrick,

after posting my last thought, I went through all the options and tested some of them. I ended up with merging the two tables (Funds and Funds_Other) and simplifying the query. Everything works well. I have just to make sure that the operator can not enter letters and spaces while entering the Funds (numbers only!).
Sometimes what looked like the cleanest attemped turns out to be the most complicated one. The keep the two tables separated was nice, but not very good in practice. I am still using your function to sort through the records.

Thanks to everybody, thanks Patrick.

You earned the points!
 
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question