E
EvilSewingMachine
I need to multiply records according to the [QNTY] field.
HOW!?
HOW!?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Use [Qnty] to multiply number of records, short and sweet.
S
Steve
Create a query based on your table. Put this expression in a blank field:
ExtendedPrice:[QNTY]*[Price]
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
ExtendedPrice:[QNTY]*[Price]
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
E
EvilSewingMachine
Maybe I over-simplified.
This database is a configurator based, manufacturing resource planner.
If you configure a product and then set [Qnty] to 2, I want a query
that creates 2 identical records.
example:
[Qnty=2]*PartA = PartA 1
PartA 2
This database is a configurator based, manufacturing resource planner.
If you configure a product and then set [Qnty] to 2, I want a query
that creates 2 identical records.
example:
[Qnty=2]*PartA = PartA 1
PartA 2
S
Steve
Need more information!
1. Do you have a product table? What are the fields?
2. Do you want to create 2 identical records in some table? What are
the fields?
3. Is Qnty a field in some table? What are the fields?
4. Do you enter QNTY in a textbox on a form?
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
1. Do you have a product table? What are the fields?
2. Do you want to create 2 identical records in some table? What are
the fields?
3. Is Qnty a field in some table? What are the fields?
4. Do you enter QNTY in a textbox on a form?
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
E
EvilSewingMachine
My Product table is tbl_Specs. Data is entered through a form
frm_Cornice. The fields in frm_Cornice are: [Qnty] [Width] [Length]
[Projection]. Each product ordered has an assembly of 4 parts whose
size vary depending on these inputs. I want to make a report of labels
that will identify each part.
frm_Cornice
[Qnty] [Width] [Length] [Projection]
Tbl_Specs
OrderID Qnty Width Length Projection
1 1 54 12 4
2 2 96 14.5 5.5
3 1 78.5 20 2
4 3 24 8 3.5
This would be the desired output:
Rpt_PartList
PartID OrderID Width Length
1 1 52.5 3.5
2 1 12 3.5
3 1 12 3.5
4 1 54 12
5 2 96 5.5
6 2 14.5 5.5
7 2 14.5 5.5
8 2 97.5 14.5
9 2 96 5.5
10 2 14.5 5.5
11 2 14.5 5.5
12 2 97.5 14.5
13 3 78.5 2.5
14 3 20.75 2.5
15 3 20.75 2.5
16 3 80 20.75
17 4 24 3.5
18 4 8 3.5
19 4 8 3.5
20 4 24 8
21 4 24 3.5
22 4 8 3.5
23 4 8 3.5
24 4 24 8
25 4 24 3.5
26 4 8 3.5
27 4 8 3.5
28 4 24 8
Out of the 4 orders there are 7 products, producing a list of 28
parts. I want a label for each part.
frm_Cornice. The fields in frm_Cornice are: [Qnty] [Width] [Length]
[Projection]. Each product ordered has an assembly of 4 parts whose
size vary depending on these inputs. I want to make a report of labels
that will identify each part.
frm_Cornice
[Qnty] [Width] [Length] [Projection]
Tbl_Specs
OrderID Qnty Width Length Projection
1 1 54 12 4
2 2 96 14.5 5.5
3 1 78.5 20 2
4 3 24 8 3.5
This would be the desired output:
Rpt_PartList
PartID OrderID Width Length
1 1 52.5 3.5
2 1 12 3.5
3 1 12 3.5
4 1 54 12
5 2 96 5.5
6 2 14.5 5.5
7 2 14.5 5.5
8 2 97.5 14.5
9 2 96 5.5
10 2 14.5 5.5
11 2 14.5 5.5
12 2 97.5 14.5
13 3 78.5 2.5
14 3 20.75 2.5
15 3 20.75 2.5
16 3 80 20.75
17 4 24 3.5
18 4 8 3.5
19 4 8 3.5
20 4 24 8
21 4 24 3.5
22 4 8 3.5
23 4 8 3.5
24 4 24 8
25 4 24 3.5
26 4 8 3.5
27 4 8 3.5
28 4 24 8
Out of the 4 orders there are 7 products, producing a list of 28
parts. I want a label for each part.
J
John W. Vinson
Maybe I over-simplified.
This database is a configurator based, manufacturing resource planner.
If you configure a product and then set [Qnty] to 2, I want a query
that creates 2 identical records.
example:
[Qnty=2]*PartA = PartA 1
PartA 2
One way to do this is to have a handy little utility table named Num with one
field N, filled with (say) 10000 records with values 1 to 10000. You can
create this set of records using Fill... Series in Excel and then import the
spreadsheet into a table.
Create a query by adding your parts table and Num, initially joining Qnty to
N. Then open the query in SQL view and edit
ON [yourtable].[Qnty] = [Num].[N]
to
ON [yourtable].[Qnty] >= [Num].[N]
John W. Vinson [MVP]
E
EvilSewingMachine
One way to do this is to have a handy little utility table named Num with one
field N, filled with (say) 10000 records with values 1 to 10000. You can
create this set of records using Fill... Series in Excel and then import the
spreadsheet into a table.
Create a query by adding your parts table and Num, initially joining Qnty to
N. Then open the query in SQL view and edit
ON [yourtable].[Qnty] = [Num].[N]
to
ON [yourtable].[Qnty] >= [Num].[N]
John W. Vinson [MVP]
After importing the spreadsheet and linking Qnty and N.
SQL comes up with:
SELECT qry_CorniceFace.Qnty, tbl_Num.N
FROM qry_CorniceFace INNER JOIN tbl_Num ON qry_CorniceFace.Qnty =
tbl_Num.N;
I edit it:
SELECT qry_CorniceFace.Qnty, tbl_Num.N
FROM qry_CorniceFace INNER JOIN tbl_Num ON qry_CorniceFace.Qnty >=
tbl_Num.N;
It works great! my only problem....
I don't understand why it works, could you elaborate on the theory
behind the solution? Now that I have a record for each individual
product, ( first step ) what if I wanted to break them down into their
4 parts and have an individual record for each part of the 4 part
assembly?
J
John W. Vinson
After importing the spreadsheet and linking Qnty and N.
SQL comes up with:
SELECT qry_CorniceFace.Qnty, tbl_Num.N
FROM qry_CorniceFace INNER JOIN tbl_Num ON qry_CorniceFace.Qnty =
tbl_Num.N;
I edit it:
SELECT qry_CorniceFace.Qnty, tbl_Num.N
FROM qry_CorniceFace INNER JOIN tbl_Num ON qry_CorniceFace.Qnty >=
tbl_Num.N;
It works great! my only problem....
I don't understand why it works, could you elaborate on the theory
behind the solution?
This is called a "non equi join". A typical "equal" join matches each record
in qry_CorniceFace with every record (only one of them as it happens) in Num
which has an exactly equal value.
The Non Equi Join instead matches each record in qry_CorniceFace with every
record in Num for which Qnty is greater than or equal to N: that is, if Qnty
is 4, it will match with the records in Num with values 1, 2, 3, and 4.
Now that I have a record for each individual
product, ( first step ) what if I wanted to break them down into their
4 parts and have an individual record for each part of the 4 part
assembly?
I have no idea what you mean here. I have no idea what an "assembly" is, nor
anything about the structure of qry_CorniceFace or the tables underlying it.
You can change this non equi join query into an Append query if that's
appropriate.
John W. Vinson [MVP]
S
Steve
Please explain how you get the four lines for OrderID 1 in Rpt_PartList from
Tbl_Specs. What you have for OrderID 1 in the report does not seem to match
what you have for OrderID 1 in the table.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
Tbl_Specs. What you have for OrderID 1 in the report does not seem to match
what you have for OrderID 1 in the table.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
E
EvilSewingMachine
frm_Cornice
Please explain how you get the four lines for OrderID 1 in
Rpt_PartList from
Tbl_Specs. What you have for OrderID 1 in the report does not seem to
match
what you have for OrderID 1 in the table.
Each PartID in Rpt_Partlist is 1 of 4 parts that make up an
assembly of parts. There are always 4. Width and Length are the
dimensions of the part. I use an iif statement to determine the
dimensions of the parts. ie: " iif([WidhtIO]=1,[Width],[Width]-1.5) "
or " iif([ReturnIO]=2,[Length]+.5,[Length]) ". [WidthIO] and
[ReturnIO] are in Tbl_Specs and accessed through Frm_Cornice using
check box option groups. That is why OrderID1 in the table doesn't
match OrderID1 in the Report, because depending on the configuration,
the the actual dimensions will differ from the dimensions as entered.
Part 1 is the Top, Part 2 is the Face and Parts 3 and 4 are Legs.
There is a different iif statement for each type of part. For the top
the formula is iif([WidthIO]=1,[Width],[Width]-1.5). For the face the
formula is iif([WidthIO]=1,[Width]+1.5,[Width]). For the 2 Legs the
formula is iif([ReturnIO]=1,[Return],[Return]-.5).
So I need a label report that lists the dimensions (as modified
by the iif statements) of each of the 4 parts of each assembly, and
the number of assemblies multiplied by the Qnty of assemblies ordered.
If someone orders 2 products, then this will report out 8 labels, 2
tops, 2 faces and 4 legs.
Questions?
[Qnty] [Width] [Length] [Projection]Tbl_Specs
OrderID Qnty Width Length Projection
1 1 54 x 2 x 4
2 2 96 x 14.5 x 5.5
3 1 78.5 x 20 x 2
4 3 24 x 8 x 3.5This would be the desired output:
Rpt_PartList
PartID OrderID Width Length
1 1 52.5 x 3.5
2 1 12 x 3.5
3 1 12 x 3.5
4 1 54 x 12
5 2 96 x 5.5
6 2 14.5 x 5.5
7 2 14.5 x 5.5
8 2 97.5 x 14.5
9 2 96 x 5.5
10 2 14.5 x 5.5
11 2 14.5 x 5.5
12 2 97.5 x 14.5
13 3 78.5 x 2.5
14 3 20.75 x 2.5
15 3 20.75 x 2.5
16 3 80 x 20.75
17 4 24 x 3.5
18 4 8 x 3.5
19 4 8 x 3.5
20 4 24 x 8
21 4 24 x 3.5
22 4 8 x 3.5
23 4 8 x 3.5
24 4 24 x 8
25 4 24 x 3.5
26 4 8 x 3.5
27 4 8 x 3.5
28 4 24 x 8
Please explain how you get the four lines for OrderID 1 in
Rpt_PartList from
Tbl_Specs. What you have for OrderID 1 in the report does not seem to
match
what you have for OrderID 1 in the table.
Each PartID in Rpt_Partlist is 1 of 4 parts that make up an
assembly of parts. There are always 4. Width and Length are the
dimensions of the part. I use an iif statement to determine the
dimensions of the parts. ie: " iif([WidhtIO]=1,[Width],[Width]-1.5) "
or " iif([ReturnIO]=2,[Length]+.5,[Length]) ". [WidthIO] and
[ReturnIO] are in Tbl_Specs and accessed through Frm_Cornice using
check box option groups. That is why OrderID1 in the table doesn't
match OrderID1 in the Report, because depending on the configuration,
the the actual dimensions will differ from the dimensions as entered.
Part 1 is the Top, Part 2 is the Face and Parts 3 and 4 are Legs.
There is a different iif statement for each type of part. For the top
the formula is iif([WidthIO]=1,[Width],[Width]-1.5). For the face the
formula is iif([WidthIO]=1,[Width]+1.5,[Width]). For the 2 Legs the
formula is iif([ReturnIO]=1,[Return],[Return]-.5).
So I need a label report that lists the dimensions (as modified
by the iif statements) of each of the 4 parts of each assembly, and
the number of assemblies multiplied by the Qnty of assemblies ordered.
If someone orders 2 products, then this will report out 8 labels, 2
tops, 2 faces and 4 legs.
Questions?