Duplicate text box entries into multiple records on same form

[dbnut]

• I am using Access Trial 2007 in 2003 Mode.

• I designed a Form to track what tickets still need to be issued
using a split form:
________________________________________

Ticket 14 First --------- Last ----------- Issued (n)
__________________________________________
Ticket # ---First----Last-----Issued
14 ----------|--------|----------|----- (n)
15 ----------|--------|----------|----- (n)
16 ----------|--------|----------|----- (n)
17 ----------|--------|----------|----- (n)
34 ----------|--------|----------|----- (n)
35 ----------|--------|----------|----- (n)

• I can issue up to 150 tickets to the same person – so how do I keep the person’s first and last name I input into record 14 to repeat itself when I go to ticket # 15?
• I am sure it is better to write a batch program (?) that allows me to choose tickets 10 – 110 and issue them to John Doe and all the fields are updated automatically but I am long way from understanding that.
• I used to program in DBASE III – many moons ago – this object based stuff has taken this old man back to square 1. I do not mind going from record to record and updating the logic field (Y/N) but having to type the same name in over and over does get old.
--- I spent about 5 hours looking over some of the discussions and tried some of them for fun - I never got anything to work as I am not sure where the code goes. When at the MS site they tell you to were to insert it (be nice). I need that also. It has been at least 25 years since I actually coded anything.

[ADezii]

• I am using Access Trial 2007 in 2003 Mode.

• I designed a Form to track what tickets still need to be issued
using a split form:
________________________________________

Ticket 14 First --------- Last ----------- Issued (n)
__________________________________________
Ticket # ---First----Last-----Issued
14 ----------|--------|----------|----- (n)
15 ----------|--------|----------|----- (n)
16 ----------|--------|----------|----- (n)
17 ----------|--------|----------|----- (n)
34 ----------|--------|----------|----- (n)
35 ----------|--------|----------|----- (n)

• I can issue up to 150 tickets to the same person – so how do I keep the person’s first and last name I input into record 14 to repeat itself when I go to ticket # 15?
• I am sure it is better to write a batch program (?) that allows me to choose tickets 10 – 110 and issue them to John Doe and all the fields are updated automatically but I am long way from understanding that.
• I used to program in DBASE III – many moons ago – this object based stuff has taken this old man back to square 1. I do not mind going from record to record and updating the logic field (Y/N) but having to type the same name in over and over does get old.
--- I spent about 5 hours looking over some of the discussions and tried some of them for fun - I never got anything to work as I am not sure where the code goes. When at the MS site they tell you to were to insert it (be nice). I need that also. It has been at least 25 years since I actually coded anything.

You're thinking of the old flatfile architecture of DBASE III and trying to use it within a Relational Database Management System such as Access. You would have no need to duplicate the individual's First and Last Name in successive Records for Ticket purchases. You would have a Parent Table called whatever you like, say tblMember. Within this Table you would have at a minimum 3 Fields: First Name, Last Name, and a unique identifier. For the sake of argument, we can use a person's Social Security Account Number (SSAN). The Child Table, say tblTickets, would also contain at a minimum the Member's SSAN, and all Ticket related information such as: Ticket#, Section, Box, Seat, etc. You would then establish a 1 to Many Relationship between tblMember and tblTickets via the SSAN Field. This Relational structure would eliminate the necessity of entering an individual's First and Last Name more than once and help to ensure data integrity. Hope this helps. If you need further assistance, please let us know.

[MMcCarthy]

Check out this tutorial on Normalisation and Table Structures from our tutorial section. It should help you get started.

Mary

[dbnut]

You're thinking of the old flatfile architecture of DBASE III and trying to use it within a Relational Database Management System such as Access. You would have no need to duplicate the individual's First and Last Name in successive Records for Ticket purchases. You would have a Parent Table called whatever you like, say tblMember. Within this Table you would have at a minimum 3 Fields: First Name, Last Name, and a unique identifier. For the sake of argument, we can use a person's Social Security Account Number (SSAN). The Child Table, say tblTickets, would also contain at a minimum the Member's SSAN, and all Ticket related information such as: Ticket#, Section, Box, Seat, etc. You would then establish a 1 to Many Relationship between tblMember and tblTickets via the SSAN Field. This Relational structure would eliminate the necessity of entering an individual's First and Last Name more than once and help to ensure data integrity. Hope this helps. If you need further assistance, please let us know.

I do have two child tables as described but linked them by ticket #

1. Issued Table: ticket # , FirstName , LastName
2. Sold Table: ticket # , SFirstName , SLast Name

My Parent ticket table: Ticket #, issued (y/n), sold (y/n), deposited (y/n).

I am sure that I do not need both child tables if I understand the logic.

The idea is to track who I issued tickets to - who the ultimate buyer is - and make sure the deposits match sales. I have a set number of tickets = 1200.

Now to make the quantum leap from the earth is flat to round. I will redesign the project - two tables as described - I will add a SSAN (I will use the record number) I do understand the one to many concept and played with that but it is in the data input where I lose it. But I refuse to give up - If I can learn how to program in COBOL and FORTRAN using punch cards then I can do this. It seems so simple - maybe I can catch my 10 yr old grandson and have him show me. Thanks

[MMcCarthy]

I do have two child tables as described but linked them by ticket #

1. Issued Table: ticket # , FirstName , LastName
2. Sold Table: ticket # , SFirstName , SLast Name

My Parent ticket table: Ticket #, issued (y/n), sold (y/n), deposited (y/n).

I am sure that I do not need both child tables if I understand the logic.

The idea is to track who I issued tickets to - who the ultimate buyer is - and make sure the deposits match sales. I have a set number of tickets = 1200.

Now to make the quantum leap from the earth is flat to round. I will redesign the project - two tables as described - I will add a SSAN (I will use the record number) I do understand the one to many concept and played with that but it is in the data input where I lose it. But I refuse to give up - If I can learn how to program in COBOL and FORTRAN using punch cards then I can do this. It seems so simple - maybe I can catch my 10 yr old grandson and have him show me. Thanks

You have a ticket table with ticket no. etc.

If you wish to record the sale and issue of each ticket individually you could theoretically do it in one table as the relationhip would be one to one. One ticket #1, Ticket #1 sold, Ticket #1 issued. I have used DateOfSale/DateOfIssue rather than yes/no as it is a better record. If DateOfSale is null then the ticket has not been sold.

tblTicket
TicketNo (Primary Key)
DateOfSale
BuyersName
DateOfIssue
IssuedTo

tblDeposits
DepositID (Primary Key - AutoNumber)
DepositDate
DepositAmount

Unless there is one deposit per ticket you could work out the deposits per ticket as follows: Assuming tickets are 10 Euro (STG or Dollars) each.

SELECT Count(TicketNo) As TicketsSold, Sum(DepositAmount) As Deposited, TicketsSold * 10 As Sales
FROM tblTickets, tblDeposits
WHERE DateOfSale Is Not Null;

Mary

[NeoPa]

Be assured that Access can handle your situation.
An earlier poster pointed you to the Normalisation and Table Structures thread. You could do a lot worse than studying that and getting the fundamental concept inside and settled down (if you haven't already). Not all the complexities are required but the fundamental concepts can really help you understand a situation like this. It's really important because it helps you to work with Access rather than around it.
I think it's safe to say, we like your attitude. You've done as much of the homework as possible before requesting help. With material like that I'm sure we can make an Access database designer of you yet :)

When you get to the stage of designing the forms, we can help there too. There are all sorts of ways to handle situations similar to those you describe. We just need to understand the details properly to guide you in the right direction.

[dbnut]

Be assured that Access can handle your situation.
An earlier poster pointed you to the Normalisation and Table Structures thread. You could do a lot worse than studying that and getting the fundamental concept inside and settled down (if you haven't already). Not all the complexities are required but the fundamental concepts can really help you understand a situation like this. It's really important because it helps you to work with Access rather than around it.
I think it's safe to say, we like your attitude. You've done as much of the homework as possible before requesting help. With material like that I'm sure we can make an Access database designer of you yet :)

When you get to the stage of designing the forms, we can help there too. There are all sorts of ways to handle situations similar to those you describe. We just need to understand the details properly to guide you in the right direction.

I do want to thank all of you for your help.

Mary's hint about using dates is very interesting. It solves the distribution problem I have yet had time to consider.

I have already built the two tables as ADezii suggested using SSAN. Wrote about 30 quarries and as many forms trying to figure out how to post the data base - once I gave up on building everything around the tblTickets table and starting focusing around the tblMember table I am finally starting to get some interesting results.

I really appreciate the encouraging words – I needed that as I really hate to bother anyone with my problems.

[MMcCarthy]

I really appreciate the encouraging words – I needed that as I really hate to bother anyone with my problems.

Always remember, nobody starts out knowing everything. We all had to start somewhere so never worry about asking for help. That's what we're here for and it's always a pleasure to help someone who appreciates it.

Mary