Relationships: automatically take values based on field selection

[zfind]

Hi everyone, this is my first post as I've just started to work with Access after a LONG break. I remember the very basics but I'm stuck on what I think should be a simple problem.

In the attached image you can see my table structure. Basically, I want to have one main table (Data) where our sales guys can select their business partners and the country where that partner is found and based on that country, the region and subregion will automatically be filled in. I thought it would work just by using relationships but when I select a country region and subregion remain empty.

Any thoughts?

Thanks!

 

[neileg]

Too complex. I presume a subregion belongs to only one region and a region belongs to only one country. In that case, you only need to store the subregion ID in the Data table because you can deduce the region and country from that. When you build a form to show this, you base it on a query that joins all the tables together and then it will automatically show region and country without having to store it twice.

 

[zfind]

Yes, each country will have one region only and one subregion (if necessary). But we have every country on Earth in the tool so the region can belong to many countries - does that make sense?

I think then I would need to store the country in the Data table because from that I would deduce the region and subregion I think?

 

[neileg]

Yes, each country will have one region only and one subregion (if necessary). But we have every country on Earth in the tool so the region can belong to many countries - does that make sense?

Yes and no.

I think then I would need to store the country in the Data table because from that I would deduce the region and subregion I think?

No.

The problem comes from the naming of regions. Let's say the North East region of England and the North East region of France. Although they have the same name, on their own, they do not uniquely identify where they are. And the name itself tells you almost nothing. In addition, there may be no consistency in the number of regions in a country. So in France, you may have a North East and a North West, and in England you may simply have North.

So, unless you have rules that eliminate these differences, I would argue that despite the name being the same, as a piece of data they are not the same. Therefore, I see it perfectly reasonable to have the regions repeated for each country.

Does that make sense?

 

[zfind]

Yes that is clear, thanks. I ended up amalgamating the Region - Subregion - Country into one table so that's working well now. I've got to the point where I've got a nice form working based on a query, but one small thing is yet to be worked out:

I have a checkbox where users select the week of the year, let's say 29. This is found from the 'weeks' table and it has a corresponding period, for this one it's 8. I've managed to make the form automatically fill the period depending on the week chosen, so if I choose 29, then the period text box will show 8. The problem is, when I save this record, the period field in the 'data' table (where the rest of the record's data is saved) stays blank but the week saves perfectly. I know it's something simple but I can't figure it out.

 

[neileg]

I ended up amalgamating the Region - Subregion - Country into one table

There's an arugment that Region - Country should be in one table and Subregion another, but that's up to you.

If the period is determined by the week, you don't need to store the period?

 

[zfind]

I thought about doing it that way but putting them all in the one table worked simply and it doesn't seem to cause any confusion as they are all very specific business subregions.

As for the weeks/period thing, we do need to record the period for reporting purposes. The 'weeks' table has the weeks as the primary key and then a corresponding period. The problem is, that the table 'data' where we're collecting our revenue isn't saving the period. It saves the week fine as I have made that a combobox with the source as the 'weeks' table (using the internal row source query method, rather making a seperate query) but the main query just pulls the period (from the weeks table) as part of the deal. So the problem is that it's getting from the 'weeks' table to the form but not from the form to the 'data' table I am really new with queries so I could have done something wrong. In any case, if I could just get the period to save once a week has been chosen that's all I need. Can a simple expression do this or a macro?

 

[neileg]

But you don't need to store the period since you can join that table into your query and pull the period field from that.

 

[zfind]

Well it's joined in at the moment, well at least I think so. At the moment it automatically pops up when the week is chosen, that's how I want it, so it's automatic. The reason I need it to be in the same table as the data is for the reporting, so i can see totals by week and period.

Have I missed something fundamental here or am I just confused

 

[zfind]

Worked it out My understanding of Access just doubled. I just created a query that would do the same as the form is doing - taking the week as an 'input', from the data record that the user has chosen, and joining it to the period number in the 'weeks' table. As you said, there was no need at all to store it. Great!

Now maybe I can do some other things I've been wanting to incorporate.

Thanks for the help, I'm sure it won't be the last I need

 

[neileg]

Excellent.

 

[Pat Hartman]

Is your Week table still going to work come January?

 

[zfind]

It will work until week 26 of 2009, at which point I'll just append the 09 to the week key.

 

[Pat Hartman]

Why not start with year and week as the key?

 

[zfind]

I thought about it more and made that change just before you posted yesterday