If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Help With The Code
May be this is a bad structure but thank's
cause wanna help me so far.This is my table. a) GRS Record Table. (1).GRS No (2)Issue Date (3)Section Code (4)Section Name (5)Vendor Code (6)Vendor Name (7)Part No 1 (8)Part Name1 (9)DO No1 (10).Qty Received 1 (11)Qty Accepted 1 (12)Qty Rejected 1 (13)Part No 2 (14)Part Name 2.....like this untill ( )Part Name 3 and Qty Rejected 3 the there more two fields ( )Check By and ( ) Approved By b)Vendor List Table. (1)Vendor Code (2)Vendor Name c)Section List Table (1)Section Code (2)Section Name d)Part List 1 (1)Part No 1 (2)Model 1 e)Part List 2 (1)Part No 2 (2)Model 2 e)Part List 3 (1)Part No 3 (2)Model 3 "John W. Vinson" wrote in message ... On Sat, 14 Apr 2007 11:13:00 +0800, "rahmad" wrote: The first table would be a modified GRS Record table, without any PARTS fields: do i have to delete fields Part No and part name from grs record.? I'd create a *new* GRS Record table (without the PartNo and PartName fields). I still do not know (other than what was in the query you posted) ANYTHING about the structure of your tables. I'm making *suggestions* based on my *guesses* at that structure. If my guesses are wrong the suggestions will be wrong. Perhaps you could post the table and fieldnames of the tables that you now have. John W. Vinson [MVP] |
#22
|
|||
|
|||
Help With The Code
On Sat, 14 Apr 2007 14:00:25 +0800, "rahmad" wrote:
May be this is a bad structure but thank's cause wanna help me so far.This is my table. a) GRS Record Table. (1).GRS No (2)Issue Date (3)Section Code (4)Section Name (5)Vendor Code (6)Vendor Name (7)Part No 1 (8)Part Name1 (9)DO No1 (10).Qty Received 1 (11)Qty Accepted 1 (12)Qty Rejected 1 (13)Part No 2 (14)Part Name 2.....like this untill ( )Part Name 3 and Qty Rejected 3 the there more two fields ( )Check By and ( ) Approved By b)Vendor List Table. (1)Vendor Code (2)Vendor Name c)Section List Table (1)Section Code (2)Section Name d)Part List 1 (1)Part No 1 (2)Model 1 e)Part List 2 (1)Part No 2 (2)Model 2 e)Part List 3 (1)Part No 3 (2)Model 3 Now I'm COMPLETELY confused. What is the difference between the three Part List tables? For that matter, what is a "part" in your business? I gather that you join Part List 1 to the Part No 1 field, and Part List 2 to the Part No 2 - but does that mean that you can only use certain parts as a Part No 1, and a different set of parts in Part No 3? Or yuck! do you have all of your parts stored redundantly, three times, in the three Part List tables!? John W. Vinson [MVP] |
#23
|
|||
|
|||
Help With The Code
You're right John,
Last time I joined Part List 1 to Part No 1.Contents of the three of part list are same and always same. "John W. Vinson" wrote in message ... On Sat, 14 Apr 2007 14:00:25 +0800, "rahmad" wrote: May be this is a bad structure but thank's cause wanna help me so far.This is my table. a) GRS Record Table. (1).GRS No (2)Issue Date (3)Section Code (4)Section Name (5)Vendor Code (6)Vendor Name (7)Part No 1 (8)Part Name1 (9)DO No1 (10).Qty Received 1 (11)Qty Accepted 1 (12)Qty Rejected 1 (13)Part No 2 (14)Part Name 2.....like this untill ( )Part Name 3 and Qty Rejected 3 the there more two fields ( )Check By and ( ) Approved By b)Vendor List Table. (1)Vendor Code (2)Vendor Name c)Section List Table (1)Section Code (2)Section Name d)Part List 1 (1)Part No 1 (2)Model 1 e)Part List 2 (1)Part No 2 (2)Model 2 e)Part List 3 (1)Part No 3 (2)Model 3 Now I'm COMPLETELY confused. What is the difference between the three Part List tables? For that matter, what is a "part" in your business? I gather that you join Part List 1 to the Part No 1 field, and Part List 2 to the Part No 2 - but does that mean that you can only use certain parts as a Part No 1, and a different set of parts in Part No 3? Or yuck! do you have all of your parts stored redundantly, three times, in the three Part List tables!? John W. Vinson [MVP] |
#24
|
|||
|
|||
Help With The Code
On Sun, 15 Apr 2007 08:40:15 +0800, "rahmad" wrote:
You're right John, Last time I joined Part List 1 to Part No 1.Contents of the three of part list are same and always same. Then you need one, and only one, Parts table. Storing the same data redundantly in three tables is a HORRID waste of space and really risks data anomalies (e.g. Part #142 is a left handed monkey wrench in Parts 1, and Part #142 is an optical defrandibulator in Parts 2). And of course, as suggested above, you should NOT have Parts 1, Parts 2 and Parts 3 fields in your main table, but rather a GRSParts table to handle the many to many relationship. What *specific* problems are you having with restructuring your tables? What have you tried, and what errors are you getting? It would help if you would post your SQL and the actual text of the error message. John W. Vinson [MVP] |
#25
|
|||
|
|||
Help With The Code
OK John,
I had restructured my table like this. GRS Reocrd table 1.GRS No ( PK ) 2.Issue Date 3.Section Code 4.Section Name 5.Vendor Code 6.Vendor Name 7.Remark 8.Check By 9.Approved By GRS Parts table 1.GRS ID ( PK ) 2.GRS No 3.Part No 4.DO No 5.Qty received 6.Qty Accepted 7.Qty Rejected 8.Reject Content. Part List Table 1.Part No ( PK ) 2.Part Name Section List Table 1.Section Code ( PK ) 2.Section Name Vendor List Table. 1.Vendor Code ( PK ) 2.Vendor Name Then lay outting my relationship Part No from Part List Table to Part No of GRSParts GRS No from GRS Record to GRS No of GRSParts Then Create a query base to the table of GRS Record. Then in the design view re-join the relationship plus join vendor code from vendor list to vendor code of GRS Record and section code from section list to section code of GRS Record. Then try to run the query and it say " Type mismatch in expression " Here is the sql SELECT [GRS Record].[GRS No], [GRS Record].[Issue Date], [GRS Record].[Section Code], [Section List Query].[Section Name], [GRS Record].[Vendor Code], [Vendor List Query].[Vendor Name], [GRS Record].Remark, [GRS Record].[Check By], [GRS Record].[Approved By] FROM ([Section List Query] INNER JOIN ([Vendor List Query] INNER JOIN ([GRS Record] INNER JOIN GRSParts ON [GRS Record].[GRS No] = GRSParts.GRSNo) ON [Vendor List Query].[Vendor Code] = [GRS Record].[Vendor Code]) ON [Section List Query].[Section Code] = [GRS Record].[Section Code]) INNER JOIN [Part List 1 Query] ON GRSParts.PartNo = [Part List 1 Query].[Part Number 1]; And untill here I'm confuse. "John W. Vinson" wrote in message ... On Sun, 15 Apr 2007 08:40:15 +0800, "rahmad" wrote: You're right John, Last time I joined Part List 1 to Part No 1.Contents of the three of part list are same and always same. Then you need one, and only one, Parts table. Storing the same data redundantly in three tables is a HORRID waste of space and really risks data anomalies (e.g. Part #142 is a left handed monkey wrench in Parts 1, and Part #142 is an optical defrandibulator in Parts 2). And of course, as suggested above, you should NOT have Parts 1, Parts 2 and Parts 3 fields in your main table, but rather a GRSParts table to handle the many to many relationship. What *specific* problems are you having with restructuring your tables? What have you tried, and what errors are you getting? It would help if you would post your SQL and the actual text of the error message. John W. Vinson [MVP] |
#26
|
|||
|
|||
Help With The Code
John,
The union query ask me the parameter value for Part No1,Part Number 1,Part Number 2 and Part Number 3. Here is the SQL please help. SELECT [GRS No], [Part No 1], [Qty Received 1], [Qty Accepted 1], [Qty Rejected 1], [Reject Content 1] FROM [GRS Record] WHERE [Part Number 1] IS NOT NULL AND [Part Number 1] 0 UNION ALL SELECT [GRS No], [Part No 2], [Qty Received 2], [Qty Accepted 2], [Qty Rejected 2], [Reject Content 2] FROM [GRS Record] WHERE [Part Number 2] IS NOT NULL AND [Part Number 2] 0 UNION ALL SELECT [GRS No], [Part No 3], [Qty Received 3], [Qty Accepted 3], [Qty Rejected 3], [Reject Content 3] FROM [GRS Record] WHERE [Part Number 3] IS NOT NULL AND [Part Number 3] 0; "John W. Vinson" wrote in message ... On Sun, 15 Apr 2007 08:40:15 +0800, "rahmad" wrote: You're right John, Last time I joined Part List 1 to Part No 1.Contents of the three of part list are same and always same. Then you need one, and only one, Parts table. Storing the same data redundantly in three tables is a HORRID waste of space and really risks data anomalies (e.g. Part #142 is a left handed monkey wrench in Parts 1, and Part #142 is an optical defrandibulator in Parts 2). And of course, as suggested above, you should NOT have Parts 1, Parts 2 and Parts 3 fields in your main table, but rather a GRSParts table to handle the many to many relationship. What *specific* problems are you having with restructuring your tables? What have you tried, and what errors are you getting? It would help if you would post your SQL and the actual text of the error message. John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|