Overlapping Dates causing duplicates in query

[AishlinnAnne]

Hello, I am creating a personal budgeting database as practice to get used to Access.


The part I am struggling with is trying to get my tables to relate to another the way I want them and then make a form based off of it.
I am hoping my form will be able to select a pay period, which will show how much income comes in, how much $ needs to go out and what i will have left over.

1.) Right now I have my tables set up as follows:

tbl1Months: ID, Month, Year
tbl1PayPeriods: ID, StartDate, EndDate, Month_ID
tbl2Income: ID, PayDate, PayAmt, PayPeriod_ID (bi-weekly)
tbl2Expenses: ID, Company_ID, DueDate, AmtDue, PayPeriod_ID (monthly, each amount is listed as a negative number, representing that it is money going out)
tbl3Companies: ID, CompanyName

2.) I created a query to show:

qryIncomeByPayPeriod: [tbl1Months.Month],[PayPeriod:StartDate & "-" & EndDate], [tbl2Income.PayDate], [tbl2Income.PayAmt]

3.) I then tried to create another query to get the Expenses to link with qryIncomeByPayPeriod

The problem being, I have a paydate of 11/25 @ 786$, and i have 3 bills that are due on 11/25, so when I link the queries, it is adding 786$ for all three 11/25 dates that appear due to expenses.

I am having a hard time explaining this, so any other questions you have that will help me get to a solution, just ask and I will tell.

Thank you for any help anyone can provide!

[ranman256]

Set your query to UNIQUE VALUES = TRUE.

[ssanfu]

My observations:


1) Field names....
"ID" as the PK field for every table is a poor naming convention.
"Month" & "Year" are reserved words in Access and shouldn't be used as object names. Plus both are built-in functions.

I would have:
tbl1Months: MonthID_PK, TheMonth, TheYear
tbl1PayPeriods: PayPeriod_PK, MonthID_FK, StartDate, EndDate
tbl2Income: IncomeID_PK, PayPeriodID_FK, PayDate, PayAmt
tbl2Expenses: ExpensesID_PK, CompanyID_FK, PayPeriodID_FK, DueDate, AmtDue
tbl3Companies: CompanyID_PK, CompanyName

I use a suffix of "_PK" for the primary key fields and "_FK" for the foreign key fields.
First field is the PK field, then the FK fields, then the other fields.....


2) & 3) Without the SQL it is hard to diagnose the problem(s).


Maybe post the dB for analysis??

[Micron]

The problem being, I have a paydate of 11/25 @ 786$, and i have 3 bills that are due on 11/25, so when I link the queries, it is adding 786$ for all three 11/25 dates that appear due to expenses.

Depending on the rest of the data being returned, UNIQUE VALUES may be quite correct, but if not, also check out UNIQUE ROWS.

Just to clarify, if you are saying you get multiple expense records (different ones) this is how it's supposed to work. You have joined the one side of a relationship (the pay period) to the many side of another (multiple expenses in that period) hence you get the pay period data repeated for each expense. Getting the data is step one; step two would be to split the data in a form/subform with the pay period data in the main form and the detail records (expenses) in the sub form. I suppose you could use a split form, but I never cared for them much.

Not sure I see the value in having a month-year table as opposed to getting the DatePart of a pay period. One thing that may cause an issue later on is when you run into pays that start near the end of one month and end in the beginning of the next. When linking these dates to a month-year table, I'd expect problems.