need help in correcting a design mistake (1 Viewer)

[yifan]

If a query is created based on a table, and then there is a form created based on that query.

now i split the table into 2 tables, as the original table contains info of 2 different products. so wanting to have 1 table for 1 product...

Question: after spliting table, do I need to recreate the query and the form?

Problem: 1. after spliting the table, the form doesn't work properly.
2. the query has too many fields in, as it reached 208 fields.
3.There is loads of code in the form doing calculations, so it would be very costly to recreate it....

Can anyone help with this please?

thanks

 

[dsigner]

This could be tricky as I don't think there is enough detail to answer definitively. Stile this may help.
When you split a table into two you need to establish the relationship between them. If you have done that then your original query will just take the same fields but from two tables so all your calculations should be unaffected. Also because the same fields are there you should be able to use the same form.

 

[yifan]

thanks for your reply, but the 2 tables (creared by splitting) has got no relationship at all as they store info of 2 totally different products.
do I still make one between them?

after splitting, i updated the design of the query to get fields from both the 2 tables, but the form has still got a "record not updatable " error...

thanks for help

 

[gemma-the-husky]

if both your tables contain details of products they shouldnt really be split.

splitting or normalization refers to analysing your data to avoid storing duplicated or unnecessary data, which actually makes it harder to maintain your database.


eg (in a simplified explanation) if you have orders for products you would split this into two tables,

one table would store a order header, who its from, when it was placed etc
the other would store the line by line order details

in this case there would be a link between the 2 tables, of the order number.

What sort of data are you storing to need at least 208 different fields.

 

[neileg]

I agree with gemma.

If you have 208 fields, this suggests a design flaw. You should probably have a linked table with 208 records.

In other postings you have said that a redesign is not possible, but it seems to me that you are going to have more trouble coping with the bad design that if you corrected the fundamental problems.

 

[grnzbra]

Leave the table alone. The way you describe it, it shouldn't be split.
Now, assuming that you are in a situation where word is coming down from somewhere up the chain of command and you are stuck with the table having to be split, you can make the new setup work as it did before rather simply (assuming that the two tables are very similar and have the same info for the different products.)

Assuming that one of the table names and its field names remained the same, the query will work for one of the tables. (If not, create a query for one of the tables to work with the report.) When you have that working, open it in SQL view and copy the SQL for that query.

Then, between the last letter of the SQL statement and the ";" at the end, hit ENTER and then type in "UNION ALL" and hit enter again. Now paste in the SQL you originally copied ahead of the ";". (If you end up with two ";", delete one of them). Now go through this second SELECT statement and make the changes necessary to match the table and field names for the new table.

The output of this query should be a duplicate of the output of the original query.

Better yet, don't split the table.

 

[Pat Hartman]

Union queries are not updatable so they are not generally used as the RecordSource for a form.
yifan, you need to do some research on normalization. It may look like a lot of work to fix your design issue at this point but it only gets worse later on. The more forms/reports/queries you build, the more code you write to get past the design constraints, the more you have to change in the end.