Appending new columns from one table into a static table

[NickG]

Hi there

I am fairly new at using access database's but i urgently need help with a
mitragration project which entails moving historical data stored in Excel
into an acess database.

My current hurdle is as follows; I have created a static table which needs
to be updated on a month a month basis. Essentially, i need to append two new
columns from the current months data table to the end of the static table.

The common field between the two tables is the "account number". I need to
perform the same action has it would in excel; first vlookup the account
number in the static table, if the account number matches, then populate the
current months data on the same row as the account number but under the two
new field names respresenting the current months date, else insert the
account number as a new record at the end of the table and polulate the data
under the new current months field as well.

I hope that came out clear. I would sincerely appreciate some feedback.

 

[KARL DEWEY]

i need to append two new columns from the current months data table to the
end of the static table.
First off you can not append columns. You append records.

You are trying to use Access as a spreadsheet. Change your structure to
where a field (not a column name identifes your monthly data) has information
defining the month.

I expect your data looks like this --
Item Jan09 Feb09 Mar09
A 3 1 4
B 0 5 2
C 6 3 1

It should be this --
Item Month QTY
A Jan09 3
A Feb09 1
A Mar09 4
B Jan09 0
B Feb09 5
B Mar09 2
C Jan09 6
C Feb09 3
C Mar09 1

You need to use a union query to revise your data and also to revise your
imports.
SELECT Item, #1/1/09# AS [Month], [Jan09] AS Quanity
FROM Your PresentTable
UNION ALL SELECT Item, #2/1/09# AS [Month], [Feb09] AS Quanity
FROM Your PresentTable
UNION ALL SELECT Item, #3/1/09# AS [Month], [Mar09] AS Quanity
FROM Your PresentTable;

 

[Larry Daugherty]

If you do what you currently plan you'll be "committing spreadsheet"
badly. You would not be taking advantage of the Relational power of
Access. It's also a faux pas to describe what you're doing or hope
to do in Excel or any other platform's terminology. Unless my memory
fails me completely, "vlookup" is from the dark side. Many, if not
most of the unpaid volunteers who respond in these Access newsgroups
do understand Excel but here we speak *Access", The whole idea is to
help a developer at any level of competence over a specific technical
hurdle. One technical issue per thread please.

Access is a RAD system with lots of development resources that assumes
the utilization of a Relational Database Management System. There has
always been the JET RDBMS freely available. With Access 2007 it is
still available as well as the ADE. For heavier duty work, any RDBMS
that complies with the ODBC standard can be used in the client/server
paradigm.. RDBMSs are modeled on Set theory, Relationships are
created and maintained between entities in your application.

With most "roll your own" Excel applications, data is entered directly
into cells and the user provide a whole lot of the intelligence
required to use the spreadsheet or workbook to store and manipulate
the data to achieve the desired result.

The scenario in Access is quite a bit different. The application is
designed, built and tested and thereafter, the user should never see
the tables and should *never, never, never* be required or allowed to
enter data directly in the tables. All interaction with the data is
by using the User Interface provided by the developer. The user will
interact with Forms that will provide the required functionality. The
major objects in the application are Tables, Queries, Forms and
Reports.

Below is a list of resources I've cribbed from MVP John Vinson. There
is no lack of resources for Access available on the net. These are
some of the best


Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

A couple of other Access newsgroups that are great for newbies are:

microsoft.public.access.gettingstarted
microsoft.public.access..tablesdesign.

You will also find groups for Forms, Queries and Reports.


HTH
--
-Larry-
--

Hi there

I am fairly new at using access database's but i urgently need help with a
mitragration project which entails moving historical data stored in Excel
into an acess database.

My current hurdle is as follows; I have created a static table which needs
to be updated on a month a month basis. Essentially, i need to append two new
columns from the current months data table to the end of the static table.

The common field between the two tables is the "account number". I need to
perform the same action has it would in excel; first vlookup the account
number in the static table, if the account number matches, then populate the
current months data on the same row as the account number but under the two
new field names respresenting the current months date, else insert the
account number as a new record at the end of the table and polulate the data
under the new current months field as well.

I hope that came out clear. I would sincerely appreciate some

feedback.

 

[NickG]

Thanks Carl, very interesting and i will be sure to give this a try

By the way ,I am trying to achieve the following and at this point, as I
work through the text book I am using to learn this application (Step by Step
- Access 2003); I am not sure if what i am expected to do is possibe. I will
provide more detail and perhaps you can share your thoughts;

ps: Being able to get this right will ensure the company i am temporarily
working for will make this a full time opportunity for me at the end of
july'09, I prmised to up to speed by end June , but this request has come
through and I required to produce this result asap;

1: I need to pull together historcial "Arrears data" currently on "Monthly"
excel spreadsheets in an access database.

2: Common to all the excel spreadsheets is the "Account Number". The other
two fields will be "Current Month - Total Payment in Arrears" & "Current
Month - Total Number of Payments in Arrears".

What needs to happen;

1: A table in an access database that will have the following information;
Account Number|Jan -pmt in arrears|Jan - No.of pmt in Arrears |Feb -pmt in
arrears|Feb - No.of pmt in Arrears|etc....

2: I then need to import the current months data (i.e Mar) and append the
data to the end of the "Static table - after Feb).

3: If there is a new account that has gone into arrears in Mar, then the
account number must be added as a new record and the values entered under the
Mar fields, alternatively it needs to just add March data to the already
existing account number if there is a match between the account numbers on
the "Static table" and "current months data" table.

4: The last action will be to produce from the Static table - named
historical arrears data,on a monthly basis, is another table that will
provide the following output;

From the entire Static table;
List all the accounts which will firstly compare all the monthly data on an
account by account basis and give the following

Accounts Number | Hightest Pmt in Arrears |Month this occured| Highest No.of
Pmt in arrears|Month this occured

I think this can be done using a Max() calculation?.

Please Karl, i would really appreciate your help ..
thanks man

end of the static table.
First off you can not append columns. You append records.

You are trying to use Access as a spreadsheet. Change your structure to
where a field (not a column name identifes your monthly data) has information
defining the month.

I expect your data looks like this --
Item Jan09 Feb09 Mar09
A 3 1 4
B 0 5 2
C 6 3 1

It should be this --
Item Month QTY
A Jan09 3
A Feb09 1
A Mar09 4
B Jan09 0
B Feb09 5
B Mar09 2
C Jan09 6
C Feb09 3
C Mar09 1

You need to use a union query to revise your data and also to revise your
imports.
SELECT Item, #1/1/09# AS [Month], [Jan09] AS Quanity
FROM Your PresentTable
UNION ALL SELECT Item, #2/1/09# AS [Month], [Feb09] AS Quanity
FROM Your PresentTable
UNION ALL SELECT Item, #3/1/09# AS [Month], [Mar09] AS Quanity
FROM Your PresentTable;

Hi there

I am fairly new at using access database's but i urgently need help with a
mitragration project which entails moving historical data stored in Excel
into an acess database.

My current hurdle is as follows; I have created a static table which needs
to be updated on a month a month basis. Essentially, i need to append two new
columns from the current months data table to the end of the static table.

The common field between the two tables is the "account number". I need to
perform the same action has it would in excel; first vlookup the account
number in the static table, if the account number matches, then populate the
current months data on the same row as the account number but under the two
new field names respresenting the current months date, else insert the
account number as a new record at the end of the table and polulate the data
under the new current months field as well.

I hope that came out clear. I would sincerely appreciate some feedback.

 

[KARL DEWEY]

Why are you insisting on doing it wrong? I told you what you need to do.
Larry also said you had it wrong.
The Max function will find the maximum value in a field - that is vertical.
You can not use Max to find a value in your data horizontally as you have.

Thanks Carl, very interesting and i will be sure to give this a try

By the way ,I am trying to achieve the following and at this point, as I
work through the text book I am using to learn this application (Step by Step
- Access 2003); I am not sure if what i am expected to do is possibe. I will
provide more detail and perhaps you can share your thoughts;

ps: Being able to get this right will ensure the company i am temporarily
working for will make this a full time opportunity for me at the end of
july'09, I prmised to up to speed by end June , but this request has come
through and I required to produce this result asap;

1: I need to pull together historcial "Arrears data" currently on "Monthly"
excel spreadsheets in an access database.

2: Common to all the excel spreadsheets is the "Account Number". The other
two fields will be "Current Month - Total Payment in Arrears" & "Current
Month - Total Number of Payments in Arrears".

What needs to happen;

1: A table in an access database that will have the following information;
Account Number|Jan -pmt in arrears|Jan - No.of pmt in Arrears |Feb -pmt in
arrears|Feb - No.of pmt in Arrears|etc....

2: I then need to import the current months data (i.e Mar) and append the
data to the end of the "Static table - after Feb).

3: If there is a new account that has gone into arrears in Mar, then the
account number must be added as a new record and the values entered under the
Mar fields, alternatively it needs to just add March data to the already
existing account number if there is a match between the account numbers on
the "Static table" and "current months data" table.

4: The last action will be to produce from the Static table - named
historical arrears data,on a monthly basis, is another table that will
provide the following output;

From the entire Static table;
List all the accounts which will firstly compare all the monthly data on an
account by account basis and give the following

Accounts Number | Hightest Pmt in Arrears |Month this occured| Highest No.of
Pmt in arrears|Month this occured

I think this can be done using a Max() calculation?.

Please Karl, i would really appreciate your help ..
thanks man

i need to append two new columns from the current months data table to the

end of the static table.
First off you can not append columns. You append records.

You are trying to use Access as a spreadsheet. Change your structure to
where a field (not a column name identifes your monthly data) has information
defining the month.

I expect your data looks like this --
Item Jan09 Feb09 Mar09
A 3 1 4
B 0 5 2
C 6 3 1

It should be this --
Item Month QTY
A Jan09 3
A Feb09 1
A Mar09 4
B Jan09 0
B Feb09 5
B Mar09 2
C Jan09 6
C Feb09 3
C Mar09 1

You need to use a union query to revise your data and also to revise your
imports.
SELECT Item, #1/1/09# AS [Month], [Jan09] AS Quanity
FROM Your PresentTable
UNION ALL SELECT Item, #2/1/09# AS [Month], [Feb09] AS Quanity
FROM Your PresentTable
UNION ALL SELECT Item, #3/1/09# AS [Month], [Mar09] AS Quanity
FROM Your PresentTable;

Hi there

I am fairly new at using access database's but i urgently need help with a
mitragration project which entails moving historical data stored in Excel
into an acess database.

My current hurdle is as follows; I have created a static table which needs
to be updated on a month a month basis. Essentially, i need to append two new
columns from the current months data table to the end of the static table.

The common field between the two tables is the "account number". I need to
perform the same action has it would in excel; first vlookup the account
number in the static table, if the account number matches, then populate the
current months data on the same row as the account number but under the two
new field names respresenting the current months date, else insert the
account number as a new record at the end of the table and polulate the data
under the new current months field as well.

I hope that came out clear. I would sincerely appreciate some feedback.

 

[John W. Vinson]

ps: Being able to get this right will ensure the company i am temporarily
working for will make this a full time opportunity for me at the end of
july'09, I prmised to up to speed by end June , but this request has come
through and I required to produce this result asap;

Then I suggest you work WITH Access, as Karl is suggesting, rather than
struggling AGAINST Access by treating it as if it were a spreadsheet with
different columns for different repeating values.

Karl is right. *You are wrong* with you design.

Reread his suggestions. If your job is on the line with this app, *you will
lose your job* if you keep on your current course.

Sorry to be brutal but it will be MUCH easier to do the job right than to do
it wrong and struggle with it.