We help IT Professionals succeed at work.

complicated query for report

Bergkamen
Bergkamen asked
on
Medium Priority
239 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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
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

Author

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

Author

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
CERTIFIED EXPERT
Top Expert 2010
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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!
 
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.