Help With The Code

[rahmad]

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]

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]

 

[rahmad]

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]

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]

 

[rahmad]

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.

 

[rahmad]

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;