Prev: Having problems with Combo Box (Form Control)
Next: String formatting question
From: forest8 on 16 Jan 2010 01:29
Since I started this, I haven't attempted any queries since I don't fully
understand the functionality. I am going to divide my table into 2 sections.
Hopefully the append query will be easy for my to use to make sure that the 2
tables are connected.

Thank you for all your help with this question as well as other questions
you've been kind to answer.

Forest

"John W. Vinson" wrote:

> On Fri, 15 Jan 2010 20:37:04 -0800, forest8
> <forest8(a)discussions.microsoft.com> wrote:
>
> >The original data was in the SPSS format. I inherited the database from
> >someone who had imported the SPSS tables into ACCESS.
> >
> >I can try to normalize the data but that may take more time than I can
> >afford at the moment. So I'm trying to make the most out of the situation.
>
> Were you able to restructure the table?
>
> The alternative would be to create a new (250 or so) field table, manually;
> and run an Append query to migrate the data. Access will convert most datatype
> changes, you might need some expressions or code to do it in some cases.
>
> And you have my sympathy... I used to work with a lot of SAS users...!
> --
>
> John W. Vinson [MVP]
> .
>
From: forest8 on 16 Jan 2010 12:12
Hi again

Since my last post, I attempted an Append Query but was not very successful.
Instead of appending columns I appended rows.

I normalized my table into 6 separate tables for each category. There are 2
unique keys in each table. The first is an autonumber for each table (i.e.,
Part1_ID, Part2_ID, Part3_ID, etc.) . I made the StudentID also unique (and
it appears in every table). There is no primary key identified.

My question here is: when people are adding data to each table do I have to
run an append each time?

If I am going off in a tangent, how do I get back on track?

Thank you.
Forest



"forest8" wrote:

> Since I started this, I haven't attempted any queries since I don't fully
> understand the functionality. I am going to divide my table into 2 sections.
> Hopefully the append query will be easy for my to use to make sure that the 2
> tables are connected.
>
> Thank you for all your help with this question as well as other questions
> you've been kind to answer.
>
> Forest
>
> "John W. Vinson" wrote:
>
> > On Fri, 15 Jan 2010 20:37:04 -0800, forest8
> > <forest8(a)discussions.microsoft.com> wrote:
> >
> > >The original data was in the SPSS format. I inherited the database from
> > >someone who had imported the SPSS tables into ACCESS.
> > >
> > >I can try to normalize the data but that may take more time than I can
> > >afford at the moment. So I'm trying to make the most out of the situation.
> >
> > Were you able to restructure the table?
> >
> > The alternative would be to create a new (250 or so) field table, manually;
> > and run an Append query to migrate the data. Access will convert most datatype
> > changes, you might need some expressions or code to do it in some cases.
> >
> > And you have my sympathy... I used to work with a lot of SAS users...!
> > --
> >
> > John W. Vinson [MVP]
> > .
> >
From: John W. Vinson on 16 Jan 2010 14:19
On Sat, 16 Jan 2010 09:12:01 -0800, forest8
<forest8(a)discussions.microsoft.com> wrote:

>Hi again
>
>Since my last post, I attempted an Append Query but was not very successful.
> Instead of appending columns I appended rows.

Well, it sounds like you succeeded perfectly, in that this is exactly what
Append queries are SUPPOSED to do: add new records, not new fields.

>I normalized my table into 6 separate tables for each category. There are 2
>unique keys in each table. The first is an autonumber for each table (i.e.,
>Part1_ID, Part2_ID, Part3_ID, etc.) . I made the StudentID also unique (and
>it appears in every table). There is no primary key identified.

Sorry... but that's DEnormalizing, not normalizing.

If you're going to do this, I'd delete the autonumber; there is no point to
having two different unique identifiers. If each student will have one, and
only one, ever, record in each of these tables you can make StudentID the
Primary Key and define a one-to-one relationship between the Students table
and this table.

>My question here is: when people are adding data to each table do I have to
>run an append each time?

It's not at all clear to me what you're appending from. Generally, no - if
users enter data into a table, then the data *is in the table* and you don't
need a separate operation to add it! Only if the users enter data into one
table, and it needs to end up in a different table, will you need an Append
query.

>If I am going off in a tangent, how do I get back on track?

If you have consciously chosen to reject the "tall thin" structure - with one
ROW per answer, not one FIELD per answer - described in At Your Survey, then
I'm afraid I can't be much help. Perhaps you could post an example of your
current table structure.
--

John W. Vinson [MVP]
From: Arvin Meyer [MVP] on 17 Jan 2010 06:34
"forest8" <forest8(a)discussions.microsoft.com> wrote in message
news:D58DDD88-93CB-459C-8E02-CAE7FEBE39B7(a)microsoft.com...
> The original data was in the SPSS format. I inherited the database from
> someone who had imported the SPSS tables into ACCESS.
>
> I can try to normalize the data but that may take more time than I can
> afford at the moment. So I'm trying to make the most out of the situation.

As John mentioned, you really should re-design your structure. The only
other possibility that I can think of is to add a second table, and use the
same primary key field for the additional columns. That will create a 1 to 1
relationship between the tables. It is really poor design though and you
will most certainly have problems with queries, reports and forms. It's
probably much more time efficient in the long run to bite the bullet and fix
it now.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


From: Jeff Boyce on 19 Jan 2010 10:52
As John & Arvin have pointed out, you can "pay now" (spend the time to learn
normalization and normalize your data structure) or "pay later" (keep
struggling with how to get Access to use 'sheet data, and modifying your
structure and queries and forms and ... everytime you change questions).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"forest8" <forest8(a)discussions.microsoft.com> wrote in message
news:EC4D0A43-3DA2-4331-BF39-0C6A8F1D5B9A(a)microsoft.com...
> Hi there
>
> I have an access table that has about 255 columns.
>
> Several fields need to be adjusted to a Yes/No instead of its current
> setting of text.
>
> When I try to change it, I get a "Too many fields defined" message that
> pops
> up.
>
> Followed by "Errors were encountered during the save operation. Data types
> were not changed. Properties were not updated."
>
> What is the best way to resolve this?
>
> Thank you in advance.
>
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Having problems with Combo Box (Form Control)
Next: String formatting question