Solved

MS Excel / MS Access

Posted on 2011-02-20
11
805 Views
Last Modified: 2012-05-11
I have attached the file. I have 2  worksheets
1) For state of ME col J =colum E*0.05                                                                              
1)  ME Withholding . I enter the data I ME  Withholding daily. Later as required the JE colum is filled with the dates. It is left blank in some cases.                                                                        
2) ME Processed. In this worksheet I enter the data from ME Withholding  I copy the rows after I update rows with JE column, and those rows are pasted in ME Processed worksheet.                                                                              
Later I select the data and run subtotal.                                                             
                        
My question is                                                                              
I would like to upload the rows from ME Withholding to ME Processed as soon I enter date in JE colum of ME Withholding . Is there any formula or macro for this ? Or is there any way this could be incorporated in  MS Access database.                  ME Witholding                                                                                           
Note : I have one more file which is a has same structure as this file. That file is for CA only the percentage is .
For state of CA Col J has formula E*0.07
                                    
Test-ME.xls
0
Comment
Question by:chitralekhaa
  • 6
  • 5
11 Comments
 
LVL 5

Accepted Solution

by:
roger_karam earned 500 total points
ID: 34939788
Hello chitralekhaa,

You could definitly use a macro, but there are other ways that might be simpler and easier for you to adapt in the future.

Have you tried using pivottables? They would be more dynamic than macros and if you sort it out by the JE Column you can filter the ones with no values.

Another way to do it would be to use a vlookup formula but with an if statement to see if JE is blank.

see attached for examples. Is that what you are looking for?

Regarding the access database, it is possible through a Macro or you could use access to import, however for this it would be better to understand your database setup. If you could inform the database file name, table name, and field names, it is possible to setup a macro that adds and/or updates the fields.

-RK
 Test-ME-v01.xls
0
 

Author Comment

by:chitralekhaa
ID: 34940093
I liked the VLookup. Thanks. I have a question. Will the subtotal work on the Processed sheet ? I tried subtotal, I get the subtotal, but when I make changes to the Withholding spreadsheet, it does not reflect on the processed.
0
 

Author Comment

by:chitralekhaa
ID: 34940155
I have the database setup too. I will upload the file tomorrow. Thanks.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 5

Expert Comment

by:roger_karam
ID: 34943109
If you are using the Data-> Insert Subtotal, you need to re-do it make sure that "Replace Current Subtotals" is selected. This is because the Subtotals inserts a new row for the subtotals and won't take into account the empty rows that don't have a date yet.

If you are using formulas, the subtotal should work as long as the formula includes all the right rows/columns. If your "calculate" is set to manual, you might have to force a re-calculate (F9).

You could also use a pivot table on the Vlookup sheet for your sub-totals.

-RK
0
 

Author Comment

by:chitralekhaa
ID: 34946027
Thanks for the above comment. I am attaching the database I created. There is a taxamount table (like main worksheet) and process table (like process worksheet)
I have a query which append those process table with the JE dates rows from taxamount table. The problem is it picks up all the entries and that duplicated the process table.
The database was originally created in 2003
test-db.mdb
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34947400
do you want the Access do import from the excel or do you want totally separate but doing the same thing as the excel (update the process table from the taxamount table)?
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34947472
In case you are trying to do it in Access, it would be alot simpler to just create a new Query that gets everything from the Taxamount table however has a criteria for JE Date where  >#01/01/1900#. That would be the best way since every time you run the query it will check the data from taxamount.

If you create a new blank query, check it in SQL view and put this in:


SELECT tblTaxAmount.[File], tblTaxAmount.[Cat], tblTaxAmount.[Settlement Date], tblTaxAmount.[Security Number], tblTaxAmount.[Gross Amt], tblTaxAmount.[Ledger], tblTaxAmount.[TEFRA Amt], tblTaxAmount.[State], tblTaxAmount.[State Ledger], tblTaxAmount.[Tax], tblTaxAmount.[Date of JE], tblTaxAmount.[No Wiithholding], tblTaxAmount.[Reversal], tblTaxAmount.[Comments], tblTaxAmount.[Status Code], tblTaxAmount.[Entry No]
FROM tblTaxAmount
WHERE (((tblTaxAmount.[Date of JE])>#1/1/1900#));
0
 

Author Comment

by:chitralekhaa
ID: 34947477
I don't want to import from excel. I get the data in a text file. I will be creating an import spec so the data is loaded in taxamount table. Later as I  fill with JE date colums, I would like the process table to be appended with new rows with JE dates. Currently the append query appends all the rows with the JE dates. Is there a way to append only the new rows ?
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34947525
Here is your file with the query I mentioned (named qryTaxAmountwithJE_Date). IMHO using a new query is better than appending to a different table because you avoid duplicate data.
-RK
test-db-v1.mdb
0
 

Author Closing Comment

by:chitralekhaa
ID: 34950762
I think I will go with excel solution. The date query does not append to the table.  The reason I wanted to append to a table to maintain records. Also it selects all the data from the taxamount table.
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34954536
Thanks for the points chitralekhaa.

I'm not sure what you are looking for in the Access solution, but you can make a mix query with two tables that will include extra data if you want. example: have a tblProcessInfo that has the File name as well that has a relationship to the tblTaxAmount info. Then your query will get the data from both tables. Let me know if you would like to see an example.

-RK
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question