[Solved] Using same column multiple times on a form

[amitmahajan74]

I am making a small database to maintain the orders daily production, daily despatch to maintain the balance orders and history of an article when it was manufactured. An article has 3 attributes to it - Product Name, Colour, Size. I have made tables and forms to enter the product, colour & size in individual tables and corrosponding forms for these. Now working on the order form, the order comes in the following manner - One product, One colour & 6 Sizes with prodcution qauntities. I have created a form in which I can call upon the Product and Colour and Size using a drop down box (passing the ID's from the order form to fetch the relavent names from their respective tables) and entering the quantity for the particular size. Now since the order comes for the same product and colour in 6 sizes, I have to enter the same form with all the same fields except the size 6 times to enter 1 order. Can anyone suggest a form layout where I can call upon the product & colour, then put 6 boxes for the size and enter the size and their respective quantity in each box and this would in turn update the table with 6 rows.

[eremmel]

Hi, welcome to this forum. First please remove the 'flame' icon from your post. It has a special meaning, you missed it during reading of the forum rules.

Now your question. I would say, look to your solution and form with a slightly more complex database layout. Consider using an order with order lines (so two tables). When you make an order entry form map the order to the main form and the order line to the sub-form. I suggest that you go through the 'Base Tutorials'. You find the examples with the same layout.

[amitmahajan74]

Hi eremmel,
Thanks for the quick reply. Have removed the 'flame' icon.
Will try working with 2 tables for order and see if I can get the result.

[DACM]

...since the order comes for the same product and colour in 6 sizes, I have to enter the same form with all the same fields except the size 6 times to enter 1 order. Can anyone suggest a form layout where I can call upon the product & colour, then put 6 boxes for the size and enter the size and their respective quantity in each box and this would in turn update the table with 6 rows.

This could be done using a FILTER table to save the PRODUCT, COLOR and DATE while using a SubForm to create the order. The SubForm links (PRODUCT_ID, COLOR_ID and DATE) would be copied automatically to the ARTICLES (orders) table as you enter each record by entering only SIZE & QUANTITY.

Here's a demo:

[amitmahajan74]

This could be done using a FILTER table to save the PRODUCT, COLOR and DATE while using a SubForm to create the order. The SubForm links (PRODUCT_ID, COLOR_ID and DATE) would be copied automatically to the ARTICLES (orders) table as you enter each record by entering only SIZE & QUANTITY.

Thanks for the order entry, i have been able to do that with creation of 2 tables, order and orderline, the form with subform, order table has the order id and order no, and order line has order no, product, colour, size and quantity. this is working fine, as required.

Now to enter daily production, how to create view or form for this, i need the order no (i created the list box to show the orders), but in the next line i only want those products to show which are present in the selected order, and so on for colour and size (these to be read-only) so that only id's are passed to the production table and then enter the production quantity, also before entering today's production qty, it should show the total order against that combo & total production done till date against that combo, (combo consists of order no, product, colour & size)

[amitmahajan74]

I have enclosed the DPR.odb, I've tried making the frmProduction but not able to achieve the results. Can anyone suggest how to make the querries to achieve the result

[amitmahajan74]

I have done some homework and reached some conclusion on the Daily Production. Filled in some data in the Production and ProdLine tables manually and then designed the Form. But still the form in not perfect.
1 - In the balance order querry, I am trying to use InnerJoin to display the complete order details, and for items not produced, it shows zero production
2 - In the same table, for items having multiple orders and production against multiple orders, it showing balance qty correctly, but if there are multiple order but production for one order only, then it is showing sum of other orders in balance qty
3 - In the Grid to enter the production, it should filter the order no, product, colour & size based on the order no. selected in the Top (i.e. Production) and a check to show that the production is not more than the order qty

I am attaching the db with some sample enteries, please advise changes to be done to achieve the above.

[amitmahajan74]

After doing some working on OUTER JOIN, I have created a query

SELECT "Order"."Order No", "Order"."Order ID", "Product"."Product Name", "Colour"."Colour", "Size"."Size", "OrdersLine"."Quantity" AS "Order Qty", SUM( "ProdLine"."Prod Today" ) AS "Total Prod", SUM( "OrdersLine"."Quantity" - "ProdLine"."Prod Today" ) AS "Balance Prod"
FROM
{ OJ "OrdersLine" LEFT OUTER JOIN "Production" ON "OrdersLine"."Order No ID" = "Production"."Order ID" },
"ProdLine", "Order", "Product", "Colour", "Size"
WHERE
"OrdersLine"."Order No ID" = "ProdLine"."OrderID" AND
"OrdersLine"."Product ID" = "ProdLine"."Product ID" AND
"OrdersLine"."Colour ID" = "ProdLine"."Colour ID" AND
"OrdersLine"."Size ID" = "ProdLine"."Size ID" AND
"OrdersLine"."Order No ID" = "Order"."Order ID" AND
"ProdLine"."Product ID" = "Product"."ID Number" AND
"OrdersLine"."Product ID" = "Product"."ID Number" AND
"OrdersLine"."Colour ID" = "Colour"."ID Number" AND
"ProdLine"."Colour ID" = "Colour"."ID Number" AND
"ProdLine"."Size ID" = "Size"."ID Number"
AND "OrdersLine"."Size ID" = "Size"."ID Number"
GROUP BY "Order"."Order No", "Order"."Order ID", "Product"."Product Name", "Colour"."Colour", "Size"."Size", "OrdersLine"."Quantity"

With the result is coming, but in the column "Order Qty" it is showing only records where "Production" has been entered for that particular "Order", "Product", "Colour" & "Size" whereas in "Order Qty" it should show all records even "Production" has not been entered. Please suggest the change in query. This query is saved as DailyProd.

[RPG]

Hello

I cannot help you with your problem.

When you want make such a complex query then start first with two tables and when you have the result of this two tables then add an other table until you have all the tables.

Try also to work with more joins. I have the idea that working with join cost less time then working with a lot of testing in combination with AND.

When you want work with joining tables then start first with a simple tables and learn how the three different kinds this command is working. Simple tables are for me those tables so I can see real quick the diference in the result when I change a little in a command.

I think this is part of the learning process when you want work with databases.

I think also this can be done with the gui.
When you want do this in the GUI learn adding relations in the query.

Romke

[RPG]

Hello

I did work a little longer on your problem. It is difficult for me to understand what you want.

I have the idea to start with the table Orderlines is the best. The reason is you want work with that data. Then you can work with left join. But read always good to the comment in the dialog. When you use left join then the table what starts the selecting must stand left in the dialog.

Second example
I did nos start with an other table.

Adding table Order
Then I have add the table Order. I do use the relationwhat you did defined on an other place. I do modify it a little but this is only important for this query and not for the relation you define for the complete database. As far I understand relation you define for the complete database you see back in the query designer. But when this relations in the query designer give not the good result you can modify them without problems.

Adding table Production
I did delete the relation what is added automatic and added a relation from Ordersline.ProdId to Production.prodID. I do this there I think this is the result you want have.

Adding Table Colour and Size
When I add both table to the query designer one by one then I must modify the relation.


Missing ProdLine
I do have problems with the table prodline. I can not get a good place for it.


Modify it yourself
Possible you want have an other result. What I have made is as an example for you and as far I can understand what you want. I have made the queries only in the query designer and have done it most with the mouse so it can be easy. But it is only easy when you understand SQL. But this SQL is not so difficult read what you see in the dialog special when you change the kind of the join.

Romke