asked on

complicated query for report

I like to refer to my question:
https://www.experts-exchange.com/questions/21782505/ORDER-BY-Val-Table-B-Number-doesn't-work.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

Patrick Matthews

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

Bergkamen

ASKER

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

Bergkamen

ASKER

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

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bergkamen

ASKER

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!