Creating a grid on a form

[plog]

You've got some weird joins still.

TeamMembers has 2 lines coming from it. The only relationship it should have should be to the TeamMakeup field. Also, TeamId shouldn't be a primary key in the TeamMakeup table. Since this table handles a many to many relationship, in my opinion it doesn't need one.

The TeamId field in TeamName connects to the CityBorough field in the City table--that just doesn't sound right. It seems there would be a one to many relationship between city and team which means you would probably need another table like CityTeams which is similar in structure and function to the TeamMakeup table you added.

A lot of links to/from the City table use the CityBorough field which isn't a primary key. Connections to the City table should probably be doing so via the MC City field.

Weeks essentially connects to the City table on 2 fields: Directly via Wave and indirectly via Borough (it goes through Activities to make this connection), that too doesn't seem right. Weeks should probably not be connected to one of those tables.

 

[Megacitizen]

Starting to get the relationships sorted, thanks guys

Trouble is, I'm wanting to test them but when I put my fields into place on the Design View of the form and then switch to Form View, I'm getting a completely blank sheet - no fields, no labels etc in view. Is there any reason for this?

 

[plog]

Most likely your joins aren't matching data. For example, if you had 2 tables of people's data and you tried to join between the first name field of one table to the zip code field of the second table, you'd get no results. I think something similar is happening here.

I'd go slowly, starting with 2 tables, run the query to see if you get any results, then add another table until you get no results. The last table you join would then be the culprit. Investigate the data in the fields on both sides of that last join and make sure they contain similar data.

 

[Megacitizen]

Having tried and tested working on just two tables, (slowly but surely) I managed to get something working This was tested yesterday, just before close of play. Unfortunately today, it seems my system has decided to swap my relationships over. I should be recording a relationship of One Base - Many Teams, but on checking my tables, it's showing the BaseID column populated with TeamID info instead, indicating a One Team - Many Bases relationship . How do I rectify this?

 

[Megacitizen]

(Bump!!)

Everything works well when I have just two tables, but when I start adding a third, no matter what it contains, that's when the problems start, ie blank page in Form View. Also, I sometimes cannot enter details in Form view as I get the legend "Join Key not in RecordSet" appearing in the bottom left corner of the page.

A crude example of what I'm trying to achieve would be as follows:-

At any given time;
One Base sends One Team
One Team visits One Borough
One Borough receives One Team.

The above statements as one are true in many cases for teams we are registering. However, as we are receiving registrations from all over the world, for a number of bases (including my own who are running the entire project) we are requiring the following scenarios:-

At any given time;
One Base can send Many Teams
One Team in One Borough at any one time.
One Borough can receive Many Teams simultaneously.

Where a Base sends more than one team, I have managed to find ways of uniquely identifying each team to avoid duplications, which also covers the situations where two or more bases run schools of the same name (General scenario is that Teams are named after the schools they have been attending - common examples of potential duplication are Classic DTS and School of Evangelism).

I'm struggling to get my relationships to achieve the above. Please help before I headbutt the table hard enough to crack the glass (our office tables are home-made - wooden pallets on leg frames with a glass covering on top).

 

[Megacitizen]

Next question:-

I have managed to get a sheet showing the fields I require, (following Plog's advice and starting with 2 or 3 tables only) and have them populated . However, ALL the info is unique on each page, but we don't necessarily want that. On some pages, certain information can and will be duplicated ie Bases and Regions (Boroughs in London, but called Regions as that will be what we use in future cities), only the Teams will be unique.

A Base can send, and in some cases has already sent, more than one team. These would be placed in different regions and/or at different times of the year. There are 33 Regions in London, we won't be adding any more to that number. We want to be able to record that each region can receive one OR MORE teams at any given time.

How do I get my relationships to achieve all this????

 

[Megacitizen]

Thanks for everyones' help so far (a bit difficult to keep track at the moment as my questions for this project have spread into another thread here).

I have managed to relate my tables and have established the Teams table as the "parent" or "master" table, as all teams will be unique, and the Bases and Regions tables as "child" tables, as they will contain some repeating information in the whole schema.

When I try to open up a form to try and test everything, I get the legend "Type mismatch in Expression" in a warning macro and am unable to access my form. Obviously, I need to key in some expressions somewhere, but being completely new to DB programming, I have zero idea of what expressions to write - fortunately, I've seen where they go so I don't have to worry about that score. I basically need to ensure that repeating information is allowed in 3 of the 5 text boxes I'm starting the form with, namely "Base" & "Nation" sending teams, and "Region" receiving teams.

If I can get all this working, I'll have some sense of achievement, and when I need to add in other details like dates, actions etc, I'll be back to bombard you all.

 

[Megacitizen]

BUMP!!

Managed to get tables normalised and referential integrities established. Combos and Data Entry fields are now working . (Could do with a thumbs up smilie here).

Pat, you presented a table earlier in this thread showing your data in a grid form. I would like to know how you achieved that, as it is close to the type of view I am wanting to achieve. As our form is intended to be in Data Entry format, we would like it to be simple for the end user to key the info in on the grid we create, if that's possible.

 

[Megacitizen]

BUMP !!!!

Sorry for the lengthy wait to reply but I've been away for the last two months or so. I had 3 weeks holiday due and on my return, I spent the next 6 weeks on a TESOL Course, learning how to be an English teacher. But now I'm back, asking annoying questions again . One thing I did discover when I returned to my office was that I've completely forgotten almost everything I learned about DBs so I will take time (quite a bit of it) to get back upto speed.


Would I be right in thinking I need to add my labels to an existing table so I can get them onto the mainform, thus avoiding a cartesian effect? Do I then link my subforms (I'll have 12 in total) to that particular table?

 

[Megacitizen]

Managed to get my various subforms onto the mainform and everything lined up correctly (yes, it was a pain), however, I notice in my form view there is a solid blue line down the left side of each subform. Is there any way of getting rid of this? I've found that overlapping gets rid of the scroll bars on the right of each form, but I probably still require those. Can I get the scroll bars to overlap the left borders rather than the other way round.

Edit: forgot to say Access 2007