Group Records

[alex]

Hello All,

I’m in the middle of constructing a database for a group of users that
deal with files, like the kind you stuff in a drawer.

I have a pretty simple table(s) structure with a single form (I have a
subform but it’s only for editing).

My problem/question is that the database needs to capture what files
came into the group together. The files are often in groups of two or
three and I need to account for this grouping (the only delineation is
that they were banded together!).

I know a properly normalized table will NOT have fields that look like
this: File1; File2; File3; etc., but I don’t know how else to group
the files when there’s more than one.

To put it another way; I want my table to look like this:

RecordID; RecordDate; EmployeeID; FileNumber

But, let’s say I have three records…

FileNumber
A21
A03
A11

I need to capture somewhere/somehow that A21 and A11 were together
(are a group) when they’re data entered into the database.

Has anyone had a similar experience?

Thanks,
alex

 

[alex]

How about these tables:
TblEmployee
EmployeeID
etc

TblRecord
RecordID
RecordDate
RecordNumber
RecordGroup        (1 to whatever)       *can be left blanklike for A03 in
example
EmployeeID
etc

Steve
(e-mail address removed)


Hello All,

I’m in the middle of constructing a database for a group of users that
deal with files, like the kind you stuff in a drawer.

I have a pretty simple table(s) structure with a single form (I have a
subform but it’s only for editing).

My problem/question is that the database needs to capture what files
came into the group together.  The files are often in groups of two or
three and I need to account for this grouping (the only delineation is
that they were banded together!).

I know a properly normalized table will NOT have fields that look like
this: File1; File2; File3; etc., but I don’t know how else to group
the files when there’s more than one.

To put it another way; I want my table to look like this:

RecordID; RecordDate; EmployeeID; FileNumber

But, let’s say I have three records…

FileNumber
A21
A03
A11

I need to capture somewhere/somehow that A21 and A11 were together
(are a group) when they’re data entered into the database.

Has anyone had a similar experience?

Thanks,
alex

Thanks Steve for your help…

How would you recommend setting that up in table/form setting?

The files are grouped merely by the presence of a rubber band—there is
no “group number.”

I suppose you could arbitrarily assign a single number to a specific
group, but it couldn’t be an autonum because the number would have to
stay the same for each group.

The form would also require a command button or some other mechanism
to “start” and “end” a group, which could get tricky, especially if
the data-entry clerk isn’t paying attention.

Is this what you had in mind? I may be making things more difficult
than they have to be!

alex

 

[alex]

Even though your files are bound together, you need some way of uniquely
identifying tje rubber band. Doing this delineates which files are bound
together. I chose to identify a group by a number because it is easy to make
a number sequential to keep track of which numbers were used and there are
an infinite number of numbers to use.

Regarding the data-entry clerk paying attention .....

Maybe another design of the tables would lessen the chances of something
going wrong:

TblGroup
GroupID
RecordDate

TblRecord
RecordID
GroupID
RecordNumber
EmployeeID
etc

For data entry use a form/subform. The main form would be based on TblGroup
and the subform would be based on TblRecord. The main form would be a single
form and the subform would be a continous form. The Linkmaster and Linkchild
properties would be set to GroupID. The advantage here is that when you go
to a new record in the main form and enter a record date, Access will
automatically enter a unique GroupID because it is autonumber. This
alleviates possible data-entry clerk error if the clerk has to assign the
group number. For data entry you would first enter the date in the main
form. Then you would enter one or more files that were bound together in the
group. As a form/subform, Access would automatically enter the GroupID in
the subform and it would be the same as Access assigned to the group when
you entered the date in the main form.

Steve















Thanks Steve for your help…

How would you recommend setting that up in table/form setting?

The files are grouped merely by the presence of a rubber band—there is
no “group number.”

I suppose you could arbitrarily assign a single number to a specific
group, but it couldn’t be an autonum because the number would have to
stay the same for each group.

The form would also require a command button or some other mechanism
to “start” and “end” a group, which could get tricky, especially if
the data-entry clerk isn’t paying attention.

Is this what you had in mind?  I may be making things more difficult
than they have to be!

alex- Hide quoted text -

- Show quoted text -

Thanks Steve for the clarification...I think I'm on the right track
now!
alex

 

[Tuscmavis]

How about these tables:
TblEmployee
EmployeeID
etc

TblRecord
RecordID
RecordDate
RecordNumber
RecordGroup (1 to whatever) *can be left blank like for A03 in
example
EmployeeID
etc

Steve
(e-mail address removed)


Hello All,

I’m in the middle of constructing a database for a group of users that
deal with files, like the kind you stuff in a drawer.

I have a pretty simple table(s) structure with a single form (I have a
subform but it’s only for editing).

My problem/question is that the database needs to capture what files
came into the group together. The files are often in groups of two or
three and I need to account for this grouping (the only delineation is
that they were banded together!).

I know a properly normalized table will NOT have fields that look like
this: File1; File2; File3; etc., but I don’t know how else to group
the files when there’s more than one.

To put it another way; I want my table to look like this:

RecordID; RecordDate; EmployeeID; FileNumber

But, let’s say I have three records…

FileNumber
A21
A03
A11

I need to capture somewhere/somehow that A21 and A11 were together
(are a group) when they’re data entered into the database.

Has anyone had a similar experience?

Thanks,
alex

Thanks Steve for your help…

How would you recommend setting that up in table/form setting?

The files are grouped merely by the presence of a rubber band—there is
no “group number.”

I suppose you could arbitrarily assign a single number to a specific
group, but it couldn’t be an autonum because the number would have to
stay the same for each group.

The form would also require a command button or some other mechanism
to “start” and “end” a group, which could get tricky, especially if
the data-entry clerk isn’t paying attention.

Is this what you had in mind? I may be making things more difficult
than they have to be!

alex