Solved

MS Excel / MS Access

Posted on 2011-02-20
11
800 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

919 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

13 Experts available now in Live!

Get 1:1 Help Now