Simple issue?

[Patience]

Hi guys,

I have hit a wall here. I have devided 1 table into 2 since the amount of fields exceeds the maximum allowed for 1 table. Furthermore, I use a separate form for each table. The key field in both tables is "Item number".

Here is what I want to do: When I write an item number in the "Item number" field in one of the two forms, I want this item number (the same record) to be shown in the other form, when I open it. This way I can continue writing to the same record without having to write the same item number in the second form. I hope you know what I mean.

Is there an easy way to do this?



Thank you very much in advance

[TheShabz]

The mention that you hit the maximum allowed fields for a table tells me that your table isnt normalized. Before we tackle your main question, give us your field names and lets make sure you dont need more tables.

[Patience]

I am not sure what you are asking for. Do you want the names of all 400 fields???

[TheShabz]

Yes. Reason being, 400 fields in 2 tables concerns me. Even if your situation gets resolved, if your 2 tables aren't normalized (i just cant see how they can be) you will come across many more issues down the line. I'd like to solve the bigger issue first.

[Patience]

But even if I manage to split the fields up in even more tables, I would still have a problem, since the query can not handle 400 fields. So I would still need two forms right?

[TheShabz]

We will probably end up deleting certain fields, combining many fields into one, and creating a workflow to better streamline the process. I just for the life of me cannot figure out why you would need that many fields in 2 tables. Each table should be its own entity. You gave the key as Item Number. This sounds like an inventory table or something of the kind. Each item has 400 fields describing it?

Once seeing your fields, we will probably figure out a way to better use the forms to get your job done.

[Patience]

Yeah, it actually requires 400 fields to describe the item. It is a pretty technical product. Therefore it will not make sense to post the names here since it would not make sense to you I think. No offence just a fact.

I really appreciate that you want to help me out with my table structure, but all I really need is an answer to how I can continue to write to the same record when I open a new form. I hope that you have a solution to this. I will be very happy.

Thanks and BR

[kimmer]

Patience, The Shabtz is right. Not only is the fact that you have that many fields telling, but more concerning is the fact that you're unaware that databases contain multiple tables.

In all likelihood, you have field names that should be data in a field, not fields themselves. For example, it is incorrect to have fields named Red, Green, Blue. Instead one should have a field called Color. Another example: is incorrect to have fields called Think1, Thing2, Thing3. Instead one would have a field called Thing and another field called ThingType.

There are zillions of articles about database design on the web and there are tons of books available and people willing to help.

Here is an article from Microsoft: http://support.microsoft.com/kb/283878

[kimmer]

It occurred to my that might put it into perspective: You are saying that out of 400 fields, this data never has any one-to-many relationships. It is all one-to-one. Does that sound believable?