I am working on an access 2002 flight schedule database. I am new to access but have some basic understanding of sql and vb6 code.
I have learned a lot from this website. Thanks much
Hopefully you can help me with this one.
This database handles a varying number of flights for a varying number of days for a varying number of months.
I have a number of tables as follows. I tried to normalize to the best of my ability.
tblflightadhoc
PK FlightADID
AirlineCode
FlightNuminout
ArrivalTime
DepartureTime
RouteFrom
RouteTo
Equipment
Program
Remarks
tblflightcharter
PK FlightCHID
AirlineCode
FlightNuminout
ArrivalTime
DepartureTime
RouteFrom
RouteTo
Equipment
Program
Remarks
tblflightservice
PKFlightServiceID
FKFlightADID
FKFlightCHID
FlightService
These are the three main tables. However I have also constructed tables
for each month of the year with 5 weeks eg.....
tblJandays
PKJanID
FKFlightADID
FKFlightCHID
Monthname-January'default'
Program-tells me which flightprogram eg adhoc,charter.
Week1
M
T
W
T
F
S
S
Week 2 the weeks continue to 5 weeks consisting of 7 days.
The dates are very random based on the dates and the year a flight can operate. eg a flight might operate on Monday in Jan 2 times because that particular Monday based on year only consists of 2 Mondays or vice versa based on random dates and days a flight can operate for 5 Mondays in January.
My one of many issues is. The data entry for this becomes very time consuming. That is, If FlightAdhoc operates on Sundays between April 2 to August 19th. My current design only allows data entry by manually entering theses dates to be stored in the monthdays tables or if FlightCharter operates on Sunday/Thursday/Saturday then manual entry is required.
I am currently using a calendar so that it pops up by the textbox and
autopopulates
QUESTION Is there a way in access or maybe by means of an external program that communicates with forms built in access to autopopulate these fields so that the data entry can be automatically stored in table.
txtbox frequencyday=Mon,Thurs,Wed
txbox frequencymonthstart= March 27 2007
txtbox frequencymonthend = October 23 2007
I am aware that the DatePart() function is able to return DAY of week based on value entered.
Can You point me in the right direction for a way in VB to design a function that
checks for day of week
month of year
number of days in month of year eg the number of Saturdays, Mondays in the month.
Then after checking for these values I can use an sql statement to insert into tblJandays based on month' number daysof week in month ,FlightID (I am trying to change PK to this but having problems concatenating string airlinecode/flightnum) automatically.
Finally, I know this post is very long, export the data captured in access to a preformatted excel file.