?
Solved

MS Excel / MS Access

Posted on 2011-02-20
11
Medium Priority
?
821 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 5

Accepted Solution

by:
roger_karam earned 2000 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

801 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