Building

[jlech1805]

Hi,



I am new to access and have been asked to build a database.


We have a list on contractors with all their relevant information that will be easy to keep in a single table and filled in and updated with forms.

The problem i am running into is we want to create queries that will be calculated fields using varies if then statements for each period going forward.

My original idea was to create new "schedules" each time there is an update.

Example

Contractor Info Table
Employee #1
name
vendor
wage
wage type
expenses

Schedule table
Employee #1
name
Jan 2010
Feb 2010
Mar 2010(see below)

name jan feb mar apr may jun jul aug sep oct nov dec
xyz 1 1 1 1 1 1 0 0 0 0 0 0


*1's indicate they are active for that period

The problem is that we want to be able to keep the historical budgeted amounts and each time i get a new forecast (so different months) I'd have to re-write the query. As far as I can tell there is no way to write a macro to create a query each time for the new schedule.

Any suggestions?

My thoughts were to reference the column number so if i am running a report in january or september it would always reference what is needed....



Anyway sorry for the likely confusing set up of the question.

I am sure there is a simple answer that i am missing, but i am new to access.


thanks in advance,


justin

[Access_Blaster]

Hi,

I am new to access and have been asked to build a database.


We have a list on contractors with all their relevant information that will be easy to keep in a single table and filled in and updated with forms.

The problem i am running into is we want to create queries that will be calculated fields using varies if then statements for each period going forward.

My original idea was to create new "schedules" each time there is an update.

Example

Contractor Info Table
Employee #1
name
vendor
wage
wage type
expenses

Schedule table
Employee #1
name
Jan 2010
Feb 2010
Mar 2010(see below)

name jan feb mar apr may jun jul aug sep oct nov dec
xyz 1 1 1 1 1 1 0 0 0 0 0 0


*1's indicate they are active for that period

The problem is that we want to be able to keep the historical budgeted amounts and each time i get a new forecast (so different months) I'd have to re-write the query. As far as I can tell there is no way to write a macro to create a query each time for the new schedule.

Any suggestions?

My thoughts were to reference the column number so if i am running a report in january or september it would always reference what is needed....



Anyway sorry for the likely confusing set up of the question.

I am sure there is a simple answer that i am missing, but i am new to access.


thanks in advance,


justin

Hi Justin,
I would experiment with different table designs. A three table setup might work using a 1:M model.

tblContractor
ContractorID [pk]
Name
Vendor
Wage
WageType
Expenses
MoreFields

tblSchedule
ScheduleID [pk]
Active [y/n]
SchDate [date]
MoreFields

tblEmployees
EmployeeID [pk]
Employee
MoreFields

You then would have to decide how to relate the tables using foreign keys.