Question How can I work this out?

[frankietp]

Hey guys, I'm new here as you may have noticed

I am having a hard time with something here that I will describe for you to see if I can get some help.

I am creating a small program on access 2007 that will let me keep a record of incoming money. Of course, all incoming money records are going to be stored in the "IncomingMoney" table. But here is what is giving me a hard time to work out... If the incoming money is because of a bonus, then I want to store that record in a "Bonuses" table too, not only on the "IncomingMoney" table.

How can I do that? If I am not mistaken, I need to build a relationship between those tables. But what fields should I build the relationship on?

These are the fields I have for each table mentioned above:

Table: IncomingMoney
- IncomingMoneyID [AutoNumber]
- IncomingMoneyName [Text]
- Quantity [Number]
- Description [text]
- Date [Date]

Table: Bonuses
- BonusID [AutoNumber]
- Date [Date]
- Quantity [Number]
- Description [Text]

I was thinking like "If the IncomingMoneyName.text = "Bonus", then store the bonus record. But to me it is easier to say than do.

Please help... Thanks in advance!

 

[DCrake]

You do not need an extra table you need an extra field in your existing table that is a Boolean type

fldBonus Yes/No
Default Value 0

By default all incoming money will not be a bonus. However when you want to indicate that the money is a bonus payment then set the boolean flag to Yes/True

You can then use queris to distinguish between the bonus and non bonus payments.

David

 

[frankietp]

Well, the truth is that I have more tables that I would like to do the same with. Not only do I want to store the bonus records, I want to store into more tables as I explained with the Bonuses table.

I should have pointed that out, sorry. Also, the other tables have other fields that the "IncomingMoney" table doesn't have. Therefore, I really need to have different tables and do what I explained in the first post.

I didn't talk about the other tables in the first post because I thought that when I'd get help I would just do the same with the rest of the tables

Thanks for your response.

 

[DCrake]

If you have monies coming into your application that appear in different tables what relationship is there between the records that state that they share something in common?

You may find that different payments have different fields but come the end of the day when you want to pull reports of by type of payment you are going to struggle. If you consider the cost of extra fields against extra tables in terms of size and weight, in my opinion I woudl sooner have a few redundant fields in one table than have a non normalised database that would be hard to manage.

David

 

[frankietp]

I'm thinking about that David.

What if I just do this?

Have a form to enter the IncomingMoney details, but when I choose the IncomingMoneyName, if it is a bonus (for example) i could add a button to fill out the bonus record in a separate form.

But that would require me to pass the bonus details (like quantity) to the IncomingMoney form so the user doesn't have to input that again or to avoid the user from entering different data.


What do you think?

Btw, I don't know how to pass data from one form to another...

 

[frankietp]

Nevermind... I came to the conclusion it is better to use the description field in the "IncomingMoney" table to enter the info I would enter in the extra fields on the extra tables. I hope you guys get what I mean lol...

 

[boblarson]

Nevermind... I came to the conclusion it is better to use the description field in the "IncomingMoney" table to enter the info I would enter in the extra fields on the extra tables. I hope you guys get what I mean lol...

We get what you mean but it is the wrong way to do it and will come back and bite you in the end. But it is your backside that will be feeling the pain, not ours. <lol>

 

[frankietp]

lol... what do you suggest I could do?

 

[Boro]

I think that it is better for all who are not IT professionals to become one or to let a job to professionals.

 

[boblarson]

lol... what do you suggest I could do?

Follow the path that DCrake gave.