Hello. Sorry this will be in multiple parts as I am so new to Access. I am just starting to learn how to build an access db, and I have lots of questions. These are the pressing ones at the moment.
I am building a database in order to track gas surveys. What I need help with is the best method for importing info, and calculating a due date from that info.
I have a survey table [SurveyPatrolT] which is a linked table (not sure if this is the best method to do this) but this table is brought in from my company database (Oracle based and asking them to modify or add a query for me is like asking for an act of God). The problem is, the company database is not user freindly. IE I cannot limit the information to just my districts and my surveys. (I have 7 of the 29 districts and 7 of the 60 survey types) but I can either look at one district and all survey types, or one district and one survey type, or all districts and one survey type and that is it. I cannot looke at just my Division. Therefore I think I need to export the information to an excel file and then link it to my database. I am assuming this is the best method because there are due dates in this information that are updated daily when a survey is completed.
The next struggle I have is that the company system calculates a due date but it is based on each month having 30 days. Therefore I need to calculate a correct due date. However, I have different survey types and each type has a different due date schedule. I understand the DateAdd function which works for my 5 year surveys but I have surveys that have a due date that is based on 3.5 months and 7.5 months. This does not work with the DateAdd function as it seems to drop the .5 portion and I'm not sure how to add a day interval along with the month interval. I have also tried the DateSerial function but I am apparently not doing something correct as I am creating an unbound field with ActualDueDate: DateSerial([SurveyPatrolT]![Last Completed Start Date],[SurveyPatrolT]![Last Completed Start Date]+7,[SurveyPatrolT]![Last Completed Start Date]+15) This just gives #Name in the field of my query. (my linked table is named [SurveyPatrolT] and the field that I am trying to use for the date info to add the month and days to is [Last Completed Start Date]).
I don't know VBA or SQL yet but I'm hoping there is a function I can use in the interim. Any ideas or help would be greatly appreciated. I know VBA is probably the best method but I don't know how to add that into my database as yet. I am only in the beginner video lessons at the moment.
I am building a database in order to track gas surveys. What I need help with is the best method for importing info, and calculating a due date from that info.
I have a survey table [SurveyPatrolT] which is a linked table (not sure if this is the best method to do this) but this table is brought in from my company database (Oracle based and asking them to modify or add a query for me is like asking for an act of God). The problem is, the company database is not user freindly. IE I cannot limit the information to just my districts and my surveys. (I have 7 of the 29 districts and 7 of the 60 survey types) but I can either look at one district and all survey types, or one district and one survey type, or all districts and one survey type and that is it. I cannot looke at just my Division. Therefore I think I need to export the information to an excel file and then link it to my database. I am assuming this is the best method because there are due dates in this information that are updated daily when a survey is completed.
The next struggle I have is that the company system calculates a due date but it is based on each month having 30 days. Therefore I need to calculate a correct due date. However, I have different survey types and each type has a different due date schedule. I understand the DateAdd function which works for my 5 year surveys but I have surveys that have a due date that is based on 3.5 months and 7.5 months. This does not work with the DateAdd function as it seems to drop the .5 portion and I'm not sure how to add a day interval along with the month interval. I have also tried the DateSerial function but I am apparently not doing something correct as I am creating an unbound field with ActualDueDate: DateSerial([SurveyPatrolT]![Last Completed Start Date],[SurveyPatrolT]![Last Completed Start Date]+7,[SurveyPatrolT]![Last Completed Start Date]+15) This just gives #Name in the field of my query. (my linked table is named [SurveyPatrolT] and the field that I am trying to use for the date info to add the month and days to is [Last Completed Start Date]).
I don't know VBA or SQL yet but I'm hoping there is a function I can use in the interim. Any ideas or help would be greatly appreciated. I know VBA is probably the best method but I don't know how to add that into my database as yet. I am only in the beginner video lessons at the moment.
