append records to query

[peace77]

I need help with ACCESS query. I am not at all good with access queries and am unable to figure this out even though it may have an easy solution.

There is an existing query designed by our company in a database. This query links different fields from 4 more queries and finally generates 16 columns as shown below. (I have also attached excel file with the same example as the text below is not aligned)

Name(1) ReportDate(2) TB(3) MV(4) NC(5) AI(6) SN(7) LN(8) SID(9) Country(10) PVS(11) BMV(12) Fund Code(13) MV by Fund code(14) Total TNAD(15) CUR(16)
ABC 3/31/2008 USD A CD UD na 0 $150.00 abcd $731.00 $745.00 CAD
ABC 3/31/2008 USD B CD UD na 0 $256.00 abcd $731.00 $745.00 CAD
ABC 3/31/2008 USD C CD UD na 0 $325.00 abcd $731.00 $745.00 CAD
XYZ 3/31/2008 CAD A CD UD na 0 $112.00 wxyz $387.00 $420.00 CAD
XYZ 3/31/2008 CAD B CD UD na 0 $275.00 wxyz $387.00 $420.00 CAD
PQR 3/31/2008 EUR C CD UD na 0 $60.00 pqrs $166.00 $200.00 CAD
PQR 3/31/2008 EUR A CD UD na 0 $72.00 pqrs $166.00 $200.00 CAD
PQR 3/31/2008 EUR B CD UD na 0 $34.00 pqrs $166.00 $200.00 CAD


In this example I am just showing 8 records, but in reality there usually about 4000 records and the number changes every day.


Now I am asked to design a query based on the query that gives the above results.

The 14th column (heading –MV by Fund code) actually sums up the market values in column 12 (BMV) that has same fund code in column 15. For example for fund code “abcd” the BMV are $150, $256 and $325 which sum upto $731 and hence this number shows up in column 14 against each record with “abcd” fund code.

Now there is a difference of $14 between column 15 (heading-Total TNAD) and column 14 (heading- MV by Fund Code) for fund code “abcd”. I am asked to design a query that will add records on the results generated above by the existing query.

The new record added should show the difference of $14 in column 12 and text “OTH” column 8 and column 9 and should show the fund code “abcd” . The values in remaining columns should be the same as the fund code “abcd”.

The above logic would then apply to funds “wxyz”, “pqrs” and so on

In this example the following three records should be added below the above 8 records with these details

ABC 3/31/2008 USD A OTH OTH na 0 $14.00 abcd $731.00 $745.00 CAD
XYZ 3/31/2008 CAD A OTH OTH na 0 $33.00 wxyz $387.00 $420.00 CAD
PQR 3/31/2008 EUR B OTH OTH na 0 $34.00 pqrs $166.00 $200.00 CAD


I guess this would require coding in SQL. But I am unable to do so.

Any help is highly appreciated

Thanks,

Peace

 

[Guus2005]

The solution is not complicated.

Lets say that the above query producing the 8 records is qryA
We can use this query to produce the three records you want to add.
Adding records is done using the keyword UNION. With UNION you can add records to a query. In fact you are pasting the results of one query to another.
Create qryB:

Select Name, ReportDate, TB, MV, NC, AI, "X" as SN, LN, SID, country, PVS, [Total TNAD] - [MV By Fund Code] as BMV, [Fund Code], [MV By Fund Code], [Total TNAD], CUR from qryA group by Name, ReportDate, TB, MV, NC, AI, "X" as SN, LN, SID, country, PVS, [Total TNAD] - [MV By Fund Code] as BMV, [Fund Code], [MV By Fund Code], [Total TNAD], CUR

Create qryC:

Select Name, ReportDate, TB, MV, NC, AI, SN, LN, SID, country, PVS, BMV, [Fund Code], [MV By Fund Code], [Total TNAD], CUR from qryA
UNION ALL
Select Name, ReportDate, TB, MV, NC, AI, SN, LN, SID, country, PVS, BMV, [Fund Code], [MV By Fund Code], [Total TNAD], CUR from qryB

This should give you the requested result.
Note that the field SN in the added records have "X". If you want them to be A, A and C as in your example, then things are getting a lot more complicated and slower.

This query was not tested so it might give you an error.

HTH

 

[peace77]

Thanks so much Guus2005. You have been a saviour. I ran the query suggested by you and it does work. However there is one glitch with queryb.

In the example that I showed earlier , queryb should generate only three records. But when I run this query it generates 9 records in total, 3 are unique and 6 are duplicates. I just want to append the unique records to querya. Is there a way of doing so?

Please help.

Thanks,

Peace

 

[neileg]

Substitute UNION for UNION ALL.

 

[Guus2005]

You have to "group by" qryB leaving unique records only.

 

[peace77]

Thanks for the help. I could finally run by using the Group clause in sql statement for queryc. Substituting Union to Union All couldn’t work as for some reason the number of records were less than I should have got.


I have a final question on this query. I did not envisage this before so missed out in my initial question.

Once those new records are added, I need to update or change the values in [MV By Fund Code] generated by query c. This field should sum up the values in BMV for each fund code. In Excel I could use sumif function but I am not sure how to do this in Access?

Please give some suggestions on how to do it..Thanks a lot for the help.

Peace

 

[Guus2005]

It doesn't matter in the end if you groupby qryB or qryC. Using a groupby on qryB works faster however.

You lost me on your last question. I am not sure what you want.

 

[peace77]

I am sorry for not having been able to explain my question well.

I have again attached the excel spreadhsheet that shows the final results that I need to generate (it’s from row 20 to row 30 in the attached spreadsheet). After adding the new records to the existing records, I need to update column N which is the sum of values (in column L) for each fund code (in column M).

In excel I can use the “SUMIF” formula to generate the desired results but I am not sure how to do that in Access. Would really appreciate if you can suggest a way to do so.

Thanks for the help.

Regards,

Peace