adding multiple records in one fell swoop

D

dave.degroot

Hi, In my database, I'd like to give users the ability to add more
than one record to a table at once, should they desire to.

Is there a way to do that?

I would guide them to a special form where they could just enter the
number of instances of a type of record and that number would create
that number of instances in my "instances" table (the date column I
would like to just populate with the current date instead of using the
exact date of the specific instance).

Is this possible?

Thanks so much!

David
 
G

grep

I reread your original post. I still think an append query will wind up
being part of your solution, but it's not going to be all of it, I don't
think. It sounds like what you're trying to do is like the Data Fill
function of Excel. I don't know how to do that in Access, though.

grep
 
J

John W. Vinson

I would guide them to a special form where they could just enter the
number of instances of a type of record and that number would create
that number of instances in my "instances" table (the date column I
would like to just populate with the current date instead of using the
exact date of the specific instance).
Click to expand...

Well... you might want to reconsider. Do you want to create X number of
*identical* records, or identical except for an Autonumber primary key? Why?
What will be done with all these peas in a pod once you have them created?

It can certainly be done - you can use a handy auxiliary table named Iotas
with one integer field Iota, with values from 0 to 10000 or so, and base an
Append query on it using a criterion on Iota of

< [Forms]![YourFormName]![HowMany]

but I'm just concerned that you may not really *need* to do this at all!

John W. Vinson [MVP]
 
D

dave.degroot

I would guide them to a special form where they could just enter the
number of instances of a type of record and that number would create
that number of instances in my "instances" table (the date column I
would like to just populate with the current date instead of using the
exact date of the specific instance).
Click to expand...

Well... you might want to reconsider. Do you want to create X number of
*identical* records, or identical except for an Autonumber primary key? Why?
What will be done with all these peas in a pod once you have them created?

It can certainly be done - you can use a handy auxiliary table named Iotas
with one integer field Iota, with values from 0 to 10000 or so, and base an
Append query on it using a criterion on Iota of

< [Forms]![YourFormName]![HowMany]

but I'm just concerned that you may not really *need* to do this at all!

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

Hi John, thanks for your post... I'm not sure I want to do this
either!

We collect data on an annual basis currently, so people add instances
of our products into our database in batches. So currently, a
record's primary key is determined by the date entered and just has
columns of data for the types of the products sold.

I am thinking that I would like to set up (is this normalization) the
system in a way to track every instance of a sale, but still allow
people to enter multiple instances should this be their entry of
choice.

In this way, I can run queries in a much easier fashion based on the
type of product, etc.

Thanks again for your help.

David
 
J

John W. Vinson

Hi John, thanks for your post... I'm not sure I want to do this
either!

We collect data on an annual basis currently, so people add instances
of our products into our database in batches. So currently, a
record's primary key is determined by the date entered and just has
columns of data for the types of the products sold.
Click to expand...

This design is fine for a spreadsheet, but is SIMPLY WRONG for a relational
database... as I gather that you have concluded!
I am thinking that I would like to set up (is this normalization) the
system in a way to track every instance of a sale, but still allow
people to enter multiple instances should this be their entry of
choice.
In this way, I can run queries in a much easier fashion based on the
type of product, etc.
Click to expand...

You certainly should have three tables, just as is done in the Northwind
sample database: Orders, Products, and OrderDetails are the tablenames there.
The OrderDetails table has a link to the Orders table (what's currently your
date-entered) and to the Products table (what is currently stored in
fieldnames in your spreadsheet table). It would also have fields for quantity,
price, etc. - not sure what data you are collecting.

It makes no sense, to me, to auto-enter many records for products sold. Surely
you need to identify which products WERE sold, and how many, and probably
other information about the sale, right? A Subform with a combo box allowing
you to select a product, on a Form with information about the whole day's
activity, would make it easy to just add records for the actual sales. Am I
misunderstanding the real-life situation?

John W. Vinson [MVP]
 
D

dave.degroot

This design is fine for a spreadsheet, but is SIMPLY WRONG for a relational
database... as I gather that you have concluded!


You certainly should have three tables, just as is done in the Northwind
sample database: Orders, Products, and OrderDetails are the tablenames there.
The OrderDetails table has a link to the Orders table (what's currently your
date-entered) and to the Products table (what is currently stored in
fieldnames in your spreadsheet table). It would also have fields for quantity,
price, etc. - not sure what data you are collecting.

It makes no sense, to me, to auto-enter many records for products sold. Surely
you need to identify which products WERE sold, and how many, and probably
other information about the sale, right? A Subform with a combo box allowing
you to select a product, on a Form with information about the whole day's
activity, would make it easy to just add records for the actual sales. Am I
misunderstanding the real-life situation?

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

I would like to collect data about the sale, however, one can do that
in batches as well.... e.g. 5 widgets sold in one month... I don't
want to have to ask my salespersons to record dates of every widet
sold in that month, but if they meet a certain criteria, (e.g. 5
widgets to customer A in that month (doesn't matter what day)), then I
don't care and don't want them to have to go to the trouble of doing
that...

However, when it comes to my queries, I need to have every sale be a
record so that I can query across them in a much better way. For this
reason, I think the batch entering of records makes sense to me.
Does that make sense?
 
D

dave.degroot

I would like to collect data about the sale, however, one can do that
in batches as well.... e.g. 5 widgets sold in one month... I don't
want to have to ask my salespersons to record dates of every widet
sold in that month, but if they meet a certain criteria, (e.g. 5
widgets to customer A in that month (doesn't matter what day)), then I
don't care and don't want them to have to go to the trouble of doing
that...

However, when it comes to my queries, I need to have every sale be a
record so that I can query across them in a much better way. For this
reason, I think the batch entering of records makes sense to me.
Does that make sense?- Hide quoted text -

- Show quoted text -
Click to expand...

and as you said, is not good database design!
 
D

dave.degroot

and as you said, is not good database design!- Hide quoted text -

- Show quoted text -
Click to expand...

Right, so they would be identical except for an Autonumber primary key.
 
D

dave.degroot

I would guide them to a special form where they could just enter the
number of instances of a type of record and that number would create
that number of instances in my "instances" table (the date column I
would like to just populate with the current date instead of using the
exact date of the specific instance).
Click to expand...

Well... you might want to reconsider. Do you want to create X number of
*identical* records, or identical except for an Autonumber primary key? Why?
What will be done with all these peas in a pod once you have them created?

It can certainly be done - you can use a handy auxiliary table named Iotas
with one integer field Iota, with values from 0 to 10000 or so, and base an
Append query on it using a criterion on Iota of

< [Forms]![YourFormName]![HowMany]

but I'm just concerned that you may not really *need* to do this at all!

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

John, how do I make the value of the field in the lotas table go from
0 to 10000?
 
J

John W. Vinson

Right, so they would be identical except for an Autonumber primary key.
Click to expand...

Ok... I'm flummoxed. It's not clear to me how your database is structured,
what information you are collecting, or - at this point - even what you're
asking!

Could you please back up and describe the context, and just what you want to
accomplish and why?

John W. Vinson [MVP]
 
J

John W. Vinson

John, how do I make the value of the field in the lotas table go from
0 to 10000?
Click to expand...

You can do it in code, but the easiest way is to do it in Excel. Just create a
new workbook (you won't be keeping it) in Excel; put 0 in the A1 cell, select
A1 through A10000, and use Edit... Fill... Series. Copy and paste the 10000
rows into your Iotas table.

John W. Vinson [MVP]
 
D

dave.degroot

You can do it in code, but the easiest way is to do it in Excel. Just create a
new workbook (you won't be keeping it) in Excel; put 0 in the A1 cell, select
A1 through A10000, and use Edit... Fill... Series. Copy and paste the 10000
rows into your Iotas table.

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

:) No problem.

My database collects data on an annual basis about certain "events".
I want to record the type of event, how many of this type, how long
they took, and some other factors.

Currently, as mentioned before, these entries are submitted, as you
suggested, in more of a spreadsheet form. I hate that set up and want
to do it in a database form and I'm pretty sure it can be done.
Further, I want to give users the option of entering one instance of
an "event" at a time, which doesn't work well in Excel.

I think this about sums it up... Please fire away with any questions
though... Quite new with Databases in general and Access as well.

Thanks for all your help though.

David
 
Top