Solved

MS Excel / MS Access

Posted on 2011-02-20
11
795 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
Comment Utility
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
Comment Utility
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
Comment Utility
I have the database setup too. I will upload the file tomorrow. Thanks.
0
 
LVL 5

Expert Comment

by:roger_karam
Comment Utility
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
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 5

Expert Comment

by:roger_karam
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now