CODEWITHSUNDEEP
ms-accessms-access-2013
Joseph Richard
Joseph Richard

Reputation: 15

Filtering using multiple date fields

Maybe this isnt possible. Brand new to access trying to make a dB for my own purposes to track my work and pay and tips. Pay and tips get paid to me separately depending on when the company that pays me receives them. I have a tblDeliveries and in that table are the fields [wage] [wagedate] [tip] [tipdate] my form is a split form and I’d like to be able to have some sort of filter or something that can total up both the wages and tips which will be paid out according to the date range I select. Since each record has both a tip and wage date, but those dates may not both be in the range I select, for instance I may have received a tip on a job I did last week and already got paid the wage portion from, a simple table totals doesn’t work, so I am unsure how to proceed. Should the tips and wages be in different tables to make this work? Any assistance would be greatly appreciated.

Upvotes: 0

Views: 45

Answers (1)

Applecore
Applecore

Reputation: 4099

I would suggest that you split this into several tables:

  • tblDelivery: - DeliveryID (Autonumber), DeliveryDate (Date)
  • tblPaymentType: - PaymentTypeID (Autonumber), PaymentType (Short Text) (values - Wage, Tip)
  • tblPayment: - PaymentID (Autonumber), PaymentTypeID (long integer, foreign key from tblPaymentType), DeliveryID (long integer, foreign key from tblDelivery), PaymentDate (Date), PaymentAmount (currency)

You would then create a form based on tblDelivery, and add a continuous subform based on tblPayment, joined one-to-many on DeliveryID. In this subform, you would have a combo box based on tblPaymentType that is bound to PaymentTypeID.

You would then be able to create queries based on these three tables that should show you what you are after, either by using tblDelivery!DeliveryDate as a criteria, or tblPayment!PaymentDate.

Regards,

Upvotes: 1

Related Questions