MS Excel / MS Access

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
chitralekhaaAsked:
Who is Participating?
 
roger_karamConnect With a Mentor Commented:
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
 
chitralekhaaAuthor Commented:
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
 
chitralekhaaAuthor Commented:
I have the database setup too. I will upload the file tomorrow. Thanks.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
roger_karamCommented:
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
 
chitralekhaaAuthor Commented:
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
 
roger_karamCommented:
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
 
roger_karamCommented:
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
 
chitralekhaaAuthor Commented:
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
 
roger_karamCommented:
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
 
chitralekhaaAuthor Commented:
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
 
roger_karamCommented:
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
All Courses

From novice to tech pro — start learning today.