THIS ONE IS FOR AN ACCESS GURU!

[Robert]

Here is my dilemma:

I am going to condense this scenario.

I have a query that pulls up two records with identical part numbers in the
first field.
In the second field, which I will call "Transaction" there is a value.
For the first record, the value is 1.
For the second record, the value is 2.
In the third field there is a date on which each "Transaction occurred".
I want to design a form that the user can type in the part number, which
will in turn pull up the value in record 1, field 1.
I would then like to dump this data, along with a final checkout number,
which should be 1, onto a report.

My only problem is that the date field is throwing my task of summing off.
It is keeping me from getting this final checkout number.

Is their something that I need to put in the query? In VB?

PLEASE HELP

 

[Ken Snell [MVP]]

You may have "condensed" your scenario too much.

What is "final checkout number"? What are you summing? How does the "date
field" ("Transaction occurred" field?) come into play with the summing
action? Do you use the record with a value of 2 ("Transaction") at all?

Post some sample data and what the result should be from those data. Likely
a query can do what you seek, but it's just not clear how everything is
working here.

 

[Robert]

Here is some sample data (We'll supposed there are only two record for March
'05):

Part# Date Transaction Process Transaction
Type
Quantity
------ ------ ----------- --------
------------------
1-123-3 03/04/05 IC A 3
1-123-3 03/06/05 IR A 2

On the form, the user can search by any (or all) of the following criteria;

Date Range
Begin Date: 03/01/05
End Date: 04/01/05

or

Process

or

Part Number


The way the condensed version should read (if filtered as follows:

Begin Date: 03/01/05
End Date: 03/06/05
Process: A

Results (As they should appear on the report this form is linked to):

Part# Transaction Qty Process
------ ----------------- --------
1-123-3 1 A


The transcation quantity should be 1 because the "IC"s for this part number
subtract the "IR"s. There should only be one instance of a given part
number, and one instance of the process.

I hope this helps. Thanks.
RSF

 

[Captain OhNo]

Hi Robert,
Looking over your question and the sample data, I interpret that your
Transaction Quantity should be 5 for the Summary data for that Part # and
Process.

However, I think I know what you are asking. If what you want is for the
Transaction Quantity to be totalled for that Part # and Process for the given
dates, then the following information may be helpful.

You can base the report on nested queries.
In the first query you put the Date, Transaction Quantity, Part Number, etc.
In the Date field in Query Design View you input the criteria Between [] And
[], which will prompt the User to input the beginning and ending dates.

Create a 2nd query, using the above query as your data source. In Query
Design View select the Sum icon (looks like a Z). Do not put the Date Field
as one of the fields in the query becasue this will cause the query to
summarize by Date. Under the Transaction Quantity (if that is what you are
summarizing) , use the drop down arrow to select Sum.

Hope this helps.
Captain OhNO

 

[Ken Snell [MVP]]

Not sure if I've fully understood, but it appears that a query for doing
this might be something like this:

SELECT [Part#], Max([Date]),
Sum([Transaction]*(IIf([Transaction Type]="IC", 1, -1)))
WHERE [Date] Between Forms!FormName![Start Date]
And Forms!FormName![End Date] AND
[Process]=Forms!FormName![Process]
GROUP BY [Part#];

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

Sorry... left out one important part:

SELECT [Part#], Max([Date]),
Sum([Transaction]*(IIf([Transaction Type]="IC", 1, -1)))
FROM YourTableName
WHERE [Date] Between Forms!FormName![Start Date]
And Forms!FormName![End Date] AND
[Process]=Forms!FormName![Process]
GROUP BY [Part#];


By the way, using Date as a field name or a control name is not
recommended. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>

Not sure if I've fully understood, but it appears that a query for doing
this might be something like this:

SELECT [Part#], Max([Date]),
Sum([Transaction]*(IIf([Transaction Type]="IC", 1, -1)))
WHERE [Date] Between Forms!FormName![Start Date]
And Forms!FormName![End Date] AND
[Process]=Forms!FormName![Process]
GROUP BY [Part#];

--

Ken Snell
<MS ACCESS MVP>

Here is some sample data (We'll supposed there are only two record for
March
'05):

Part# Date Transaction Process Transaction
Type
Quantity
------ ------ ----------- --------
------------------
1-123-3 03/04/05 IC A 3
1-123-3 03/06/05 IR A 2

On the form, the user can search by any (or all) of the following
criteria;

Date Range
Begin Date: 03/01/05
End Date: 04/01/05

or

Process

or

Part Number


The way the condensed version should read (if filtered as follows:

Begin Date: 03/01/05
End Date: 03/06/05
Process: A

Results (As they should appear on the report this form is linked to):

Part# Transaction Qty Process
------ ----------------- --------
1-123-3 1 A


The transcation quantity should be 1 because the "IC"s for this part
number
subtract the "IR"s. There should only be one instance of a given part
number, and one instance of the process.

I hope this helps. Thanks.
RSF

 

[Robert]

Ken, where would I type your statements at? In the criteria of a query
field, or in VB? Thanks
--
RSF

Not sure if I've fully understood, but it appears that a query for doing
this might be something like this:

SELECT [Part#], Max([Date]),
Sum([Transaction]*(IIf([Transaction Type]="IC", 1, -1)))
WHERE [Date] Between Forms!FormName![Start Date]
And Forms!FormName![End Date] AND
[Process]=Forms!FormName![Process]
GROUP BY [Part#];

--

Ken Snell
<MS ACCESS MVP>

Here is some sample data (We'll supposed there are only two record for
March
'05):

Part# Date Transaction Process Transaction
Type
Quantity
------ ------ ----------- --------
------------------
1-123-3 03/04/05 IC A
3
1-123-3 03/06/05 IR A
2

On the form, the user can search by any (or all) of the following
criteria;

Date Range
Begin Date: 03/01/05
End Date: 04/01/05

or

Process

or

Part Number


The way the condensed version should read (if filtered as follows:

Begin Date: 03/01/05
End Date: 03/06/05
Process: A

Results (As they should appear on the report this form is linked to):

Part# Transaction Qty Process
------ ----------------- --------
1-123-3 1 A


The transcation quantity should be 1 because the "IC"s for this part
number
subtract the "IR"s. There should only be one instance of a given part
number, and one instance of the process.

I hope this helps. Thanks.
RSF

 

[Ken Snell [MVP]]

What I posted is the SQL statement of an example query. You would create a
new query, select no tables, click OK, click SQL view (on toolbar), and
paste the statement into the window; then switch back to design view to see
the QBE version.
--

Ken Snell
<MS ACCESS MVP>

Ken, where would I type your statements at? In the criteria of a query
field, or in VB? Thanks
--
RSF

Not sure if I've fully understood, but it appears that a query for doing
this might be something like this:

SELECT [Part#], Max([Date]),
Sum([Transaction]*(IIf([Transaction Type]="IC", 1, -1)))
WHERE [Date] Between Forms!FormName![Start Date]
And Forms!FormName![End Date] AND
[Process]=Forms!FormName![Process]
GROUP BY [Part#];

--

Ken Snell
<MS ACCESS MVP>

Here is some sample data (We'll supposed there are only two record for
March
'05):

Part# Date Transaction Process
Transaction
Type
Quantity
------ ------ ----------- --------
------------------
1-123-3 03/04/05 IC A
3
1-123-3 03/06/05 IR A
2

On the form, the user can search by any (or all) of the following
criteria;

Date Range
Begin Date: 03/01/05
End Date: 04/01/05

or

Process

or

Part Number


The way the condensed version should read (if filtered as follows:

Begin Date: 03/01/05
End Date: 03/06/05
Process: A

Results (As they should appear on the report this form is linked to):

Part# Transaction Qty Process
------ ----------------- --------
1-123-3 1 A


The transcation quantity should be 1 because the "IC"s for this part
number
subtract the "IR"s. There should only be one instance of a given part
number, and one instance of the process.

I hope this helps. Thanks.
RSF


:

You may have "condensed" your scenario too much.

What is "final checkout number"? What are you summing? How does the
"date
field" ("Transaction occurred" field?) come into play with the summing
action? Do you use the record with a value of 2 ("Transaction") at
all?

Post some sample data and what the result should be from those data.
Likely
a query can do what you seek, but it's just not clear how everything
is
working here.


--

Ken Snell
<MS ACCESS MVP>


Here is my dilemma:

I am going to condense this scenario.

I have a query that pulls up two records with identical part numbers
in
the
first field.
In the second field, which I will call "Transaction" there is a
value.
For the first record, the value is 1.
For the second record, the value is 2.
In the third field there is a date on which each "Transaction
occurred".
I want to design a form that the user can type in the part number,
which
will in turn pull up the value in record 1, field 1.
I would then like to dump this data, along with a final checkout
number,
which should be 1, onto a report.

My only problem is that the date field is throwing my task of
summing
off.
It is keeping me from getting this final checkout number.

Is their something that I need to put in the query? In VB?

PLEASE HELP

 

[Robert]

How does the Between [] And [] work? When I enter this into the query, it
only asks for one parameter. Shouldn't it ask for two;

Between [first date] And [second date]

Am I not understanding this part right?
--
RSF

What I posted is the SQL statement of an example query. You would create a
new query, select no tables, click OK, click SQL view (on toolbar), and
paste the statement into the window; then switch back to design view to see
the QBE version.
--

Ken Snell
<MS ACCESS MVP>

Ken, where would I type your statements at? In the criteria of a query
field, or in VB? Thanks
--
RSF

Not sure if I've fully understood, but it appears that a query for doing
this might be something like this:

SELECT [Part#], Max([Date]),
Sum([Transaction]*(IIf([Transaction Type]="IC", 1, -1)))
WHERE [Date] Between Forms!FormName![Start Date]
And Forms!FormName![End Date] AND
[Process]=Forms!FormName![Process]
GROUP BY [Part#];

--

Ken Snell
<MS ACCESS MVP>




Here is some sample data (We'll supposed there are only two record for
March
'05):

Part# Date Transaction Process
Transaction
Type
Quantity
------ ------ ----------- --------
------------------
1-123-3 03/04/05 IC A
3
1-123-3 03/06/05 IR A
2

On the form, the user can search by any (or all) of the following
criteria;

Date Range
Begin Date: 03/01/05
End Date: 04/01/05

or

Process

or

Part Number


The way the condensed version should read (if filtered as follows:

Begin Date: 03/01/05
End Date: 03/06/05
Process: A

Results (As they should appear on the report this form is linked to):

Part# Transaction Qty Process
------ ----------------- --------
1-123-3 1 A


The transcation quantity should be 1 because the "IC"s for this part
number
subtract the "IR"s. There should only be one instance of a given part
number, and one instance of the process.

I hope this helps. Thanks.
RSF


:

You may have "condensed" your scenario too much.

What is "final checkout number"? What are you summing? How does the
"date
field" ("Transaction occurred" field?) come into play with the summing
action? Do you use the record with a value of 2 ("Transaction") at
all?

Post some sample data and what the result should be from those data.
Likely
a query can do what you seek, but it's just not clear how everything
is
working here.


--

Ken Snell
<MS ACCESS MVP>


Here is my dilemma:

I am going to condense this scenario.

I have a query that pulls up two records with identical part numbers
in
the
first field.
In the second field, which I will call "Transaction" there is a
value.
For the first record, the value is 1.
For the second record, the value is 2.
In the third field there is a date on which each "Transaction
occurred".
I want to design a form that the user can type in the part number,
which
will in turn pull up the value in record 1, field 1.
I would then like to dump this data, along with a final checkout
number,
which should be 1, onto a report.

My only problem is that the date field is throwing my task of
summing
off.
It is keeping me from getting this final checkout number.

Is their something that I need to put in the query? In VB?

PLEASE HELP

 

[Ken Snell [MVP]]

It will ask for any parameter that it cannot find. If you have a control
named "Start Date" or "End Date" on the form (and I am assuming that you
have both controls because you said you want to use the form's controls as
the source of the parameters), then the query will not ask for that
parameter -- instead, it will read it from the form.

--

Ken Snell
<MS ACCESS MVP>

How does the Between [] And [] work? When I enter this into the query, it
only asks for one parameter. Shouldn't it ask for two;

Between [first date] And [second date]

Am I not understanding this part right?
--
RSF

What I posted is the SQL statement of an example query. You would create
a
new query, select no tables, click OK, click SQL view (on toolbar), and
paste the statement into the window; then switch back to design view to
see
the QBE version.
--

Ken Snell
<MS ACCESS MVP>

Ken, where would I type your statements at? In the criteria of a query
field, or in VB? Thanks
--
RSF


:

Not sure if I've fully understood, but it appears that a query for
doing
this might be something like this:

SELECT [Part#], Max([Date]),
Sum([Transaction]*(IIf([Transaction Type]="IC", 1, -1)))
WHERE [Date] Between Forms!FormName![Start Date]
And Forms!FormName![End Date] AND
[Process]=Forms!FormName![Process]
GROUP BY [Part#];

--

Ken Snell
<MS ACCESS MVP>




Here is some sample data (We'll supposed there are only two record
for
March
'05):

Part# Date Transaction Process
Transaction
Type
Quantity
------ ------ ----------- --------
------------------
1-123-3 03/04/05 IC A
3
1-123-3 03/06/05 IR A
2

On the form, the user can search by any (or all) of the following
criteria;

Date Range
Begin Date: 03/01/05
End Date: 04/01/05

or

Process

or

Part Number


The way the condensed version should read (if filtered as follows:

Begin Date: 03/01/05
End Date: 03/06/05
Process: A

Results (As they should appear on the report this form is linked
to):

Part# Transaction Qty Process
------ ----------------- --------
1-123-3 1 A


The transcation quantity should be 1 because the "IC"s for this part
number
subtract the "IR"s. There should only be one instance of a given
part
number, and one instance of the process.

I hope this helps. Thanks.
RSF


:

You may have "condensed" your scenario too much.

What is "final checkout number"? What are you summing? How does the
"date
field" ("Transaction occurred" field?) come into play with the
summing
action? Do you use the record with a value of 2 ("Transaction") at
all?

Post some sample data and what the result should be from those
data.
Likely
a query can do what you seek, but it's just not clear how
everything
is
working here.


--

Ken Snell
<MS ACCESS MVP>


Here is my dilemma:

I am going to condense this scenario.

I have a query that pulls up two records with identical part
numbers
in
the
first field.
In the second field, which I will call "Transaction" there is a
value.
For the first record, the value is 1.
For the second record, the value is 2.
In the third field there is a date on which each "Transaction
occurred".
I want to design a form that the user can type in the part
number,
which
will in turn pull up the value in record 1, field 1.
I would then like to dump this data, along with a final checkout
number,
which should be 1, onto a report.

My only problem is that the date field is throwing my task of
summing
off.
It is keeping me from getting this final checkout number.

Is their something that I need to put in the query? In VB?

PLEASE HELP

 

[Robert]

OK OK... Now I understand. And everything appears to be working.

Time for part 2. How do I design a form around this query? I want the form
to have two text boxes: 1 for begin date, and one for end date. These two
boxes should be located at the top of the form, with the bottom "half" grayed
out. When the two dates are entered, the user will press a command button
that will then display the results for the given date range in the grayed out
section, or bottom half of the form. Is this possible? Thanks

By the way, you have been a big help so far. Thanks
--
RSF

It will ask for any parameter that it cannot find. If you have a control
named "Start Date" or "End Date" on the form (and I am assuming that you
have both controls because you said you want to use the form's controls as
the source of the parameters), then the query will not ask for that
parameter -- instead, it will read it from the form.

--

Ken Snell
<MS ACCESS MVP>

How does the Between [] And [] work? When I enter this into the query, it
only asks for one parameter. Shouldn't it ask for two;

Between [first date] And [second date]

Am I not understanding this part right?
--
RSF

What I posted is the SQL statement of an example query. You would create
a
new query, select no tables, click OK, click SQL view (on toolbar), and
paste the statement into the window; then switch back to design view to
see
the QBE version.
--

Ken Snell
<MS ACCESS MVP>



Ken, where would I type your statements at? In the criteria of a query
field, or in VB? Thanks
--
RSF


:

Not sure if I've fully understood, but it appears that a query for
doing
this might be something like this:

SELECT [Part#], Max([Date]),
Sum([Transaction]*(IIf([Transaction Type]="IC", 1, -1)))
WHERE [Date] Between Forms!FormName![Start Date]
And Forms!FormName![End Date] AND
[Process]=Forms!FormName![Process]
GROUP BY [Part#];

--

Ken Snell
<MS ACCESS MVP>




Here is some sample data (We'll supposed there are only two record
for
March
'05):

Part# Date Transaction Process
Transaction
Type
Quantity
------ ------ ----------- --------
------------------
1-123-3 03/04/05 IC A
3
1-123-3 03/06/05 IR A
2

On the form, the user can search by any (or all) of the following
criteria;

Date Range
Begin Date: 03/01/05
End Date: 04/01/05

or

Process

or

Part Number


The way the condensed version should read (if filtered as follows:

Begin Date: 03/01/05
End Date: 03/06/05
Process: A

Results (As they should appear on the report this form is linked
to):

Part# Transaction Qty Process
------ ----------------- --------
1-123-3 1 A


The transcation quantity should be 1 because the "IC"s for this part
number
subtract the "IR"s. There should only be one instance of a given
part
number, and one instance of the process.

I hope this helps. Thanks.
RSF


:

You may have "condensed" your scenario too much.

What is "final checkout number"? What are you summing? How does the
"date
field" ("Transaction occurred" field?) come into play with the
summing
action? Do you use the record with a value of 2 ("Transaction") at
all?

Post some sample data and what the result should be from those
data.
Likely
a query can do what you seek, but it's just not clear how
everything
is
working here.


--

Ken Snell
<MS ACCESS MVP>


Here is my dilemma:

I am going to condense this scenario.

I have a query that pulls up two records with identical part
numbers
in
the
first field.
In the second field, which I will call "Transaction" there is a
value.
For the first record, the value is 1.
For the second record, the value is 2.
In the third field there is a date on which each "Transaction
occurred".
I want to design a form that the user can type in the part
number,
which
will in turn pull up the value in record 1, field 1.
I would then like to dump this data, along with a final checkout
number,
which should be 1, onto a report.

My only problem is that the date field is throwing my task of
summing
off.
It is keeping me from getting this final checkout number.

Is their something that I need to put in the query? In VB?

PLEASE HELP

 

[Ken Snell [MVP]]

Create a form in design view. Set the form to the continous forms view
(Format tab of the Properties window for the form). Set the form's
recordsource to the query that I posted.

Make the form header and form footer sections visible. Put your textboxes
for the date values in the form header section. Put a command button (name
it cmdFilter) in the form header section.

Use this code on the command button's Click event:

Private Sub cmdFilter_Click()
Me.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>

 

[Hasan Farrokhmanesh]

What isgoing on?

Sorry... left out one important part:

SELECT [Part#], Max([Date]),
Sum([Transaction]*(IIf([Transaction Type]="IC", 1, -1)))
FROM YourTableName
WHERE [Date] Between Forms!FormName![Start Date]
And Forms!FormName![End Date] AND
[Process]=Forms!FormName![Process]
GROUP BY [Part#];


By the way, using Date as a field name or a control name is not
recommended. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>

Not sure if I've fully understood, but it appears that a query for doing
this might be something like this:

SELECT [Part#], Max([Date]),
Sum([Transaction]*(IIf([Transaction Type]="IC", 1, -1)))
WHERE [Date] Between Forms!FormName![Start Date]
And Forms!FormName![End Date] AND
[Process]=Forms!FormName![Process]
GROUP BY [Part#];

--

Ken Snell
<MS ACCESS MVP>

Here is some sample data (We'll supposed there are only two record for
March
'05):

Part# Date Transaction Process Transaction
Type
Quantity
------ ------ ----------- --------
------------------
1-123-3 03/04/05 IC A 3
1-123-3 03/06/05 IR A 2

On the form, the user can search by any (or all) of the following
criteria;

Date Range
Begin Date: 03/01/05
End Date: 04/01/05

or

Process

or

Part Number


The way the condensed version should read (if filtered as follows:

Begin Date: 03/01/05
End Date: 03/06/05
Process: A

Results (As they should appear on the report this form is linked to):

Part# Transaction Qty Process
------ ----------------- --------
1-123-3 1 A


The transcation quantity should be 1 because the "IC"s for this part
number
subtract the "IR"s. There should only be one instance of a given part
number, and one instance of the process.

I hope this helps. Thanks.
RSF


:

You may have "condensed" your scenario too much.

What is "final checkout number"? What are you summing? How does the
"date
field" ("Transaction occurred" field?) come into play with the summing
action? Do you use the record with a value of 2 ("Transaction") at all?

Post some sample data and what the result should be from those data.
Likely
a query can do what you seek, but it's just not clear how everything is
working here.


--

Ken Snell
<MS ACCESS MVP>


Here is my dilemma:

I am going to condense this scenario.

I have a query that pulls up two records with identical part numbers
in
the
first field.
In the second field, which I will call "Transaction" there is a value.
For the first record, the value is 1.
For the second record, the value is 2.
In the third field there is a date on which each "Transaction
occurred".
I want to design a form that the user can type in the part number,
which
will in turn pull up the value in record 1, field 1.
I would then like to dump this data, along with a final checkout
number,
which should be 1, onto a report.

My only problem is that the date field is throwing my task of summing
off.
It is keeping me from getting this final checkout number.

Is their something that I need to put in the query? In VB?

PLEASE HELP