How do I repeat data in one record to another?

I am trying to repeat data from one record into other records. For example,
I have created a table for ticket sales, where the ticket number is the
primary key. I would like to make it possible to enter information for a
person (name, phone, type of pmt, etc) for multiple tickets. That is, I want
to enter info for one person and have it autopopulate in other records
according to the other ticket numbers they purchased.
I am thinking I need to have a list of tickets and then another sublist for
the repeated tickets, but am not sure how to go about doing that.
Thanks!

Al,

On the basis of what you have said so far, I think your table design
needs to be along these lines...

Table: People
PersonID (primary key)
PersonName
PhoneNumber
(plus any other data specific to each person)

Table: Purchases
PurchaseID (primary key)
PurchaseDate
PersonID (foreign key to People table)
PaymentType
PaymentAmount
ReceiptNumber
(plus any other data specific to each transaction)

Table: TicketsSold
PurchaseID (foreign key to Purchases table)
TicketNumber
(plus any other data specific to each ticket)

This allows any given person to make more than one ticket purchase, and
on any given purchase occasion to purchase more than one ticket. I
imagine it would not be necessary for you to have a table for a master
list of all available ticket numbers.

--
Steve Schapel, Microsoft Access MVP


Al wrote:
I am trying to repeat data from one record into other records. For example,
I have created a table for ticket sales, where the ticket number is the
primary key. I would like to make it possible to enter information for a
person (name, phone, type of pmt, etc) for multiple tickets. That is, I want
to enter info for one person and have it autopopulate in other records
according to the other ticket numbers they purchased.
I am thinking I need to have a list of tickets and then another sublist for
the repeated tickets, but am not sure how to go about doing that.
Thanks!

Thank you for the help!

A couple of follow ups:
Currently, I have one table for all the information from ticket #'s, name,
pymt type, etc. I do this to keep the number of tables down as we manage
tickets sales for several shows at once. (up to 15 shows at any given time)
From what you suggest, it looks like we should create more tables. Is there
a way to consolidate the info into one table, as we are trying to do, and
still be able to repeat information from one record to another?
Because we use just one table, I was thinking I could repeat data from
certain fields by way of a query. If a person purchases 4 tickets, each
ticket having a specific number that we need to track, I would like to be
able to enter the person's info (i.e. name, phone, etc) and fill out the
ticket number and price per each ticket they purchase. Each ticket must be
tracked separately for reporting purposes. The personal info of the
purchaser would then be copied to the multiple tickets, which would be new
records.
As you can tell, I am very new at this, and really appreciate all your help.
Now if I want to make a form to fill out info when a purchase is made, I
would have the fields on the table name, phone, etc, along with the ability
to enter ticket numbers and their prices, which would then be added to the
table, I imagine, by means of a query that would do as I mentioned above.
Does this sound feasible?

Just as a reference to what we are doing, here are some specifics:
-Each ticket must be tracked separately
-All information must be attached to a ticket number
-Persons who buy multiple tickets must have all personal info attached to
all their tickets

Thanks!

Al


"Steve Schapel" wrote:

Al,

On the basis of what you have said so far, I think your table design
needs to be along these lines...

Table: People
PersonID (primary key)
PersonName
PhoneNumber
(plus any other data specific to each person)

Table: Purchases
PurchaseID (primary key)
PurchaseDate
PersonID (foreign key to People table)
PaymentType
PaymentAmount
ReceiptNumber
(plus any other data specific to each transaction)

Table: TicketsSold
PurchaseID (foreign key to Purchases table)
TicketNumber
(plus any other data specific to each ticket)

This allows any given person to make more than one ticket purchase, and
on any given purchase occasion to purchase more than one ticket. I
imagine it would not be necessary for you to have a table for a master
list of all available ticket numbers.

--
Steve Schapel, Microsoft Access MVP


Al wrote:
I am trying to repeat data from one record into other records. For example,
I have created a table for ticket sales, where the ticket number is the
primary key. I would like to make it possible to enter information for a
person (name, phone, type of pmt, etc) for multiple tickets. That is, I want
to enter info for one person and have it autopopulate in other records
according to the other ticket numbers they purchased.
I am thinking I need to have a list of tickets and then another sublist for
the repeated tickets, but am not sure how to go about doing that.
Thanks!

Al,

In a database, the design of your tables is pretty much dictated by the
actual nature of the information you are trying to manage. In practice,
you really don't have much choice. Keeping the number of tables down
should never be a consideration, and equally the volume of data (e.g.
the number of shows) would seldom be relevant.

As for your forms for data entry, where there are one-to-many
relationships between data elements, as there are in your database (e.g.
each person can make 'many' purchases), it is usual to use a
form/subform structure to manage this. So, you might have a form based
on the People table, and another form for ticket purchases which is
placed on the People form as a subform. In this way, the tickets
purchased by any given person is shown on their record of the People
form. But anyway, we are jumping ahead... form design comes next, and
shouldn't really even be thought about until you have the tables set up
right.

So, obviously I don't know the details of your procedures. My
suggestion before assumed that you would be needing to record payments
for ticket purchases, but I don't know whether this is the case. I
don't know whether a given ticket number is exclusive to any given show,
or whether the price is the same for all tickets for the same show, etc.
Most likely you will need another table for Shows, and another table
again for ticket prices for each show. But whatever the case, the
general concept of what I am suggesting will certainly meet the
requirements that you mentioned, regarding tracking tickets with the
personal details of the purchaser... in fact, that's the whole purpose
of using a database like Access.

Steve Schapel, Microsoft Access MVP

Al wrote:
Thank you for the help!

A couple of follow ups:
Currently, I have one table for all the information from ticket #'s, name,
pymt type, etc. I do this to keep the number of tables down as we manage
tickets sales for several shows at once. (up to 15 shows at any given time)
From what you suggest, it looks like we should create more tables. Is there
a way to consolidate the info into one table, as we are trying to do, and
still be able to repeat information from one record to another?
Because we use just one table, I was thinking I could repeat data from
certain fields by way of a query. If a person purchases 4 tickets, each
ticket having a specific number that we need to track, I would like to be
able to enter the person's info (i.e. name, phone, etc) and fill out the
ticket number and price per each ticket they purchase. Each ticket must be
tracked separately for reporting purposes. The personal info of the
purchaser would then be copied to the multiple tickets, which would be new
records.
As you can tell, I am very new at this, and really appreciate all your help.
Now if I want to make a form to fill out info when a purchase is made, I
would have the fields on the table name, phone, etc, along with the ability
to enter ticket numbers and their prices, which would then be added to the
table, I imagine, by means of a query that would do as I mentioned above.
Does this sound feasible?

Just as a reference to what we are doing, here are some specifics:
-Each ticket must be tracked separately
-All information must be attached to a ticket number
-Persons who buy multiple tickets must have all personal info attached to
all their tickets

Thanks!

Al

Okay! Looks like it is back to the drawing board, but at least I know it is
possible and now have a direction to go. Thanks again!

Al

"Steve Schapel" wrote:

Al,

In a database, the design of your tables is pretty much dictated by the
actual nature of the information you are trying to manage. In practice,
you really don't have much choice. Keeping the number of tables down
should never be a consideration, and equally the volume of data (e.g.
the number of shows) would seldom be relevant.

As for your forms for data entry, where there are one-to-many
relationships between data elements, as there are in your database (e.g.
each person can make 'many' purchases), it is usual to use a
form/subform structure to manage this. So, you might have a form based
on the People table, and another form for ticket purchases which is
placed on the People form as a subform. In this way, the tickets
purchased by any given person is shown on their record of the People
form. But anyway, we are jumping ahead... form design comes next, and
shouldn't really even be thought about until you have the tables set up
right.

So, obviously I don't know the details of your procedures. My
suggestion before assumed that you would be needing to record payments
for ticket purchases, but I don't know whether this is the case. I
don't know whether a given ticket number is exclusive to any given show,
or whether the price is the same for all tickets for the same show, etc.
Most likely you will need another table for Shows, and another table
again for ticket prices for each show. But whatever the case, the
general concept of what I am suggesting will certainly meet the
requirements that you mentioned, regarding tracking tickets with the
personal details of the purchaser... in fact, that's the whole purpose
of using a database like Access.

Steve Schapel, Microsoft Access MVP

Al wrote:
Thank you for the help!

A couple of follow ups:
Currently, I have one table for all the information from ticket #'s, name,
pymt type, etc. I do this to keep the number of tables down as we manage
tickets sales for several shows at once. (up to 15 shows at any given time)
From what you suggest, it looks like we should create more tables. Is there
a way to consolidate the info into one table, as we are trying to do, and
still be able to repeat information from one record to another?
Because we use just one table, I was thinking I could repeat data from
certain fields by way of a query. If a person purchases 4 tickets, each
ticket having a specific number that we need to track, I would like to be
able to enter the person's info (i.e. name, phone, etc) and fill out the
ticket number and price per each ticket they purchase. Each ticket must be
tracked separately for reporting purposes. The personal info of the
purchaser would then be copied to the multiple tickets, which would be new
records.
As you can tell, I am very new at this, and really appreciate all your help.
Now if I want to make a form to fill out info when a purchase is made, I
would have the fields on the table name, phone, etc, along with the ability
to enter ticket numbers and their prices, which would then be added to the
table, I imagine, by means of a query that would do as I mentioned above.
Does this sound feasible?

Just as a reference to what we are doing, here are some specifics:
-Each ticket must be tracked separately
-All information must be attached to a ticket number
-Persons who buy multiple tickets must have all personal info attached to
all their tickets

Thanks!

Al