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]

I didn't succeed. It keeps adding a field F10 at the end of the table. frmEchoEnter does not get updated.

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.

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.

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 misunderstandin g 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]

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)

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;