help in converting excel to access

[Daniel2000]

have an excel programme which uses sumifs and wants to impiort same as an access database.



have created the tables and queries but needs help with formulas!!!!

[rpeare]

what is your original formula and what are your field names from your access table (the source data that are part of the formula)

[Daniel2000]

what is your original formula and what are your field names from your access table (the source data that are part of the formula)

Hi rpeare,

Thank you very much for your answer.

Attached is the excel spreadsheet. I have coding, working and output worksheet. Coding is the QB data that I will me mapping to get reasult as per output, working is the input data which will be updated monthly (col F and G). Basically I am doing a vlookup in working and sumif in the output.

I have already imported the coding and input as 2 tavles in access. I then run a query to get the results but getting problems with the following:

1. While I am able to get results for fields like PL A, etc... I am not able to get the result for the fields marked as formula in output worksheet col K.
2. Secondly, since I will be updating the input file monthly, what is the best way of updating the table in access, taking into consideration that I need to keep a history of the input file for comparison.

Thanking you so much in advance.

[Daniel2000]

forgot to attached the file. I am now attaching the excel workboot.

[Daniel2000]

attachment

[June7]

I can't download and review the worksheet now. If you would just post one of the SUMIF expressions, will try to translate to Access version. Probably would be like:

Sum(IIf([fieldname]="something",1,0))

[Daniel2000]

=IFERROR((SUMIF(Table1[[#All],[cbs coding]],K14,Table1[[#All],[Mar 13]])),0)/1000

[rpeare]

Ok so the Sumif function works like:

SUMIF( range, criteria, [sum_range] )

In access you can do one of two things. Limit your criteria of your query so that it only shows values that meet your requirements and sum the value you're interested in

The second is to do what June suggested. IN your formula you're substituting a value in cell K14, in access you'd either have to supply that value or have it stored in a table where your query could read it.

[Daniel2000]

thanks rpeare and June. Still it is not clear to me and the issue is NOT with the sumif. My query is giving me the results of A3,A4, A5 and A6 on a form. On the same form I have A1, A2 and A7 which will be condition based on the results of the query to get A3,A4,A5 and A6.

A2 will be result from query A3 + A4. A7 will be result from query A5 + A6.

A1 is result A2 - A7.

How to update A2, A7 and A1 on the same form with the formula above!!!!!

[June7]

Why is Sum(IIf...) even needed? Forget how things are done in Excel, Access is a totally different animal. If you want to add fields A3 and A4 in textbox named A2 then simply: =A3 + A4

In A7, =A5 + A6

In A1, =A2 - A7

[Daniel2000]

June, thanks for the response. the A1 to A7 are rows in a query. when Im run my query, i get the results for A3 to A6. how can i define A7 = A5 + A6 in same query to update a specific row (say A2)? can you please give me an example? I have used a second query just to get the result of A2 and A7 and then used a 3rd query to link the result of second query to A2. It is working but then I used a 4th query to get A1 and the linking of this fourth query is not working.

[June7]

You are trying to treat Access table like an Excel worksheet. A relational database works differently.

Normally, calculated results are not saved. Review http://allenbrowne.com/casu-14.html

Calculations involving multiple records (sum, average, count) are done with aggregate (Totals) queries or on reports http://fontstuff.com/access/acctut04.htm