Link Excel Data to append ACCESS table

[MNNovice]

I am working on a DB to record expenses related to various grants. To record Payroll expenses I created tblPayroll with these fields:

PayrollID – auto / PK
ECHOID – Number FK (tied to tblECHO)
PayrollNo – Text
PayrollDt – Date/ Time
AccountID – Number FK (tied to tblAccounts)
FundID – Number FK (tied to tblGrantFunds)
OrgID – Number FK (tied to tblOrg) 
ProgramID – Number FK (tied to tblProgram)
SubClassID – Number FK (tied to tblSubClass)
ProjectID – Number FK (tied to tblProject)
PayAmount – Currency

Each pay period payroll data is downloaded from the server and dumped into an excel file. This spreadsheet has the following info:

Account
Fund
Org
Program
Sub Class
Project
Amount

Now if I were to append data to tblPayroll each pay period, what will be the best way to do it? Obviously I don’t want to fill in the other information (that are missing from the spreadsheet) manually. Any guidance will be much appreciated. Thanks.

[Denburt]

I haven't linked to many Excel sheets but this seems like a logical path to take. I would create a form that had the pertinent fields that needs to be filled in by the user then the update query will be able to take that with the information from excel and update the Access table.
I believe the would contain the following fields. I would probably include the data from the excel spreadsheet just so you can review it.

ECHOID – Number FK (tied to tblECHO)
PayrollNo – Text
PayrollDt – Date/ Time
 

[MNNovice]

Denburt:

I didn't quite understand how to do this when you say:

I would create a form that had the pertinent fields that needs to be filled in by the user then the update query will be able to take that with the information from excel and update the Access table.

There is no manual data entry involved to input the payroll data. Because it's a large file that gets dumped from the server (we use accounting software PeopleSoft). What form you are refering to?

tblPayroll has GrantFundID that replaced FundID. Excel file has Fund numbers, how am I going to update tblPayroll so that it can identify the Fund number and automatically associate the Grant Number that's associated with that fund number?

This data in tblPayroll will have to be updated every two weeks as people get paid.

This is a little complex for me. Can you please give me the step by step guide to it?

Thanks.

[Denburt]

Start with linking in the payroll sheet and for the purpose of this example name that linked table sheet1 (so the following query will work). Start with the query when you are in design view you can click on the query button on the menu bar and change the type of query you want. Since I have a copy of your DB I helped you out a little. Start a new query and view it in SQL View, then paste the following into it and it and then view it in Datasheet or design view look it over then when you run the query it should add the records you see in Datasheet view. I think once you do that you will have a better understanding.

I also wanted to point out that you aren't using many if any of the properties for these tables and I'll tell you this, if you go step back and add the captions use the look up for your foreign keys etc. then it will help when you design your forms.

INSERT INTO tblPayroll ( AccountID, GrantFundID, SubClassID, ProjectID, ECHOID )
SELECT Sheet1.Account, tblGrantFunds.GrantFundID, tblSubClass.SubClassID, tblProjects.ProjectID, 12 AS [I Need to put something in here for EchoID or dont I]
FROM (((Sheet1 INNER JOIN tblFunds ON Sheet1.Fund = tblFunds.FundNo) INNER JOIN tblGrantFunds ON tblFunds.FundID = tblGrantFunds.FundID) INNER JOIN tblSubClass ON Sheet1.[Sub Class] = tblSubClass.SubClassNo) INNER JOIN tblProjects ON Sheet1.Project = tblProjects.ProjectNo;

[

[MNNovice]

Denburt:

Thanks I will give it a try and keep you posted.

[MNNovice]

I didn't succeed. It keeps adding a field F10 at the end of the table. frmEchoEnter does not get updated. I am sure I am not doing something right but what is it - don't know.

I tried several times with the same result. Here is what I did.

1. Created a link table called Sheet1
2. Created a query based on this table called Sheet 1 Query
3. Opened the query in Design view and switched to SQL view to insert this code

INSERT INTO tblPayroll ( AccountID, GrantFundID, SubClassID, ProjectID, ECHOID ) 
SELECT Sheet1.AccountID, tblGrantFunds.GrantFundID, tblSubClass.SubClassID, tblProjects.ProjectID, 09-064 AS [ECHOID] FROM (((Sheet1 INNER JOIN tblFunds ON Sheet1.FundID = tblFunds.FundNo) INNER JOIN tblGrantFunds ON tblFunds.FundID = tblGrantFunds.FundID) INNER JOIN tblSubClass ON Sheet1.[SubClassID] = tblSubClass.SubClassNo) INNER JOIN tblProjects ON Sheet1.ProjectID = tblProjects.ProjectNo;

4. Opened Sheet 1 Query in datasheet view
5. I get no data...

Well, that's where it ended.

Even if I succeeded I am not sure

1. How will tblPayroll be populated with these data?
2. What are the steps to take when I have payroll 9, 10 ,...keeps coming?

Thanks for your help.

[Denburt]

I see that I did forget to add the tblAccounts table and the appropriate relationship with the Excel sheet. Also you have changed the field echoID to represent an echo number not an ID number.

You have to remember that the Excel sheet is going to have an account No. not an ID so you need to associate all of the fields from the Excel sheet to their appropriate table by using their No or Name then you can extract the ID and return that to be inserted in the payroll table.

I also have my Excel sheet using the fields names that you posted above.

Here is an adjusted SQL statement that should work if you rename the excel sheets header information to the information above:

This spreadsheet has the following info:

Account
Fund
Org
Program
Sub Class
Project
Amount

INSERT INTO tblPayroll ( AccountID, GrantFundID, SubClassID, ProjectID, ECHOID )
SELECT tblAccounts.AccountID, tblGrantFunds.GrantFundID, tblSubClass.SubClassID, tblProjects.ProjectID, 66 AS ECHOID
FROM tblAccounts INNER JOIN ((((Sheet1 INNER JOIN tblFunds ON Sheet1.Fund = tblFunds.FundNo) INNER JOIN tblSubClass ON Sheet1.[Sub Class] = tblSubClass.SubClassNo) INNER JOIN tblProjects ON Sheet1.Project = tblProjects.ProjectNo) INNER JOIN tblGrantFunds ON tblFunds.FundID = tblGrantFunds.FundID) ON tblAccounts.AcctNo = Sheet1.Account;

Let me know how it goes.

[Denburt]

@MNNovice
Not sure why your getting F10 at the end of your table did you make sure you have column titles for all the columns? If so then I wouldn't be bothered with it i have seen excel do weird things with empty columns before.

Also I may not have this right but you said this is payroll so I set the query up so that it will update the tblPayroll no other table will be updated at this time.

@MNNovice
It will be populated when you run the query we can look at that once we get this query straight.

Is the data that gets dumped in this Excel sheet added to the existing data or is it replaced by new data each month? If it is added then you will need to set the criteria in the query so it only pulls the data for that month.

[MNNovice]

Denburt:

Thanks. It's almost time for me to leave for the day. But to answer your question:

Is the data that gets dumped in this Excel sheet added to the existing data or is it replaced by new data each month? If it is added then you will need to set the criteria in the query so it only pulls the data for that month.

Each payroll will be added to the existing data in tblPayroll. PayrollNo indicates the sequence of payroll in a given year. eg, 09-08 (for year 2009, it's the 8th payroll). Hope this makes sense now.

I will look at your instructions next week.

Have a good weekend and many thanks for your patience and help. Regards. MN

[MNNovice]

Denburt:

I see that I did forget to add the tblAccounts table and the appropriate relationship with the Excel sheet. Also you have changed the field echoID to represent an echo number not an ID number.

It should EchoNo (text file). I can manually enter this field into the excel file each pay period. And import this data each pay period. No problem. Will this require a revision of the SQL you sent? Or am I misunderstanding something entirely?

I also wanted to point out that you aren't using many if any of the properties for these tables and I'll tell you this, if you go step back and add the captions use the look up for your foreign keys etc. then it will help when you design your forms.

Are you suggesting I should add Look Up combo boxes in table's design view? If yes, then I need to tell you that NeoPa (for a different DB) suggested that it's NOT a good practice. So I am avoiding on this new DB. Let me know.

Last week when I attempted to follow your instructions I didn't succeed. But when I hit "run" for query, I got the message that reads: "Type mismatch in expression".

Just FYI: the Excel file has these fields:

PayrollNo
PayrollDt
Account
Fund
SubClass
Project
PayAmount

Just a couple of questions before I venture into it. What happens to PayrollID, PayrollNo and PayAmount? How did these fields fit into this SQL you sent me?

Well, I ran the query and

Thanks & regards. M

[Denburt]

The payrollID is autonumber so that should take care of itself the Amount field in the excel sheet should be added by selecting the table (sheet1) and set the field to Amount (if that's its name) Then below in the "Append to:" you can select the amount field from tblPayroll and that should be there. You are essentially taking any data from the top fields in inserting them into the fields below in the table that is designated in the properties box under Destination Table. It appears that you may have to enter the PayrollNo as well as the echo number then use the update query.

Are you suggesting I should add Look Up combo boxes in table's design view? If yes, then I need to tell you that NeoPa (for a different DB) suggested that it's NOT a good practice. So I am avoiding on this new DB. Let me know.

Interesting I would like to hear more about this. One point of mentioning this is that it can save time when designing. Say your form is in design view and you drag a field from the field list as soon as you drop it the caption is in the label, the control will either be a list box or combo depending on what you selected and it will be ready to roll. I don't recall hearing anything negative about this practice but I don't know everything.

[MNNovice]

I keep getting an error message of: Type mismatch in expression.

I checked all the fields in excel files and changed numbers to text to match the text fields in Access but still keep getting the same error message. What am I doing wrong?

This is what I have for the query

INSERT INTO tblPayroll ( AccountID, GrantFundID, SubClassID, ProjectID, ECHONo, PayrollNo, PayAmount )
SELECT tblAccounts.AccountID, tblGrantFunds.GrantFundID, tblSubClass.SubClassID, tblProjects.ProjectID, Sheet1.ECHONo, Sheet1.PayrollNo, Sheet1.PayAmount
FROM (tblAccounts INNER JOIN (((Sheet1 INNER JOIN tblFunds ON Sheet1.Fund = tblFunds.FundNo) INNER JOIN tblSubClass ON Sheet1.SubClass = tblSubClass.SubClassNo) INNER JOIN tblProjects ON Sheet1.Project = tblProjects.ProjectNo) ON tblAccounts.AcctNo = Sheet1.Account) INNER JOIN tblGrantFunds ON tblFunds.FundID = tblGrantFunds.FundID;

Thanks.

[Denburt]

First the Echo table needs to be added to the query and the ID entered into the Payroll table (see tblSubclass or tblProjects in this query for an example) You must be missing something else somewhere also I think. The Excel sheet I am using does not have any fields formatted and works fine with the query you provided (Except for EchoNo). I would create a new Excel sheet then insert some data manually (pasting might also change the fields format in Excel) for a couple of records then format them one by one to suit your needs.

[MNNovice]

Denburt:

I added tblECHO but the outcome remains the same. I get the same error message. Can you please have a look at the DB? (attached)

[Denburt]

@MNNovice
Got it

OK I opened it and looked at your query and almost all the links table to table were broken... Well I set up a NEW spreadsheet and removed the field EchoNo from the query since this is handled with EchoID and you did add (that nicely done)..
I relinked the tables in the query and I hit the same error. I then took a look in the Excel sheet and noticed that any field that has all numeric numbers in a field in Excel will need to have that formatted specifically to text if indeed the database has it stored as text for the corresponding table, once the format is changed you may need to manually re-input the data (I did). Once I did that it seemed to work nicely.
You didn't send the Excel Sheet but this should do it for you.

INSERT INTO tblPayroll ( AccountID, GrantFundID, SubClassID, ProjectID, PayrollNo, PayAmount, ECHOID )
SELECT tblAccounts.AccountID, tblGrantFunds.GrantFundID, tblSubClass.SubClassID, tblProjects.ProjectID, Sheet1.PayrollNo, Sheet1.PayAmount, tblECHO.ECHOID
FROM ((tblProjects INNER JOIN (tblFunds INNER JOIN (tblECHO INNER JOIN (Sheet1 INNER JOIN tblAccounts ON Sheet1.Account = tblAccounts.AcctNo) ON tblECHO.ECHONo = Sheet1.EchoNo) ON tblFunds.FundNo = Sheet1.Fund) ON tblProjects.ProjectNo = Sheet1.Project) INNER JOIN tblSubClass ON Sheet1.[Sub Class] = tblSubClass.SubClassNo) INNER JOIN tblGrantFunds ON tblFunds.FundID = tblGrantFunds.FundID;

[MNNovice]

Denburt:

any field that has all numeric numbers in a field in Excel will need to have that formatted specifically to text if indeed the database has it stored as text for the corresponding table,

1. Are you referring to the fields in the Excel file? For example, Account (in Excel file) need to be changed to be formatted as text and NOT number?
2.

once the format is changed you may need to manually re-input the data (I did)

Manually re-input which data and where? All data in the Excel file?

I am wondering if it would be lot simpler for the end user to re-input data using sfrmPayroll on fromEchoEnter.

Thanks.

[Denburt]

@MNNovice
Yes MS Access looks at the Excel file and any fields that appear to be numbers get designated as such even though their related part in MS Access is a text field hence the errors. So if a related field in MS Access is designated as text you should format the related field in Excel as text.

I merely had several records in an Excel file for testing once I changed the formats for several Excel fields I had to re input those few records for the change to take effect. When you get your data as you say you do on a regular basis it may or may not be an issue, we can always deal with on a later basis.

[Denburt]

ok I took a look at your Excel file and ran a few more tests and this is what I have found. In Excel have you seen the green box in the top left of a cell that tells you this number is stored as text (look at your sheet then highlight a field that has a number hit f2 then enter it should show up)? Well as long as I see that in the Excel file then MS access has no problem treating this as a text field however when I don't see that then MS Access treats it as a number. once I made that adjustment it worked quite well. When they do a data dump I am not sure how it will handle this so that is what you need to look for and it should be easy to create a macro in Excel to handle this if it is needed. The only other issue I see is if they decide to add an EchoNo or another item from one of your foreign tables then you will not update those records until it is added in the appropriate table such as EchoID. If you are not sure how MS Access is treating your Linked table you can view it in design view and see if the fields you need are being treated as text or number. I hope this helps.

[NeoPa]

It is a very common problem when dealing with record type data in Excel (Importing / exporting etc) that the type of the numeric data (not the format, but the type the data is stored as) causes problems that are not obvious to spot.

This is so common it probably accounts for more than half of the problems I ever see in Excel.

To convert from one type to the other put a formula in one column to produce the results you want, then copy this column. Use Edit / Paste Special / Values over the original column to adjust the data permanently. The new column is no longer required after this point.

[NeoPa]

To convert from string to numeric is simple enough.

=Val(A1)

To convert the other way is less so. A simple conversion is :

=Text(A1,"General")

A more common requirement though, is to require the string equivalent to be at least X number of characters. If we take X=6 then this is often done as :

=Text(A1,"000000")

PS. I should add that to get the formula into the whole column simply copy the top cell and, after selecting all cells in the range, paste it back in.

[Denburt]

Thanks for the tidbits NeoPa, those things are good to know. I don't usually do to much in Excel or I try not to :) But I find myself clunking around in it from time to time. Usually sorting out or cleaning up formulas and such that others have written which can be very intersting at times.

[MNNovice]

Denburt / NeoPa

All day yesterday I struggled through this issue so much so that I got totally frustrated and decided not to pursue at this time.

As an alternate option, I chose to simply key in the payroll data. To my pleasant surprise it was not that big. However, I would like to learn the other way where I don't have to type in this info. May be when I have more time to deal with it.

I kept these notes in a file and shall look it over and give it another try at a later date. Many thanks for your help. Please see my other posting on "combining reports".

Thanks & regards.

[NeoPa]

You're more than welcome Den.

I play there quite a lot, and also have a few users who often get stuck on that particular problem.

[NeoPa]

@MNNovice
I'd be happy to go there if you provide a link.

[MNNovice]

NeoPa:

Here is the link to that posting. Thanks.

http://bytes.com/topic/access/answer...ombine-reports