If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Creating Query to pull info from Linked Excel Spreadsheet
First of all, I am new to Access 2007. I have a profile table and a linked
table from Excel. I built a query to pull information on amount owed for current month. The heading for the field in the excel spreadsheet is: Jan-09 Amount Owed, Feb-09 Amount Owed, etc. I need the info for the current month to show me all the tenants that have a balance due greater than 0. Can someone point me in the right direction or do you need more info. Thanks in advance for any help you can give me. |
#2
|
|||
|
|||
Creating Query to pull info from Linked Excel Spreadsheet
It's virtually impossible to programattically determine the month from the
headings for the fields in a spreadsheet. Since you are using Access anyway, why not simplify everything by putting all your data in Access. You need the following tables: TblTenant TenantID probably all your profile fields TblUnit UnitID UnitNumber TblUnitRent UnitRentID AsOfDate UnitRent TblUnitTenant UnitTenantID UnitID TenantID StartDate EndDate TblTenantRent TenantRentID UnitTenantID UnitRentID TblRentPayment RentPaymentID TenantRentID DatePaid Now with the proper query, you can get a list of tenants with a balance due for the current month. Steve "Marilyn Myers" wrote in message ... First of all, I am new to Access 2007. I have a profile table and a linked table from Excel. I built a query to pull information on amount owed for current month. The heading for the field in the excel spreadsheet is: Jan-09 Amount Owed, Feb-09 Amount Owed, etc. I need the info for the current month to show me all the tenants that have a balance due greater than 0. Can someone point me in the right direction or do you need more info. Thanks in advance for any help you can give me. |
#3
|
|||
|
|||
Creating Query to pull info from Linked Excel Spreadsheet
I will try this tomorrow when I go back to work. Thank you so much for your
help and Happy New Year! Marilyn "Steve" wrote: It's virtually impossible to programattically determine the month from the headings for the fields in a spreadsheet. Since you are using Access anyway, why not simplify everything by putting all your data in Access. You need the following tables: TblTenant TenantID probably all your profile fields TblUnit UnitID UnitNumber TblUnitRent UnitRentID AsOfDate UnitRent TblUnitTenant UnitTenantID UnitID TenantID StartDate EndDate TblTenantRent TenantRentID UnitTenantID UnitRentID TblRentPayment RentPaymentID TenantRentID DatePaid Now with the proper query, you can get a list of tenants with a balance due for the current month. Steve "Marilyn Myers" wrote in message ... First of all, I am new to Access 2007. I have a profile table and a linked table from Excel. I built a query to pull information on amount owed for current month. The heading for the field in the excel spreadsheet is: Jan-09 Amount Owed, Feb-09 Amount Owed, etc. I need the info for the current month to show me all the tenants that have a balance due greater than 0. Can someone point me in the right direction or do you need more info. Thanks in advance for any help you can give me. |
#4
|
|||
|
|||
Creating Query to pull info from Linked Excel Spreadsheet
P.S. I might need some help building my query, I will let you know after I
build those tables. Thanks Again. M "Marilyn Myers" wrote: I will try this tomorrow when I go back to work. Thank you so much for your help and Happy New Year! Marilyn "Steve" wrote: It's virtually impossible to programattically determine the month from the headings for the fields in a spreadsheet. Since you are using Access anyway, why not simplify everything by putting all your data in Access. You need the following tables: TblTenant TenantID probably all your profile fields TblUnit UnitID UnitNumber TblUnitRent UnitRentID AsOfDate UnitRent TblUnitTenant UnitTenantID UnitID TenantID StartDate EndDate TblTenantRent TenantRentID UnitTenantID UnitRentID TblRentPayment RentPaymentID TenantRentID DatePaid Now with the proper query, you can get a list of tenants with a balance due for the current month. Steve "Marilyn Myers" wrote in message ... First of all, I am new to Access 2007. I have a profile table and a linked table from Excel. I built a query to pull information on amount owed for current month. The heading for the field in the excel spreadsheet is: Jan-09 Amount Owed, Feb-09 Amount Owed, etc. I need the info for the current month to show me all the tenants that have a balance due greater than 0. Can someone point me in the right direction or do you need more info. Thanks in advance for any help you can give me. |
#5
|
|||
|
|||
Creating Query to pull info from Linked Excel Spreadsheet
Happy New Year to you too!!
I forgot to include the field "UnitID" in TblUnitRent. Please add that field when you build the tables. Steve "Marilyn Myers" wrote in message ... I will try this tomorrow when I go back to work. Thank you so much for your help and Happy New Year! Marilyn "Steve" wrote: It's virtually impossible to programattically determine the month from the headings for the fields in a spreadsheet. Since you are using Access anyway, why not simplify everything by putting all your data in Access. You need the following tables: TblTenant TenantID probably all your profile fields TblUnit UnitID UnitNumber TblUnitRent UnitRentID AsOfDate UnitRent TblUnitTenant UnitTenantID UnitID TenantID StartDate EndDate TblTenantRent TenantRentID UnitTenantID UnitRentID TblRentPayment RentPaymentID TenantRentID DatePaid Now with the proper query, you can get a list of tenants with a balance due for the current month. Steve "Marilyn Myers" wrote in message ... First of all, I am new to Access 2007. I have a profile table and a linked table from Excel. I built a query to pull information on amount owed for current month. The heading for the field in the excel spreadsheet is: Jan-09 Amount Owed, Feb-09 Amount Owed, etc. I need the info for the current month to show me all the tenants that have a balance due greater than 0. Can someone point me in the right direction or do you need more info. Thanks in advance for any help you can give me. |
Thread Tools | |
Display Modes | |
|
|