Help With The Code

R

rahmad

Hi All,
I'm creating a table where its records
will be append to another 3 table.
I have done with the query and the form
But when creating the code I always got aproblem
please help why these two codes doesn't work properly


Private Sub Command8_Click()
Me.Visible = False
DoCmd.OpenQuery "Append Part List 1 ", acViewNormal, acEdit
DoCmd.OpenQuery "Append Part List 2 ", acViewNormal, acEdit
DoCmd.OpenQuery "Append Part List 3 ", acViewNormal, acEdit

End Sub



Private Sub Form_Unload(Cancel As Integer)

DoCmd.OpenTable " Part List 4 ", acViewNormal, acEdit
DoCmd.RunCommand , acCmdSelectAllRecords
DoCmd.RunCommand , acCmdDeleteRecord
DoCmd.Close acTable, " Part List 4 ", acSaveYes

End Sub
 
G

Graham R Seach

Rahmad,

I'm assuming the 3 queries in the Click event are action queries. Is this
what you're trying to do?

Private Sub Command8_Click()
Dim db As Database

Set db = CurrentDb

db.Execute "Append Part List 1", dbFailOnError
db.Execute "Append Part List 2", dbFailOnError
db.Execute "Append Part List 3", dbFailOnError

Set db = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
CurrentDb.Execute "DELETE * FROM [Part List 4]", dbFailOnError
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
R

rahmad

Thank's Graham,
But it still doesn't work.Compile error,
it said " user defined type not defined "

and for the second code,it said
" variable not defined"
Any solution?

Graham R Seach said:
Rahmad,

I'm assuming the 3 queries in the Click event are action queries. Is this
what you're trying to do?

Private Sub Command8_Click()
Dim db As Database

Set db = CurrentDb

db.Execute "Append Part List 1", dbFailOnError
db.Execute "Append Part List 2", dbFailOnError
db.Execute "Append Part List 3", dbFailOnError

Set db = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
CurrentDb.Execute "DELETE * FROM [Part List 4]", dbFailOnError
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

rahmad said:
Hi All,
I'm creating a table where its records
will be append to another 3 table.
I have done with the query and the form
But when creating the code I always got aproblem
please help why these two codes doesn't work properly


Private Sub Command8_Click()
Me.Visible = False
DoCmd.OpenQuery "Append Part List 1 ", acViewNormal, acEdit
DoCmd.OpenQuery "Append Part List 2 ", acViewNormal, acEdit
DoCmd.OpenQuery "Append Part List 3 ", acViewNormal, acEdit

End Sub



Private Sub Form_Unload(Cancel As Integer)

DoCmd.OpenTable " Part List 4 ", acViewNormal, acEdit
DoCmd.RunCommand , acCmdSelectAllRecords
DoCmd.RunCommand , acCmdDeleteRecord
DoCmd.Close acTable, " Part List 4 ", acSaveYes

End Sub
Click to expand...
Click to expand...
 
J

John W. Vinson

Thank's Graham,
But it still doesn't work.Compile error,
it said " user defined type not defined "
Click to expand...

PMFJI...

It's a References problem. Graham's code uses the DAO object model, and A2000
(and some other versions) default to a different object model, ADO.

Open the VBA editor. Select Tools... References. Scroll down until you find
one form

Microsoft DAO x.xx Object Library

Pick the highest version and check it.

If you're not intentionally using any ADO code, *uncheck* the checkbox for
Microsoft ActiveX Data Objects.

John W. Vinson [MVP]
 
R

rahmad

I'm sorry for keep asking John,
There is a strange things occured to my form,
it seems my form delete the record itself.
I entered some record but at last my table has
one record only.When I look into my table,
I see there are more than one records in there.
And when I look into my query it has one record only.
Do you know what's wrong with my query or form.
 
J

John W. Vinson

I'm sorry for keep asking John,
There is a strange things occured to my form,
it seems my form delete the record itself.
I entered some record but at last my table has
one record only.When I look into my table,
I see there are more than one records in there.
And when I look into my query it has one record only.
Do you know what's wrong with my query or form.
Click to expand...

Sounds like the query is returning only one record. Please open the Query in
SQL view (use the View menu item) and post the SQL text here.

John W. Vinson [MVP]
 
R

rahmad

Here is the SQL statement

SELECT [GRS Record].[GRS No], [GRS Record].[Issue Date], [GRS
Record].[Section Code], [Section List].[Section Name], [GRS Record].[Vendor
Code], [Vendor List].[Vendor Name], [GRS Record].[Part Number 1], [Part List
1].[Part Name 1], [GRS Record].[DO No 1], [GRS Record].[Qty Received 1],
[GRS Record].[Qty Accepted 1], [GRS Record].[Qty Rejected 1], [GRS
Record].[Reject Content 1], [GRS Record].[Part No 2], [Part List 2].[Part
Name 2], [GRS Record].[DO No 2], [GRS Record].[Qty Received 2], [GRS
Record].[Qty Accepted 2], [GRS Record].[Qty Rejected 2], [GRS
Record].[Reject Content 2], [GRS Record].[Part No 3], [Part List 3].[Part
Name 3], [GRS Record].[DO No 3], [GRS Record].[Qty Received 3], [GRS
Record].[Qty Accepted 3], [GRS Record].[Qty Rejected 3], [GRS
Record].[Reject Content 3], [GRS Record].Remark, [GRS Record].[Check By],
[GRS Record].[Approved By]
FROM [Part List 3] INNER JOIN ([Part List 2] INNER JOIN ([Part List 1] INNER
JOIN (([GRS Record] INNER JOIN [Section List] ON [GRS Record].[Section Code]
= [Section List].[Section Code]) INNER JOIN [Vendor List] ON [GRS
Record].[Vendor Code] = [Vendor List].[Vendor Code]) ON [Part List 1].[Part
Number 1] = [GRS Record].[Part Number 1]) ON [Part List 2].[Part Number 2] =
[GRS Record].[Part No 2]) ON [Part List 3].[Part Number 3] = [GRS
Record].[Part No 3];
 
J

John W. Vinson

Here is the SQL statement

SELECT [GRS Record].[GRS No], [GRS Record].[Issue Date], [GRS
Record].[Section Code], [Section List].[Section Name], [GRS Record].[Vendor
Code], [Vendor List].[Vendor Name], [GRS Record].[Part Number 1], [Part List
1].[Part Name 1], [GRS Record].[DO No 1], [GRS Record].[Qty Received 1],
[GRS Record].[Qty Accepted 1], [GRS Record].[Qty Rejected 1], [GRS
Record].[Reject Content 1], [GRS Record].[Part No 2], [Part List 2].[Part
Name 2], [GRS Record].[DO No 2], [GRS Record].[Qty Received 2], [GRS
Record].[Qty Accepted 2], [GRS Record].[Qty Rejected 2], [GRS
Record].[Reject Content 2], [GRS Record].[Part No 3], [Part List 3].[Part
Name 3], [GRS Record].[DO No 3], [GRS Record].[Qty Received 3], [GRS
Record].[Qty Accepted 3], [GRS Record].[Qty Rejected 3], [GRS
Record].[Reject Content 3], [GRS Record].Remark, [GRS Record].[Check By],
[GRS Record].[Approved By]
FROM [Part List 3] INNER JOIN ([Part List 2] INNER JOIN ([Part List 1] INNER
JOIN (([GRS Record] INNER JOIN [Section List] ON [GRS Record].[Section Code]
= [Section List].[Section Code]) INNER JOIN [Vendor List] ON [GRS
Record].[Vendor Code] = [Vendor List].[Vendor Code]) ON [Part List 1].[Part
Number 1] = [GRS Record].[Part Number 1]) ON [Part List 2].[Part Number 2] =
[GRS Record].[Part No 2]) ON [Part List 3].[Part Number 3] = [GRS
Record].[Part No 3];
Click to expand...

Ow.

Someday you will need a [Part Number 4].

What will you do then? Redesign your tables, rebuild all your queries,
redesign all your forms, redesign all your reports? You're "committing
spreadsheet" - you should NOT have repeated fields in your table.

The reason you're getting only one record is almost certainly that there is
only one record which has matching values in all five tables. The INNER JOIN
insists that there MUST be a matching record in every single table in order to
retrieve any data at all. If a given [GRS Record] has only two [Part Number]
fields filled... you get NOTHING. Only if it has all three, and they all three
match, will you get anything displayed.

Stop, step back, and model one ([GRS record]) to many (parts) *as a one to
many relationship* - with one *RECORD* per part rather than with one *field*
per part.

John W. Vinson [MVP]
 
R

rahmad

No John,
It seems I will not have Part List 4.
I made three cause refer to legal form sheet
in my company.And moreever if I fill one or two
part number I have a solution for the third part no.
I have a record
Part No --- 0
Part Name --- Nil
which mean that is empty.
And that record exist
in all of three part list table


John W. Vinson said:
Here is the SQL statement

SELECT [GRS Record].[GRS No], [GRS Record].[Issue Date], [GRS
Record].[Section Code], [Section List].[Section Name], [GRS Record].[Vendor
Code], [Vendor List].[Vendor Name], [GRS Record].[Part Number 1], [Part List
1].[Part Name 1], [GRS Record].[DO No 1], [GRS Record].[Qty Received 1],
[GRS Record].[Qty Accepted 1], [GRS Record].[Qty Rejected 1], [GRS
Record].[Reject Content 1], [GRS Record].[Part No 2], [Part List 2].[Part
Name 2], [GRS Record].[DO No 2], [GRS Record].[Qty Received 2], [GRS
Record].[Qty Accepted 2], [GRS Record].[Qty Rejected 2], [GRS
Record].[Reject Content 2], [GRS Record].[Part No 3], [Part List 3].[Part
Name 3], [GRS Record].[DO No 3], [GRS Record].[Qty Received 3], [GRS
Record].[Qty Accepted 3], [GRS Record].[Qty Rejected 3], [GRS
Record].[Reject Content 3], [GRS Record].Remark, [GRS Record].[Check By],
[GRS Record].[Approved By]
FROM [Part List 3] INNER JOIN ([Part List 2] INNER JOIN ([Part List 1] INNER
JOIN (([GRS Record] INNER JOIN [Section List] ON [GRS Record].[Section Code]
= [Section List].[Section Code]) INNER JOIN [Vendor List] ON [GRS
Record].[Vendor Code] = [Vendor List].[Vendor Code]) ON [Part List 1].[Part
Number 1] = [GRS Record].[Part Number 1]) ON [Part List 2].[Part Number 2] =
[GRS Record].[Part No 2]) ON [Part List 3].[Part Number 3] = [GRS
Record].[Part No 3];
Click to expand...

Ow.

Someday you will need a [Part Number 4].

What will you do then? Redesign your tables, rebuild all your queries,
redesign all your forms, redesign all your reports? You're "committing
spreadsheet" - you should NOT have repeated fields in your table.

The reason you're getting only one record is almost certainly that there is
only one record which has matching values in all five tables. The INNER JOIN
insists that there MUST be a matching record in every single table in order to
retrieve any data at all. If a given [GRS Record] has only two [Part Number]
fields filled... you get NOTHING. Only if it has all three, and they all three
match, will you get anything displayed.

Stop, step back, and model one ([GRS record]) to many (parts) *as a one to
many relationship* - with one *RECORD* per part rather than with one *field*
per part.

John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

No John,
It seems I will not have Part List 4.
I made three cause refer to legal form sheet
in my company.And moreever if I fill one or two
part number I have a solution for the third part no.
I have a record
Part No --- 0
Part Name --- Nil
which mean that is empty.
And that record exist
in all of three part list table
Click to expand...

This is still - in my opinion - a flawed table design. Can you be *absolutely
certain* that, over the course of the existance of the company, that this
particular paper form will ALWAYS be used, and that there will never, under
any circumstances whatsoever, be any change in the part list?

It's just that basing a table design on a paper form is essentially NEVER a
good idea. Paper forms have one set of constraints and requirements, which are
*different* from the constraints and requirements of a properly normalized
relational database!

That said... if you have the 0/Nil part numbers, are you still seeing only one
record?


John W. Vinson [MVP]
 
R

rahmad

OK John,
I think you're right.But how to solve the problem
that in 1 GRS No there is 3 record in it.Please help to
re-design my dbs.
 
J

John W. Vinson

OK John,
I think you're right.But how to solve the problem
that in 1 GRS No there is 3 record in it.Please help to
re-design my dbs.
Click to expand...

Create a new, normalized table:

SELECT [GRS Record].[GRS No], [GRS Record].[Issue Date], [GRS
Record].[Section Code], [Section List].[Section Name], [GRS Record].[Vendor
Code], [Vendor List].[Vendor Name], [GRS Record].[Part Number 1], [Part List
1].[Part Name 1], [GRS Record].[DO No 1], [GRS Record].[Qty Received 1],
[GRS Record].[Qty Accepted 1], [GRS Record].[Qty Rejected 1], [GRS
Record].[Reject Content 1], [GRS Record].[Part No 2], [Part List 2].[Part
Name 2], [GRS Record].[DO No 2], [GRS Record].[Qty Received 2], [GRS
Record].[Qty Accepted 2], [GRS Record].[Qty Rejected 2], [GRS
Record].[Reject Content 2], [GRS Record].[Part No 3], [Part List 3].[Part
Name 3], [GRS Record].[DO No 3], [GRS Record].[Qty Received 3], [GRS
Record].[Qty Accepted 3], [GRS Record].[Qty Rejected 3], [GRS
Record].[Reject Content 3], [GRS Record].Remark, [GRS Record].[Check By],
[GRS Record].[Approved By]
FROM [Part List 3] INNER JOIN ([Part List 2] INNER JOIN ([Part List 1] INNER
JOIN (([GRS Record] INNER JOIN [Section List] ON [GRS Record].[Section Code]
= [Section List].[Section Code]) INNER JOIN [Vendor List] ON [GRS
Record].[Vendor Code] = [Vendor List].[Vendor Code]) ON [Part List 1].[Part
Number 1] = [GRS Record].[Part Number 1]) ON [Part List 2].[Part Number 2] =
[GRS Record].[Part No 2]) ON [Part List 3].[Part Number 3] = [GRS
Record].[Part No 3];

GRSRecord
GRSNo <Primary Key>
IssueDate <Date/Time>
SectionCode ... <other GRS record fields>

Note that I'm using CamelCase rather than embedding blanks in fieldname; they
can become a hassle.

Parts
PartNo <Primary Key>
<I presume you already have a Parts table, use the same fields>

GRSParts
GRSPartID <Autonumber Primary Key>
GRSNo <link to GRSRecord>
PartNo <link to Parts>
QtyReceived
QtyAccepted
QtyRejected
RejectContent

You can then create a "Normalizing Union Query":

SELECT [GRS No], [Part Number 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 Number 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 Number 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

Save this query as uniAllParts; then base an Append query on it to migrate the
data into the new GRSParts table.

John W. Vinson [MVP]
 
R

rahmad

I'm sorry John,
More detail explanation please.
I can't understand it easyly



John W. Vinson said:
OK John,
I think you're right.But how to solve the problem
that in 1 GRS No there is 3 record in it.Please help to
re-design my dbs.
Click to expand...

Create a new, normalized table:

SELECT [GRS Record].[GRS No], [GRS Record].[Issue Date], [GRS
Record].[Section Code], [Section List].[Section Name], [GRS Record].[Vendor
Code], [Vendor List].[Vendor Name], [GRS Record].[Part Number 1], [Part List
1].[Part Name 1], [GRS Record].[DO No 1], [GRS Record].[Qty Received 1],
[GRS Record].[Qty Accepted 1], [GRS Record].[Qty Rejected 1], [GRS
Record].[Reject Content 1], [GRS Record].[Part No 2], [Part List 2].[Part
Name 2], [GRS Record].[DO No 2], [GRS Record].[Qty Received 2], [GRS
Record].[Qty Accepted 2], [GRS Record].[Qty Rejected 2], [GRS
Record].[Reject Content 2], [GRS Record].[Part No 3], [Part List 3].[Part
Name 3], [GRS Record].[DO No 3], [GRS Record].[Qty Received 3], [GRS
Record].[Qty Accepted 3], [GRS Record].[Qty Rejected 3], [GRS
Record].[Reject Content 3], [GRS Record].Remark, [GRS Record].[Check By],
[GRS Record].[Approved By]
FROM [Part List 3] INNER JOIN ([Part List 2] INNER JOIN ([Part List 1] INNER
JOIN (([GRS Record] INNER JOIN [Section List] ON [GRS Record].[Section Code]
= [Section List].[Section Code]) INNER JOIN [Vendor List] ON [GRS
Record].[Vendor Code] = [Vendor List].[Vendor Code]) ON [Part List 1].[Part
Number 1] = [GRS Record].[Part Number 1]) ON [Part List 2].[Part Number 2] =
[GRS Record].[Part No 2]) ON [Part List 3].[Part Number 3] = [GRS
Record].[Part No 3];

GRSRecord
GRSNo <Primary Key>
IssueDate <Date/Time>
SectionCode ... <other GRS record fields>

Note that I'm using CamelCase rather than embedding blanks in fieldname; they
can become a hassle.

Parts
PartNo <Primary Key>
<I presume you already have a Parts table, use the same fields>

GRSParts
GRSPartID <Autonumber Primary Key>
GRSNo <link to GRSRecord>
PartNo <link to Parts>
QtyReceived
QtyAccepted
QtyRejected
RejectContent

You can then create a "Normalizing Union Query":

SELECT [GRS No], [Part Number 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 Number 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 Number 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

Save this query as uniAllParts; then base an Append query on it to migrate the
data into the new GRSParts table.

John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

I'm sorry John,
More detail explanation please.
I can't understand it easyly
Click to expand...

Sorry, I did leave your query in there (I had copied it into the message to
get fieldnames).

What I am suggesting is:
Create a new, normalized set of tables: I'm displaying them in the format

Tablename
Fieldname <comments about that field>
Fieldname

The first table would be a modified GRS Record table, without any PARTS
fields:

GRSRecord
GRSNo <Primary Key>
IssueDate <Date/Time>
SectionCode ... <other GRS record fields>

You'll also need (and probably already have) a table of Parts:

Parts
PartNo <Primary Key>
<I presume you already have a Parts table, use the same fields>

The new table I'm suggesting is the "many to many" linking table, connecting
each GRS Record to the one, two, three or more Parts used in that GRS Record:

GRSParts
GRSPartID <Autonumber Primary Key>
GRSNo <link to GRSRecord>
PartNo <link to Parts>
QtyReceived
QtyAccepted
QtyRejected
RejectContent

You can then create a "Normalizing Union Query" to migrate data from your
existing table into the new GRSParts table. Copy and paste the following into
the SQL window of a new query. It's a "UNION" query which lets you "stitch
together" several datasets into one big one.

SELECT [GRS No], [Part Number 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 Number 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 Number 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

Save this query as uniAllParts; then base an Append query on it to migrate the
data into the new GRSParts table.

Hope that helps!

John W. Vinson [MVP]
 
R

rahmad

I think I still made a fault John,
Uni all parts ask me a parameter for each
part no fields and qty fields.



John W. Vinson said:
I'm sorry John,
More detail explanation please.
I can't understand it easyly
Click to expand...

Sorry, I did leave your query in there (I had copied it into the message to
get fieldnames).

What I am suggesting is:
Create a new, normalized set of tables: I'm displaying them in the format

Tablename
Fieldname <comments about that field>
Fieldname

The first table would be a modified GRS Record table, without any PARTS
fields:

GRSRecord
GRSNo <Primary Key>
IssueDate <Date/Time>
SectionCode ... <other GRS record fields>

You'll also need (and probably already have) a table of Parts:

Parts
PartNo <Primary Key>
<I presume you already have a Parts table, use the same fields>

The new table I'm suggesting is the "many to many" linking table, connecting
each GRS Record to the one, two, three or more Parts used in that GRS Record:

GRSParts
GRSPartID <Autonumber Primary Key>
GRSNo <link to GRSRecord>
PartNo <link to Parts>
QtyReceived
QtyAccepted
QtyRejected
RejectContent

You can then create a "Normalizing Union Query" to migrate data from your
existing table into the new GRSParts table. Copy and paste the following into
the SQL window of a new query. It's a "UNION" query which lets you "stitch
together" several datasets into one big one.

SELECT [GRS No], [Part Number 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 Number 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 Number 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

Save this query as uniAllParts; then base an Append query on it to migrate the
data into the new GRSParts table.

Hope that helps!

John W. Vinson [MVP]
Click to expand...
 
R

rahmad

And how to create many to many relationship?


John W. Vinson said:
I'm sorry John,
More detail explanation please.
I can't understand it easyly
Click to expand...

Sorry, I did leave your query in there (I had copied it into the message to
get fieldnames).

What I am suggesting is:
Create a new, normalized set of tables: I'm displaying them in the format

Tablename
Fieldname <comments about that field>
Fieldname

The first table would be a modified GRS Record table, without any PARTS
fields:

GRSRecord
GRSNo <Primary Key>
IssueDate <Date/Time>
SectionCode ... <other GRS record fields>

You'll also need (and probably already have) a table of Parts:

Parts
PartNo <Primary Key>
<I presume you already have a Parts table, use the same fields>

The new table I'm suggesting is the "many to many" linking table, connecting
each GRS Record to the one, two, three or more Parts used in that GRS Record:

GRSParts
GRSPartID <Autonumber Primary Key>
GRSNo <link to GRSRecord>
PartNo <link to Parts>
QtyReceived
QtyAccepted
QtyRejected
RejectContent

You can then create a "Normalizing Union Query" to migrate data from your
existing table into the new GRSParts table. Copy and paste the following into
the SQL window of a new query. It's a "UNION" query which lets you "stitch
together" several datasets into one big one.

SELECT [GRS No], [Part Number 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 Number 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 Number 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

Save this query as uniAllParts; then base an Append query on it to migrate the
data into the new GRSParts table.

Hope that helps!

John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

I think I still made a fault John,
Uni all parts ask me a parameter for each
part no fields and qty fields.
Click to expand...

Please post the SQL. The fieldnames in the UNION query must match those in
your existing table, and (since you unwisely, IMO, use blanks in your
fieldnames) must be enclosed in square brackets.

A many to many relationship is created by creating two one to many
relationships - from GRS Record to GRSParts, and from Parts to GRSParts.


John W. Vinson [MVP]
 
R

rahmad

which sql do you mean.if the union sql ,i'm just copy and paste your
previous post.
if the old one I think you had it.


rahmad said:
And how to create many to many relationship?


John W. Vinson said:
I'm sorry John,
More detail explanation please.
I can't understand it easyly
Click to expand...

Sorry, I did leave your query in there (I had copied it into the message to
get fieldnames).

What I am suggesting is:
Create a new, normalized set of tables: I'm displaying them in the format

Tablename
Fieldname <comments about that field>
Fieldname

The first table would be a modified GRS Record table, without any PARTS
fields:

GRSRecord
GRSNo <Primary Key>
IssueDate <Date/Time>
SectionCode ... <other GRS record fields>

You'll also need (and probably already have) a table of Parts:

Parts
PartNo <Primary Key>
<I presume you already have a Parts table, use the same fields>

The new table I'm suggesting is the "many to many" linking table, connecting
each GRS Record to the one, two, three or more Parts used in that GRS Record:

GRSParts
GRSPartID <Autonumber Primary Key>
GRSNo <link to GRSRecord>
PartNo <link to Parts>
QtyReceived
QtyAccepted
QtyRejected
RejectContent

You can then create a "Normalizing Union Query" to migrate data from your
existing table into the new GRSParts table. Copy and paste the following into
the SQL window of a new query. It's a "UNION" query which lets you "stitch
together" several datasets into one big one.

SELECT [GRS No], [Part Number 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 Number 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 Number 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

Save this query as uniAllParts; then base an Append query on it to
Click to expand...
migrate
the
data into the new GRSParts table.

Hope that helps!

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
R

rahmad

The first table would be a modified GRS Record table, without any PARTS

do i have to delete fields Part No and part name from grs record.?




rahmad said:
And how to create many to many relationship?


John W. Vinson said:
I'm sorry John,
More detail explanation please.
I can't understand it easyly
Click to expand...

Sorry, I did leave your query in there (I had copied it into the message to
get fieldnames).

What I am suggesting is:
Create a new, normalized set of tables: I'm displaying them in the format

Tablename
Fieldname <comments about that field>
Fieldname

The first table would be a modified GRS Record table, without any PARTS
fields:

GRSRecord
GRSNo <Primary Key>
IssueDate <Date/Time>
SectionCode ... <other GRS record fields>

You'll also need (and probably already have) a table of Parts:

Parts
PartNo <Primary Key>
<I presume you already have a Parts table, use the same fields>

The new table I'm suggesting is the "many to many" linking table, connecting
each GRS Record to the one, two, three or more Parts used in that GRS Record:

GRSParts
GRSPartID <Autonumber Primary Key>
GRSNo <link to GRSRecord>
PartNo <link to Parts>
QtyReceived
QtyAccepted
QtyRejected
RejectContent

You can then create a "Normalizing Union Query" to migrate data from your
existing table into the new GRSParts table. Copy and paste the following into
the SQL window of a new query. It's a "UNION" query which lets you "stitch
together" several datasets into one big one.

SELECT [GRS No], [Part Number 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 Number 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 Number 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

Save this query as uniAllParts; then base an Append query on it to
Click to expand...
migrate
the
data into the new GRSParts table.

Hope that helps!

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

do i have to delete fields Part No and part name from grs record.?
Click to expand...

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append Query problem 2
query not working on form action 3
Posting a dataset to a SQL table using VBA 0
Showing the results of a parameter form in a form! 2
The database could not lock table 1
Help with some code Please 1
The below code isn't working. Please help. 5
Dlookup in Form want pull data correctly from Table 4

Top