How To Make a Form Update a Table

[R Tanner]

Hi,

I have a form with two features right now. There is a combo box that
is linked to a parent table called Issues and another field labeled
start time, linked to a table called Tickets (Tickets is a child table
of Issues).

How do I make the information on the form update the table? Should I
put a submit button on it or something? Also, is there anything I can
read that will tell me how to create a 'front end' and a 'back end'?
I'm not sure how to do that either. I want to distribute the front
end to about 7 different people so they can update the back end.

Thank you for your help...

 

[Jeff Boyce]

If your form refers to two separate tables, how does Access know which
table's field(s) are to be updated?

It all starts with the data, so please provide more information about the
underlying data, and not just the form you've created.

On the topic of splitting (front-end, back-end), there's a wizard to help
with that. But the concept is quite simple -- all the data /tables go in
one .mdb file, while everything else goes in another. In that second
(data-less) .mdb file, all the tables in the first are connected by
"linking" to them. Put the data .mdb out on your LAN where all can reach it
(and all have read-write permissions on the folder/path), and put a copy of
the front-end (linked) on each user's desktop/PC.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[R Tanner]

If your form refers to two separate tables, how does Access know which
table's field(s) are to be updated?

It all starts with the data, so please provide more information about the
underlying data, and not just the form you've created.

On the topic of splitting (front-end, back-end), there's a wizard to help
with that.  But the concept is quite simple -- all the data /tables go in
one .mdb file, while everything else goes in another.  In that second
(data-less) .mdb file, all the tables in the first are connected by
"linking" to them.  Put the data .mdb out on your LAN where all can reach it
(and all have read-write permissions on the folder/path), and put a copy of
the front-end (linked) on each user's desktop/PC.

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

Okay I will worry about the front end vs. the back end after I get
everything else taken care of. I have alot of other things to do
first, as you can tell. So the data. The data is very much a mess.
I imported about 25 different spreadsheets with 150-200 records on
each spreadsheet into excel. The data was in different formats, some
critical fields in each record were missing or misspelled, and I have
been trying to cleanse it and make a functional database out of it,
despite my utter lack of knowledge of databases ( I was hoping I could
learn ) . The data is right now in one big table called Tickets.
In this Tickets table, I have a series of drop down fields with
specific options to choose from. These are the drop down fields:

1. Issue
2. System Adjustment
3. Manager
4. Resolved By
5. Satisfied
6. Accountability
7. Supervisor Initials

After these fields, I have the following:

1. Start Time
2. End Time
3. Description
4. If Other (This goes with Issue - There is an 'Other' option)
5. Credit
6. Adjusted Price
7. Account
8. Account
9. Original Details
10. Resolution

Now what I want to do is have this data stored in the back end and
have a front end for each specialist to enter new records into. To
organize the data and maintain the integrity of it from here on out, I
am under the impression I should have parent tables. With that
understanding, I wanted to split up all of the drop down fields into
other tables.

It is also important to know that, although for the drop down field
'Issue' listed above there are only currently about 10 - 15 choices
for specialists to choose from, there have been a total of 670
different entries in this field over the past 2 years. What I did
with this was I split the tickets table and I have a relationship
between it and my issues table with a boolean column I labeled current
in the issues table. If the issue is currently one we use, then the
box is checked.

I feel like I am trying to carve a diamond out of the rock with
this...lol

 

[Golfinray]

You have a major problem. Importing spreadsheets is not half the nightmare
that you are going to have with one big table. Data should be "normalized",
that is broken down where each piece is only stored once and it is related to
each other through keys. Then you can query your data, then build the forms
and reports you need. There is a table analyzer in Access that will help you
break your tables down the way they should be. Good luck!

 

[Jeff Boyce]

Hold on a second...

If you simply imported data from Excel into Access, odds are very good that
your tables are not well-normalized (Excel data is spreadsheet data, is
rarely well-normalized due to the structural design of spreadsheets).

Before you try anything else, can you confirm whether your data tables are
well-normalized?

If "relational" and "normalization" are not familiar terms, plan on taking
the time to brush up on them before trying to make Access behave like a
spreadsheet. "Committing spreadsheet" on Access will only give you (and
Access) a headache!

Regards

Jeff Boyce
Microsoft Office/Access MVP

If your form refers to two separate tables, how does Access know which
table's field(s) are to be updated?

It all starts with the data, so please provide more information about the
underlying data, and not just the form you've created.

On the topic of splitting (front-end, back-end), there's a wizard to help
with that. But the concept is quite simple -- all the data /tables go in
one .mdb file, while everything else goes in another. In that second
(data-less) .mdb file, all the tables in the first are connected by
"linking" to them. Put the data .mdb out on your LAN where all can reach
it
(and all have read-write permissions on the folder/path), and put a copy
of
the front-end (linked) on each user's desktop/PC.

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

Okay I will worry about the front end vs. the back end after I get
everything else taken care of. I have alot of other things to do
first, as you can tell. So the data. The data is very much a mess.
I imported about 25 different spreadsheets with 150-200 records on
each spreadsheet into excel. The data was in different formats, some
critical fields in each record were missing or misspelled, and I have
been trying to cleanse it and make a functional database out of it,
despite my utter lack of knowledge of databases ( I was hoping I could
learn ) . The data is right now in one big table called Tickets.
In this Tickets table, I have a series of drop down fields with
specific options to choose from. These are the drop down fields:

1. Issue
2. System Adjustment
3. Manager
4. Resolved By
5. Satisfied
6. Accountability
7. Supervisor Initials

After these fields, I have the following:

1. Start Time
2. End Time
3. Description
4. If Other (This goes with Issue - There is an 'Other' option)
5. Credit
6. Adjusted Price
7. Account
8. Account
9. Original Details
10. Resolution

Now what I want to do is have this data stored in the back end and
have a front end for each specialist to enter new records into. To
organize the data and maintain the integrity of it from here on out, I
am under the impression I should have parent tables. With that
understanding, I wanted to split up all of the drop down fields into
other tables.

It is also important to know that, although for the drop down field
'Issue' listed above there are only currently about 10 - 15 choices
for specialists to choose from, there have been a total of 670
different entries in this field over the past 2 years. What I did
with this was I split the tickets table and I have a relationship
between it and my issues table with a boolean column I labeled current
in the issues table. If the issue is currently one we use, then the
box is checked.

I feel like I am trying to carve a diamond out of the rock with
this...lol

 

[R Tanner]

Hold on a second...

If you simply imported data from Excel into Access, odds are very good that
your tables are not well-normalized (Excel data is spreadsheet data, is
rarely well-normalized due to the structural design of spreadsheets).

Before you try anything else, can you confirm whether your data tables are
well-normalized?

If "relational" and "normalization" are not familiar terms, plan on taking
the time to brush up on them before trying to make Access behave like a
spreadsheet.  "Committing spreadsheet" on Access will only give you (and
Access) a headache!

Regards

Jeff Boyce
Microsoft Office/Access MVP








Okay I will worry about the front end vs. the back end after I get
everything else taken care of.  I have alot of other things to do
first, as you can tell.  So the data.  The data is very much a mess.
I imported about 25 different spreadsheets with 150-200 records on
each spreadsheet into excel.  The data was in different formats, some
critical fields in each record were missing or misspelled, and I have
been trying to cleanse it and make a functional database out of it,
despite my utter lack of knowledge of databases ( I was hoping I could
learn ) .  The data is right now in one big table called Tickets.
In this Tickets table, I have a series of drop down fields with
specific options to choose from.  These are the drop down fields:

1.  Issue
2. System Adjustment
3. Manager
4. Resolved By
5. Satisfied
6. Accountability
7. Supervisor Initials

After these fields, I have the following:

1. Start Time
2. End Time
3. Description
4. If Other (This goes with Issue - There is an 'Other' option)
5. Credit
6. Adjusted Price
7. Account
8. Account
9. Original Details
10. Resolution

Now what I want to do is have this data stored in the back end and
have a front end for each specialist to enter new records into.  To
organize the data and maintain the integrity of it from here on out, I
am under the impression I should have parent tables.  With that
understanding, I wanted to split up all of the drop down fields into
other tables.

It is also important to know that, although for the drop down field
'Issue' listed above there are only currently about 10 - 15 choices
for specialists to choose from, there have been a total of 670
different entries in this field over the past 2 years.  What I did
with this was I split the tickets table and I have a relationship
between it and my issues table with a boolean column I labeled current
in the issues table.  If the issue is currently one we use, then the
box is checked.

I feel like I am trying to carve a diamond out of the rock with
this...lol- Hide quoted text -

- Show quoted text -

It is not normalized. I have tried to normalize it by splitting it
into child tables with one to many relationships but it doesn't work.
It tells me that I need to increase MaxLocksPerFile in the registry.
If there is any other way to do this however, I would like to try
that. Editing the registry is going to be hard for me to get IT to
let me do...

 

[Jeff Boyce]

It's hard to offer specific suggestions to the general information provided.

"Splitting input data into child tables" is something that folks do all the
time. But different folks may use different definitions for this.

HOW you are doing this might be affecting the messages you're receiving.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hold on a second...

If you simply imported data from Excel into Access, odds are very good
that
your tables are not well-normalized (Excel data is spreadsheet data, is
rarely well-normalized due to the structural design of spreadsheets).

Before you try anything else, can you confirm whether your data tables are
well-normalized?

If "relational" and "normalization" are not familiar terms, plan on taking
the time to brush up on them before trying to make Access behave like a
spreadsheet. "Committing spreadsheet" on Access will only give you (and
Access) a headache!

Regards

Jeff Boyce
Microsoft Office/Access MVP








Okay I will worry about the front end vs. the back end after I get
everything else taken care of. I have alot of other things to do
first, as you can tell. So the data. The data is very much a mess.
I imported about 25 different spreadsheets with 150-200 records on
each spreadsheet into excel. The data was in different formats, some
critical fields in each record were missing or misspelled, and I have
been trying to cleanse it and make a functional database out of it,
despite my utter lack of knowledge of databases ( I was hoping I could
learn ) . The data is right now in one big table called Tickets.
In this Tickets table, I have a series of drop down fields with
specific options to choose from. These are the drop down fields:

1. Issue
2. System Adjustment
3. Manager
4. Resolved By
5. Satisfied
6. Accountability
7. Supervisor Initials

After these fields, I have the following:

1. Start Time
2. End Time
3. Description
4. If Other (This goes with Issue - There is an 'Other' option)
5. Credit
6. Adjusted Price
7. Account
8. Account
9. Original Details
10. Resolution

Now what I want to do is have this data stored in the back end and
have a front end for each specialist to enter new records into. To
organize the data and maintain the integrity of it from here on out, I
am under the impression I should have parent tables. With that
understanding, I wanted to split up all of the drop down fields into
other tables.

It is also important to know that, although for the drop down field
'Issue' listed above there are only currently about 10 - 15 choices
for specialists to choose from, there have been a total of 670
different entries in this field over the past 2 years. What I did
with this was I split the tickets table and I have a relationship
between it and my issues table with a boolean column I labeled current
in the issues table. If the issue is currently one we use, then the
box is checked.

I feel like I am trying to carve a diamond out of the rock with
this...lol- Hide quoted text -

- Show quoted text -

It is not normalized. I have tried to normalize it by splitting it
into child tables with one to many relationships but it doesn't work.
It tells me that I need to increase MaxLocksPerFile in the registry.
If there is any other way to do this however, I would like to try
that. Editing the registry is going to be hard for me to get IT to
let me do...

 

[R Tanner]

It's hard to offer specific suggestions to the general information provided.

"Splitting input data into child tables" is something that folks do all the
time.  But different folks may use different definitions for this.

HOW you are doing this might be affecting the messages you're receiving.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

















It is not normalized.  I have tried to normalize it by splitting it
into child tables with one to many relationships but it doesn't work.
It tells me that I need to increase MaxLocksPerFile in the registry.
If there is any other way to do this however, I would like to try
that.  Editing the registry is going to be hard for me to get IT to
let me do...- Hide quoted text -

- Show quoted text -

The only way I know to split it is through the Table Analyzer. I then
manually make the different fields their own tables and define the
relationships by going through the Wizard. The only two ways I know
to split a table are by using the table analyzer or running a totals
query then a make table query...

 

[R Tanner]

It's hard to offer specific suggestions to the general information provided.

"Splitting input data into child tables" is something that folks do all the
time.  But different folks may use different definitions for this.

HOW you are doing this might be affecting the messages you're receiving.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

















It is not normalized.  I have tried to normalize it by splitting it
into child tables with one to many relationships but it doesn't work.
It tells me that I need to increase MaxLocksPerFile in the registry.
If there is any other way to do this however, I would like to try
that.  Editing the registry is going to be hard for me to get IT to
let me do...- Hide quoted text -

- Show quoted text -

I tried splitting my tickets table into 5 other tables and it tells me
I have to increase my MaxLocksPerFile. I don't get it. 5 tables
doesn't seem like that much.

 

[R Tanner]

I tried splitting my tickets table into 5 other tables and it tells me
I have to increase my MaxLocksPerFile.  I don't get it.  5 tables
doesn't seem like that much.- Hide quoted text -

- Show quoted text -

Okay I figured out how to create parent/child table relationships and
essentially normalize my data by using queries and then editing the
relationships...This should work to accomplish what I want it to,
shouldn't it?

 

[Jeff Boyce]

I'm still having trouble visualizing the domain you're working in.

You've stated "there, I did something, is it right?", and I have no way to
assess.

Perhaps one of the other newsgroup readers has more insight...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I tried splitting my tickets table into 5 other tables and it tells me
I have to increase my MaxLocksPerFile. I don't get it. 5 tables
doesn't seem like that much.- Hide quoted text -

- Show quoted text -

Okay I figured out how to create parent/child table relationships and
essentially normalize my data by using queries and then editing the
relationships...This should work to accomplish what I want it to,
shouldn't it?