K
Kutty
Please explain what is grouping in MSAccess. How can I group a certain field
as per my requirement. I need more knowlege in this area.
as per my requirement. I need more knowlege in this area.
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.
A
Al Campagna
Kutty,
A "support" newsgroup is geared to not the ideal It's not the primary
function of these newsgroups to teach Access basics. Your best bet is to
have an Access reference book, and... combined with the Access Help system,
you should be able to learn the Access "basic concepts." Or... a local
beginner's Access course would be a great help in starting out.
These newsgroups are really geared toward "specific" problems that
Access users run into in their database development.
That said, I'll try to give you some very basic info on Grouping....
But...
Grouping can occur in queries, such as in a Totals query, and/or
Grouping can be used in reports.
Which are you concerned with?
Why not post some sample data records, and describe what it is you would
like to accomplish. For example, "Here's what I have vs. Here's what I'd
like to see."
We'll need just a few key fields, table name/s, fieldnames, and just
enough records to allow us to see the data you're working with.
That would probably be the best way to go.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
A "support" newsgroup is geared to not the ideal It's not the primary
function of these newsgroups to teach Access basics. Your best bet is to
have an Access reference book, and... combined with the Access Help system,
you should be able to learn the Access "basic concepts." Or... a local
beginner's Access course would be a great help in starting out.
These newsgroups are really geared toward "specific" problems that
Access users run into in their database development.
That said, I'll try to give you some very basic info on Grouping....
But...
Grouping can occur in queries, such as in a Totals query, and/or
Grouping can be used in reports.
Which are you concerned with?
Why not post some sample data records, and describe what it is you would
like to accomplish. For example, "Here's what I have vs. Here's what I'd
like to see."
We'll need just a few key fields, table name/s, fieldnames, and just
enough records to allow us to see the data you're working with.
That would probably be the best way to go.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
K
Kutty
I have created a simple invoice data base as below :
Table1 : main form
INV_NO : auto number, primary key
DATA : date/time
CUSTOMER_NAME : text
CASH/CHECK : text
Table2 : sub form
INV_NO : number
LINE_NO : number
DESCRIPTION : TEXT
UNIT_PRICE : number
AMOUNT : number
I have hided INV_NO field in the subform.
I created one form with both table1 and table2 as main form and sub form.
I want auto number in LINE_NO field. In case I make this field as auto I
get new line number. But this number is getting depends on every invoice
number. Let me tell you this way for example: Once I input 3 records in
invoice 1 I get first line in invoice 2 as four. I don’t want this way.
In
invoice 1 I need 1,2,3,4,5………so on as serial number. This way once I start
new invoice I need line number of description in 1,2,3,4,5…. so on. I hope
you got what I mean.
Table1 : main form
INV_NO : auto number, primary key
DATA : date/time
CUSTOMER_NAME : text
CASH/CHECK : text
Table2 : sub form
INV_NO : number
LINE_NO : number
DESCRIPTION : TEXT
UNIT_PRICE : number
AMOUNT : number
I have hided INV_NO field in the subform.
I created one form with both table1 and table2 as main form and sub form.
I want auto number in LINE_NO field. In case I make this field as auto I
get new line number. But this number is getting depends on every invoice
number. Let me tell you this way for example: Once I input 3 records in
invoice 1 I get first line in invoice 2 as four. I don’t want this way.
In
invoice 1 I need 1,2,3,4,5………so on as serial number. This way once I start
new invoice I need line number of description in 1,2,3,4,5…. so on. I hope
you got what I mean.
A
Al Campagna
Kutty,
Well, that really doesn't have anything to do with Grouping.
If I understand correctly you want each subform record to be
sequentially numbered, starting with 1 on each Invoice.
The main form InvNo should be linked to the subform via the common InvNo
(Parent to Child)
The LineNo field should be numeric (probably integer), not an
AutoNumber.
The LineNo will have to be "calculated" each time a record is added to
that subform. An autonumber can not be reset to 1 whenever a new Invoice
and associated subform record is added.
So... we need to determine the highest LineNo value in this subform...
when we create a new subform record... and add 1 to that value.
Using the InvID to only inquire about THIS Invoice record, the LineNo
DefaultValue should be set to...
(use your own object names, and the code is all on one line)
= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
The DMax says...
Find the largest LineNo so far, for this Invoice, and add 1 to it.
The NZ also adds this caveat...
If there are no LineNos for this Invoice yet (null), call it 0 and add 1
to it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
Well, that really doesn't have anything to do with Grouping.
If I understand correctly you want each subform record to be
sequentially numbered, starting with 1 on each Invoice.
The main form InvNo should be linked to the subform via the common InvNo
(Parent to Child)
The LineNo field should be numeric (probably integer), not an
AutoNumber.
The LineNo will have to be "calculated" each time a record is added to
that subform. An autonumber can not be reset to 1 whenever a new Invoice
and associated subform record is added.
So... we need to determine the highest LineNo value in this subform...
when we create a new subform record... and add 1 to that value.
Using the InvID to only inquire about THIS Invoice record, the LineNo
DefaultValue should be set to...
(use your own object names, and the code is all on one line)
= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
The DMax says...
Find the largest LineNo so far, for this Invoice, and add 1 to it.
The NZ also adds this caveat...
If there are no LineNos for this Invoice yet (null), call it 0 and add 1
to it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
K
Kutty
Thank you for this info. In form in which field I can click to insert this
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1" ????? Please
tell me.
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1" ????? Please
tell me.
Al Campagna said:Kutty,
Well, that really doesn't have anything to do with Grouping.
If I understand correctly you want each subform record to be
sequentially numbered, starting with 1 on each Invoice.
The main form InvNo should be linked to the subform via the common InvNo
(Parent to Child)
The LineNo field should be numeric (probably integer), not an
AutoNumber.
The LineNo will have to be "calculated" each time a record is added to
that subform. An autonumber can not be reset to 1 whenever a new Invoice
and associated subform record is added.
So... we need to determine the highest LineNo value in this subform...
when we create a new subform record... and add 1 to that value.
Using the InvID to only inquire about THIS Invoice record, the LineNo
DefaultValue should be set to...
(use your own object names, and the code is all on one line)
= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
The DMax says...
Find the largest LineNo so far, for this Invoice, and add 1 to it.
The NZ also adds this caveat...
If there are no LineNos for this Invoice yet (null), call it 0 and add 1
to it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
Kutty said:I have created a simple invoice data base as below :
Table1 : main form
INV_NO : auto number, primary key
DATA : date/time
CUSTOMER_NAME : text
CASH/CHECK : text
Table2 : sub form
INV_NO : number
LINE_NO : number
DESCRIPTION : TEXT
UNIT_PRICE : number
AMOUNT : number
I have hided INV_NO field in the subform.
I created one form with both table1 and table2 as main form and sub form.
I want auto number in LINE_NO field. In case I make this field as auto I
get new line number. But this number is getting depends on every invoice
number. Let me tell you this way for example: Once I input 3 records in
invoice 1 I get first line in invoice 2 as four. I don't want this way.
In
invoice 1 I need 1,2,3,4,5...so on as serial number. This way once I
start
new invoice I need line number of description in 1,2,3,4,5.. so on. I
hope
you got what I mean.
K
Kutty
I have typed the following in line number control :
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
But I got error message as "You may have entered an invalid identifier or
typed paranthesis following the Null constant"
Please tell me what to do.
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
But I got error message as "You may have entered an invalid identifier or
typed paranthesis following the Null constant"
Please tell me what to do.
Kutty said:Thank you for this info. In form in which field I can click to insert this
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1" ????? Please
tell me.
Al Campagna said:Kutty,
Well, that really doesn't have anything to do with Grouping.
If I understand correctly you want each subform record to be
sequentially numbered, starting with 1 on each Invoice.
The main form InvNo should be linked to the subform via the common InvNo
(Parent to Child)
The LineNo field should be numeric (probably integer), not an
AutoNumber.
The LineNo will have to be "calculated" each time a record is added to
that subform. An autonumber can not be reset to 1 whenever a new Invoice
and associated subform record is added.
So... we need to determine the highest LineNo value in this subform...
when we create a new subform record... and add 1 to that value.
Using the InvID to only inquire about THIS Invoice record, the LineNo
DefaultValue should be set to...
(use your own object names, and the code is all on one line)
= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
The DMax says...
Find the largest LineNo so far, for this Invoice, and add 1 to it.
The NZ also adds this caveat...
If there are no LineNos for this Invoice yet (null), call it 0 and add 1
to it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
Kutty said:I have created a simple invoice data base as below :
Table1 : main form
INV_NO : auto number, primary key
DATA : date/time
CUSTOMER_NAME : text
CASH/CHECK : text
Table2 : sub form
INV_NO : number
LINE_NO : number
DESCRIPTION : TEXT
UNIT_PRICE : number
AMOUNT : number
I have hided INV_NO field in the subform.
I created one form with both table1 and table2 as main form and sub form.
I want auto number in LINE_NO field. In case I make this field as auto I
get new line number. But this number is getting depends on every invoice
number. Let me tell you this way for example: Once I input 3 records in
invoice 1 I get first line in invoice 2 as four. I don't want this way.
In
invoice 1 I need 1,2,3,4,5...so on as serial number. This way once I
start
new invoice I need line number of description in 1,2,3,4,5.. so on. I
hope
you got what I mean.
:
Kutty,
A "support" newsgroup is geared to not the ideal It's not the primary
function of these newsgroups to teach Access basics. Your best bet is to
have an Access reference book, and... combined with the Access Help
system,
you should be able to learn the Access "basic concepts." Or... a local
beginner's Access course would be a great help in starting out.
These newsgroups are really geared toward "specific" problems that
Access users run into in their database development.
That said, I'll try to give you some very basic info on Grouping....
But...
Grouping can occur in queries, such as in a Totals query, and/or
Grouping can be used in reports.
Which are you concerned with?
Why not post some sample data records, and describe what it is you
would
like to accomplish. For example, "Here's what I have vs. Here's what I'd
like to see."
We'll need just a few key fields, table name/s, fieldnames, and just
enough records to allow us to see the data you're working with.
That would probably be the best way to go.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your
life."
Please explain what is grouping in MSAccess. How can I group a certain
field
as per my requirement. I need more knowlege in this area.
J
John Spencer
There is an error in that. There is a period where there should be a comma.
= NZ(DMax("[LineNo]" , "Table2" , "InvNo = " & InvNo), 0) + 1
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
= NZ(DMax("[LineNo]" , "Table2" , "InvNo = " & InvNo), 0) + 1
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Kutty said:I have typed the following in line number control :
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
But I got error message as "You may have entered an invalid identifier or
typed paranthesis following the Null constant"
Please tell me what to do.
Kutty said:Thank you for this info. In form in which field I can click to insert
this
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1" ?????
Please
tell me.
Al Campagna said:Kutty,
Well, that really doesn't have anything to do with Grouping.
If I understand correctly you want each subform record to be
sequentially numbered, starting with 1 on each Invoice.
The main form InvNo should be linked to the subform via the common
InvNo
(Parent to Child)
The LineNo field should be numeric (probably integer), not an
AutoNumber.
The LineNo will have to be "calculated" each time a record is added
to
that subform. An autonumber can not be reset to 1 whenever a new
Invoice
and associated subform record is added.
So... we need to determine the highest LineNo value in this
subform...
when we create a new subform record... and add 1 to that value.
Using the InvID to only inquire about THIS Invoice record, the
LineNo
DefaultValue should be set to...
(use your own object names, and the code is all on one line)
= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
The DMax says...
Find the largest LineNo so far, for this Invoice, and add 1 to it.
The NZ also adds this caveat...
If there are no LineNos for this Invoice yet (null), call it 0 and
add 1
to it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your
life."
I have created a simple invoice data base as below :
Table1 : main form
INV_NO : auto number, primary key
DATA : date/time
CUSTOMER_NAME : text
CASH/CHECK : text
Table2 : sub form
INV_NO : number
LINE_NO : number
DESCRIPTION : TEXT
UNIT_PRICE : number
AMOUNT : number
I have hided INV_NO field in the subform.
I created one form with both table1 and table2 as main form and sub
form.
I want auto number in LINE_NO field. In case I make this field as
auto I
get new line number. But this number is getting depends on every
invoice
number. Let me tell you this way for example: Once I input 3
records in
invoice 1 I get first line in invoice 2 as four. I don't want this
way.
In
invoice 1 I need 1,2,3,4,5...so on as serial number. This way once I
start
new invoice I need line number of description in 1,2,3,4,5.. so on.
I
hope
you got what I mean.
:
Kutty,
A "support" newsgroup is geared to not the ideal It's not the
primary
function of these newsgroups to teach Access basics. Your best bet
is to
have an Access reference book, and... combined with the Access Help
system,
you should be able to learn the Access "basic concepts." Or... a
local
beginner's Access course would be a great help in starting out.
These newsgroups are really geared toward "specific" problems
that
Access users run into in their database development.
That said, I'll try to give you some very basic info on
Grouping....
But...
Grouping can occur in queries, such as in a Totals query, and/or
Grouping can be used in reports.
Which are you concerned with?
Why not post some sample data records, and describe what it is
you
would
like to accomplish. For example, "Here's what I have vs. Here's
what I'd
like to see."
We'll need just a few key fields, table name/s, fieldnames, and
just
enough records to allow us to see the data you're working with.
That would probably be the best way to go.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your
life."
Please explain what is grouping in MSAccess. How can I group a
certain
field
as per my requirement. I need more knowlege in this area.
A
Al Campagna
Kutty,
See John Spencer's reply. That's your problem...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
See John Spencer's reply. That's your problem...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
Kutty said:I have typed the following in line number control :
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
But I got error message as "You may have entered an invalid identifier or
typed paranthesis following the Null constant"
Please tell me what to do.
Kutty said:Thank you for this info. In form in which field I can click to insert
this
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1" ?????
Please
tell me.
Al Campagna said:Kutty,
Well, that really doesn't have anything to do with Grouping.
If I understand correctly you want each subform record to be
sequentially numbered, starting with 1 on each Invoice.
The main form InvNo should be linked to the subform via the common
InvNo
(Parent to Child)
The LineNo field should be numeric (probably integer), not an
AutoNumber.
The LineNo will have to be "calculated" each time a record is added
to
that subform. An autonumber can not be reset to 1 whenever a new
Invoice
and associated subform record is added.
So... we need to determine the highest LineNo value in this
subform...
when we create a new subform record... and add 1 to that value.
Using the InvID to only inquire about THIS Invoice record, the
LineNo
DefaultValue should be set to...
(use your own object names, and the code is all on one line)
= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
The DMax says...
Find the largest LineNo so far, for this Invoice, and add 1 to it.
The NZ also adds this caveat...
If there are no LineNos for this Invoice yet (null), call it 0 and
add 1
to it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your
life."
I have created a simple invoice data base as below :
Table1 : main form
INV_NO : auto number, primary key
DATA : date/time
CUSTOMER_NAME : text
CASH/CHECK : text
Table2 : sub form
INV_NO : number
LINE_NO : number
DESCRIPTION : TEXT
UNIT_PRICE : number
AMOUNT : number
I have hided INV_NO field in the subform.
I created one form with both table1 and table2 as main form and sub
form.
I want auto number in LINE_NO field. In case I make this field as
auto I
get new line number. But this number is getting depends on every
invoice
number. Let me tell you this way for example: Once I input 3
records in
invoice 1 I get first line in invoice 2 as four. I don't want this
way.
In
invoice 1 I need 1,2,3,4,5...so on as serial number. This way once I
start
new invoice I need line number of description in 1,2,3,4,5.. so on.
I
hope
you got what I mean.
:
Kutty,
A "support" newsgroup is geared to not the ideal It's not the
primary
function of these newsgroups to teach Access basics. Your best bet
is to
have an Access reference book, and... combined with the Access Help
system,
you should be able to learn the Access "basic concepts." Or... a
local
beginner's Access course would be a great help in starting out.
These newsgroups are really geared toward "specific" problems
that
Access users run into in their database development.
That said, I'll try to give you some very basic info on
Grouping....
But...
Grouping can occur in queries, such as in a Totals query, and/or
Grouping can be used in reports.
Which are you concerned with?
Why not post some sample data records, and describe what it is
you
would
like to accomplish. For example, "Here's what I have vs. Here's
what I'd
like to see."
We'll need just a few key fields, table name/s, fieldnames, and
just
enough records to allow us to see the data you're working with.
That would probably be the best way to go.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your
life."
Please explain what is grouping in MSAccess. How can I group a
certain
field
as per my requirement. I need more knowlege in this area.
K
Kutty
I have typed correctly as per John Spencer told. But still getting error
message as :
"The database engine does not recognize either the field ‘INV_NO’ in a
validation expression or the default value in the table table2."
What shall I do? Why this error message.
message as :
"The database engine does not recognize either the field ‘INV_NO’ in a
validation expression or the default value in the table table2."
What shall I do? Why this error message.
Al Campagna said:Kutty,
See John Spencer's reply. That's your problem...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
Kutty said:I have typed the following in line number control :
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
But I got error message as "You may have entered an invalid identifier or
typed paranthesis following the Null constant"
Please tell me what to do.
Kutty said:Thank you for this info. In form in which field I can click to insert
this
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1" ?????
Please
tell me.
:
Kutty,
Well, that really doesn't have anything to do with Grouping.
If I understand correctly you want each subform record to be
sequentially numbered, starting with 1 on each Invoice.
The main form InvNo should be linked to the subform via the common
InvNo
(Parent to Child)
The LineNo field should be numeric (probably integer), not an
AutoNumber.
The LineNo will have to be "calculated" each time a record is added
to
that subform. An autonumber can not be reset to 1 whenever a new
Invoice
and associated subform record is added.
So... we need to determine the highest LineNo value in this
subform...
when we create a new subform record... and add 1 to that value.
Using the InvID to only inquire about THIS Invoice record, the
LineNo
DefaultValue should be set to...
(use your own object names, and the code is all on one line)
= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
The DMax says...
Find the largest LineNo so far, for this Invoice, and add 1 to it.
The NZ also adds this caveat...
If there are no LineNos for this Invoice yet (null), call it 0 and
add 1
to it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your
life."
I have created a simple invoice data base as below :
Table1 : main form
INV_NO : auto number, primary key
DATA : date/time
CUSTOMER_NAME : text
CASH/CHECK : text
Table2 : sub form
INV_NO : number
LINE_NO : number
DESCRIPTION : TEXT
UNIT_PRICE : number
AMOUNT : number
I have hided INV_NO field in the subform.
I created one form with both table1 and table2 as main form and sub
form.
I want auto number in LINE_NO field. In case I make this field as
auto I
get new line number. But this number is getting depends on every
invoice
number. Let me tell you this way for example: Once I input 3
records in
invoice 1 I get first line in invoice 2 as four. I don't want this
way.
In
invoice 1 I need 1,2,3,4,5...so on as serial number. This way once I
start
new invoice I need line number of description in 1,2,3,4,5.. so on.
I
hope
you got what I mean.
:
Kutty,
A "support" newsgroup is geared to not the ideal It's not the
primary
function of these newsgroups to teach Access basics. Your best bet
is to
have an Access reference book, and... combined with the Access Help
system,
you should be able to learn the Access "basic concepts." Or... a
local
beginner's Access course would be a great help in starting out.
These newsgroups are really geared toward "specific" problems
that
Access users run into in their database development.
That said, I'll try to give you some very basic info on
Grouping....
But...
Grouping can occur in queries, such as in a Totals query, and/or
Grouping can be used in reports.
Which are you concerned with?
Why not post some sample data records, and describe what it is
you
would
like to accomplish. For example, "Here's what I have vs. Here's
what I'd
like to see."
We'll need just a few key fields, table name/s, fieldnames, and
just
enough records to allow us to see the data you're working with.
That would probably be the best way to go.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your
life."
Please explain what is grouping in MSAccess. How can I group a
certain
field
as per my requirement. I need more knowlege in this area.
K
Kutty
Now I am sure that you got what I mean. Once I can solve the problem as John
Spencer told me I am happy. But getting error. I don't know why this error.
Spencer told me I am happy. But getting error. I don't know why this error.
Kutty said:I have typed correctly as per John Spencer told. But still getting error
message as :
"The database engine does not recognize either the field ‘INV_NO’ in a
validation expression or the default value in the table table2."
What shall I do? Why this error message.
Al Campagna said:Kutty,
See John Spencer's reply. That's your problem...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
Kutty said:I have typed the following in line number control :
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
But I got error message as "You may have entered an invalid identifier or
typed paranthesis following the Null constant"
Please tell me what to do.
:
Thank you for this info. In form in which field I can click to insert
this
"= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1" ?????
Please
tell me.
:
Kutty,
Well, that really doesn't have anything to do with Grouping.
If I understand correctly you want each subform record to be
sequentially numbered, starting with 1 on each Invoice.
The main form InvNo should be linked to the subform via the common
InvNo
(Parent to Child)
The LineNo field should be numeric (probably integer), not an
AutoNumber.
The LineNo will have to be "calculated" each time a record is added
to
that subform. An autonumber can not be reset to 1 whenever a new
Invoice
and associated subform record is added.
So... we need to determine the highest LineNo value in this
subform...
when we create a new subform record... and add 1 to that value.
Using the InvID to only inquire about THIS Invoice record, the
LineNo
DefaultValue should be set to...
(use your own object names, and the code is all on one line)
= NZ(DMax("[LineNo]","Table2"."InvNo = " & InvNo), 0) + 1
The DMax says...
Find the largest LineNo so far, for this Invoice, and add 1 to it.
The NZ also adds this caveat...
If there are no LineNos for this Invoice yet (null), call it 0 and
add 1
to it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your
life."
I have created a simple invoice data base as below :
Table1 : main form
INV_NO : auto number, primary key
DATA : date/time
CUSTOMER_NAME : text
CASH/CHECK : text
Table2 : sub form
INV_NO : number
LINE_NO : number
DESCRIPTION : TEXT
UNIT_PRICE : number
AMOUNT : number
I have hided INV_NO field in the subform.
I created one form with both table1 and table2 as main form and sub
form.
I want auto number in LINE_NO field. In case I make this field as
auto I
get new line number. But this number is getting depends on every
invoice
number. Let me tell you this way for example: Once I input 3
records in
invoice 1 I get first line in invoice 2 as four. I don't want this
way.
In
invoice 1 I need 1,2,3,4,5...so on as serial number. This way once I
start
new invoice I need line number of description in 1,2,3,4,5.. so on.
I
hope
you got what I mean.
:
Kutty,
A "support" newsgroup is geared to not the ideal It's not the
primary
function of these newsgroups to teach Access basics. Your best bet
is to
have an Access reference book, and... combined with the Access Help
system,
you should be able to learn the Access "basic concepts." Or... a
local
beginner's Access course would be a great help in starting out.
These newsgroups are really geared toward "specific" problems
that
Access users run into in their database development.
That said, I'll try to give you some very basic info on
Grouping....
But...
Grouping can occur in queries, such as in a Totals query, and/or
Grouping can be used in reports.
Which are you concerned with?
Why not post some sample data records, and describe what it is
you
would
like to accomplish. For example, "Here's what I have vs. Here's
what I'd
like to see."
We'll need just a few key fields, table name/s, fieldnames, and
just
enough records to allow us to see the data you're working with.
That would probably be the best way to go.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your
life."
Please explain what is grouping in MSAccess. How can I group a
certain
field
as per my requirement. I need more knowlege in this area.
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.