Date Relationships Problem (hahaha)

[twychopen22]

Just thought the title was pretty funny, I know, u all are thinking this was an access forum not a dating site...

I have a relationship question that I was hoping would be quick and easy if you don't mind. I have 3 different forms (Metric Records, Variable Expenses, Fixed Expenses) that I want to have this happen to. I want a date in the form with the entries in the subform. I had created a table with 2 fields: Date and ID (auto ID for primary key) that I had linked to all 3 tables by date. the only problem is that now they look up the same date so if I wanted to go back to the 26th of july to make some change, it won't let me because it says there is a related record in another table. I could just use the date from the same form but my boss wants to see all the records for that day on the same page, hence the form/subform technique. Do you know how I would go about doing this?

 

[The_Doc_Man]

You need two things.

1. A parent/child table relationship in which the primary key of the parent table is a foreign key of the child table (or a component PART of the child's PK).

2. A form/subform in which the appropriate field names are provided in the parent/child links. If you create the child first and then create the parent, when you add the sub-form control with wizards enabled, it will make the link automatically for you.

If you try to link by date for the parent, you problem will be that EVERY item entered on that date becomes a child of that parent. This is probably also why you are having the problem you described.

 

[twychopen22]

My Date Table (I think my Parent table b/c it is the 1 in the one-many relationship) contains an AutoID which is the Primary Key. It is seperate from the 3 forms which I am assuming I have as the child forms. (being the many side of the relationship)

I used the wizard, added the two fields from the date table to all 3 different forms creating 3 forms with 3 subforms. The date and auto id were in the Form, and I could enter new records in the subform. It worked except when I wanted to go back to a certain date. I may not have understood your instructions. (sorry if that is the case, but could you expound or even dumb it down if I am wrong with this reply) Thanks!

The problem only occurs when I click on the Date box in the main form. Even if I don't edit it or change it. I automatically have it default to todays date so it shouldn't be a problem. All expenses and stuff are all put in on the current day anyways and if the day is missed, the user can add a new record and be fine.

 

[twychopen22]

1. I'm not sure what the purpose of the date table is.

I don't know how to relate all three tables otherwise and the Date was a form and had a subform. This way I could see 1 date, and enter many expenses, metrics, etc and see it all on one form/subform. Otherwise I would have to create a new expense every time one occurred even if it was on the same day. I don't know how to fix this problem. Help would be great if there is a better way.

2. When you create relationships between tables, the relationships are from the primary key of one table (the autonumber field of the date table) to a foreign key field in the child table. So, if you are creating the relationship by using the date fields, you are not creating the relationship correctly.

I c what you are saying. What do you say then should be my foreign key of the other table? Oh, and you are correct. I have autoid in my date table but it is not even the primary key. the date is. :-( I should go from the auto ID to an id (as a number but not auto number) in my child tables (expenses, metrics, etc)? Thanks Pat

 

[twychopen22]

Also, I was thinking, I could just have a form (Date) and subform (expenses, metrics etc.) but they would not have to be linked to do that. I can set Date to unique no duplicates and do the same thing I wanted to earlier. Still see it all on one form and all. The reason I had created the relationship was to get the date as the form and expenses etc as the subform. Will this accomplish the same thing? I won't even need an id for the tables now.

I found out this will not work. I deleted the date table and the form will not work anymore. I just want the date in the form, and a listing of expenses etc in the subform. I don't know what I am doing wrong?