complicated query for report

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
BergkamenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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
BergkamenAuthor Commented:
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
BergkamenAuthor Commented:
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
Patrick MatthewsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BergkamenAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.