How can i create queries/searches for information in the following format:

Name: Age: Jan-05: Feb-05: March-05:
John 15 37 40 40
Jane 17 35 35 35
Dan 18 40 42 45

Where the months represent salary, for example.
My issue is that the months are part of one row...i cannot seem to search
for it in Access via query by, say, entering Name, Age, and Month.
In Excel i was able to concatenate Name & Age, and then run an "Index"
function where you would search the following: Index(John15, Jan-05) = 37

But i cannot seem to do the same in Access...mind you there are really 1000s
of cells, and therefore need to use Access, but cannot create another bunch
of columns for the months b/c there'd be millions of cells.

Any help would be greatly appreciate!

Well, first, I would not store AGE. That is a moving target. My age today
is different than it will be in a few months. The normal formula for
obtaining a person's age as of the current date is..
DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(),"mmdd"))


Secondly, you should not store data in a field name. "Jan-05" is not a
field name. You are trying to create a spreadsheet in Access. Access is a
database application, not a spreadsheet. Your data needs to be stored in at
least two tables. For example...


TblEmployee
EmpNumber
EmpFirstName
EmpLastName
EmpBirthDate

TblSalaryHistory
EmpNumber
SalaryEffDate
SalaryAmount


In your example, each employee would have one record in the main table and
several records in the Salary History table.

Once you get your structure right, then you can start thinking about how to
get the data you need. Please note "cell" is an Excel term. There are no
"cells" in Access.



--
Rick B



"Dan" <(E-Mail Removed)> wrote in message
news:172E4912-A3D3-4E26-B1EF-(E-Mail Removed)...
> How can i create queries/searches for information in the following format:
>
> Name: Age: Jan-05: Feb-05: March-05:
> John 15 37 40 40
> Jane 17 35 35 35
> Dan 18 40 42 45
>
> Where the months represent salary, for example.
> My issue is that the months are part of one row...i cannot seem to search
> for it in Access via query by, say, entering Name, Age, and Month.
> In Excel i was able to concatenate Name & Age, and then run an "Index"
> function where you would search the following: Index(John15, Jan-05) = 37
>
> But i cannot seem to do the same in Access...mind you there are really
> 1000s
> of cells, and therefore need to use Access, but cannot create another
> bunch
> of columns for the months b/c there'd be millions of cells.
>
> Any help would be greatly appreciate!

Hi Dan,

It's because your table isn't set up properly. You should have one table
with the Person's information. Also it should not have Age; rather you should
have date of birth DOB and calculate the age as needed. This table should
have a primary key like maybe an EmployeeNumber or, IMHO, just an autonumber.

In a second table you would have another primary key (autonumber), a foriegn
key field to link to the Person table, a date field and Salary field. Each
bit of data that you now have in a "cell" would be an individual record in
this table. Then you could do a query by Person, Age (calculated), and month
to find the information.

You mentioned Excel and said "cell". You are doing what is known as
"committing spreadsheet" which is a grevious crime punishable by lots of
confusion when using a database. I suggest reading Database Design for Mere
Mortals by Hernandez or some other primer on relational databases before
going much further.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Dan" wrote:

> How can i create queries/searches for information in the following format:
>
> Name: Age: Jan-05: Feb-05: March-05:
> John 15 37 40 40
> Jane 17 35 35 35
> Dan 18 40 42 45
>
> Where the months represent salary, for example.
> My issue is that the months are part of one row...i cannot seem to search
> for it in Access via query by, say, entering Name, Age, and Month.
> In Excel i was able to concatenate Name & Age, and then run an "Index"
> function where you would search the following: Index(John15, Jan-05) = 37
>
> But i cannot seem to do the same in Access...mind you there are really 1000s
> of cells, and therefore need to use Access, but cannot create another bunch
> of columns for the months b/c there'd be millions of cells.
>
> Any help would be greatly appreciate!

To add to Rick's answer, here is a link to a previous thread on a similar
subject. To read the initial question, you may have to click on a link that
reads "Show quoted text":

http://groups.google.com/group/micro...4e76c6371e08fa

I offered a Union Query solution. Access MVP John Vinson offers a normal
SELECT query solution, where one must "put the search criterion under each of
the three
fields, on SEPARATE LINES of the query grid".

Here are some links to database design articles that will help you work
through
this issue:

http://www.datatexcg.com/Downloads/D...gnTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533

Don't underestimate the importance of gaining a good understanding of
database design. Brew a good pot of tea or coffee and enjoy reading!


Tom

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


"Rick B" wrote:

> Well, first, I would not store AGE. That is a moving target. My age today
> is different than it will be in a few months. The normal formula for
> obtaining a person's age as of the current date is..
> DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(),"mmdd"))
>
>
> Secondly, you should not store data in a field name. "Jan-05" is not a
> field name. You are trying to create a spreadsheet in Access. Access is a
> database application, not a spreadsheet. Your data needs to be stored in at
> least two tables. For example...
>
>
> TblEmployee
> EmpNumber
> EmpFirstName
> EmpLastName
> EmpBirthDate
>
> TblSalaryHistory
> EmpNumber
> SalaryEffDate
> SalaryAmount
>
>
> In your example, each employee would have one record in the main table and
> several records in the Salary History table.
>
> Once you get your structure right, then you can start thinking about how to
> get the data you need. Please note "cell" is an Excel term. There are no
> "cells" in Access.
>
>
>
> --
> Rick B
>
>
>
> "Dan" <(E-Mail Removed)> wrote in message
> news:172E4912-A3D3-4E26-B1EF-(E-Mail Removed)...
> > How can i create queries/searches for information in the following format:
> >
> > Name: Age: Jan-05: Feb-05: March-05:
> > John 15 37 40 40
> > Jane 17 35 35 35
> > Dan 18 40 42 45
> >
> > Where the months represent salary, for example.
> > My issue is that the months are part of one row...i cannot seem to search
> > for it in Access via query by, say, entering Name, Age, and Month.
> > In Excel i was able to concatenate Name & Age, and then run an "Index"
> > function where you would search the following: Index(John15, Jan-05) = 37
> >
> > But i cannot seem to do the same in Access...mind you there are really
> > 1000s
> > of cells, and therefore need to use Access, but cannot create another
> > bunch
> > of columns for the months b/c there'd be millions of cells.
> >
> > Any help would be greatly appreciate!
>
>
>

thanks for the posts, much appreciated.
However, i believe i need to make my issue more clear.
The data is from a download from a general ledger system. It cannot be
changed in its download form. It would look like it is below (i added the
concatenate myself to create a unique field).
So im just trying to create a query where i can search by Concatenate and by
month.

Concatenate Business Category Jan 05 Feb 05 Mar 05
Apple CoSold Apple Co Sold 10 20 30
Orange CoSold Orange Co Sold 20 30 40
Peach CoSold Peach Co Sold 30 40 50
Apple CoDiscarded Apple Co Discarded 5 5 5
Orange CoDiscarded Orange Co Discarded 10 10 10
Peach CoDiscarded Peach Co Discarded 15 15 15

Thanks!
-Dan

"Jerry Whittle" wrote:

> Hi Dan,
>
> It's because your table isn't set up properly. You should have one table
> with the Person's information. Also it should not have Age; rather you should
> have date of birth DOB and calculate the age as needed. This table should
> have a primary key like maybe an EmployeeNumber or, IMHO, just an autonumber.
>
> In a second table you would have another primary key (autonumber), a foriegn
> key field to link to the Person table, a date field and Salary field. Each
> bit of data that you now have in a "cell" would be an individual record in
> this table. Then you could do a query by Person, Age (calculated), and month
> to find the information.
>
> You mentioned Excel and said "cell". You are doing what is known as
> "committing spreadsheet" which is a grevious crime punishable by lots of
> confusion when using a database. I suggest reading Database Design for Mere
> Mortals by Hernandez or some other primer on relational databases before
> going much further.
>
> --
> Jerry Whittle
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Dan" wrote:
>
> > How can i create queries/searches for information in the following format:
> >
> > Name: Age: Jan-05: Feb-05: March-05:
> > John 15 37 40 40
> > Jane 17 35 35 35
> > Dan 18 40 42 45
> >
> > Where the months represent salary, for example.
> > My issue is that the months are part of one row...i cannot seem to search
> > for it in Access via query by, say, entering Name, Age, and Month.
> > In Excel i was able to concatenate Name & Age, and then run an "Index"
> > function where you would search the following: Index(John15, Jan-05) = 37
> >
> > But i cannot seem to do the same in Access...mind you there are really 1000s
> > of cells, and therefore need to use Access, but cannot create another bunch
> > of columns for the months b/c there'd be millions of cells.
> >
> > Any help would be greatly appreciate!

Then your source of data, from the General Ledger System, is either not
normalized or a query is being run that turns possibly normalized data into a
flat file. It really doesn't matter which is the case. If you want to use
Access queries effectively, and not bump up against problems with retrieving
information from your raw data, you should look at normalizing the data. You
can import (or possibly link to) this data into a temporary table, and then
use VBA procedures to properly normalize it.

With your current data structure, you'll need to either use the Union Query
that I gave you in my earlier post, or the select query that John Vinson
volunteered. There's really no other viable alternatives.


Tom

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


"Dan" wrote:

> thanks for the posts, much appreciated.
> However, i believe i need to make my issue more clear.
> The data is from a download from a general ledger system. It cannot be
> changed in its download form. It would look like it is below (i added the
> concatenate myself to create a unique field).
> So im just trying to create a query where i can search by Concatenate and by
> month.
>
> Concatenate Business Category Jan 05 Feb 05 Mar 05
> Apple CoSold Apple Co Sold 10 20 30
> Orange CoSold Orange Co Sold 20 30 40
> Peach CoSold Peach Co Sold 30 40 50
> Apple CoDiscarded Apple Co Discarded 5 5 5
> Orange CoDiscarded Orange Co Discarded 10 10 10
> Peach CoDiscarded Peach Co Discarded 15 15 15
>
> Thanks!
> -Dan
>
> "Jerry Whittle" wrote:
>
> > Hi Dan,
> >
> > It's because your table isn't set up properly. You should have one table
> > with the Person's information. Also it should not have Age; rather you should
> > have date of birth DOB and calculate the age as needed. This table should
> > have a primary key like maybe an EmployeeNumber or, IMHO, just an autonumber.
> >
> > In a second table you would have another primary key (autonumber), a foriegn
> > key field to link to the Person table, a date field and Salary field. Each
> > bit of data that you now have in a "cell" would be an individual record in
> > this table. Then you could do a query by Person, Age (calculated), and month
> > to find the information.
> >
> > You mentioned Excel and said "cell". You are doing what is known as
> > "committing spreadsheet" which is a grevious crime punishable by lots of
> > confusion when using a database. I suggest reading Database Design for Mere
> > Mortals by Hernandez or some other primer on relational databases before
> > going much further.
> >
> > --
> > Jerry Whittle
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> >
> >
> > "Dan" wrote:
> >
> > > How can i create queries/searches for information in the following format:
> > >
> > > Name: Age: Jan-05: Feb-05: March-05:
> > > John 15 37 40 40
> > > Jane 17 35 35 35
> > > Dan 18 40 42 45
> > >
> > > Where the months represent salary, for example.
> > > My issue is that the months are part of one row...i cannot seem to search
> > > for it in Access via query by, say, entering Name, Age, and Month.
> > > In Excel i was able to concatenate Name & Age, and then run an "Index"
> > > function where you would search the following: Index(John15, Jan-05) = 37
> > >
> > > But i cannot seem to do the same in Access...mind you there are really 1000s
> > > of cells, and therefore need to use Access, but cannot create another bunch
> > > of columns for the months b/c there'd be millions of cells.
> > >
> > > Any help would be greatly appreciate!

You don't have to keep the data in the form that it comes to you. You could
bring it into an Excel spreadsheet, massage it into a different format, which
could then be imported into a normalized Access database. It could look more
like this, then:

Concatenate Business Category DateMonth Quantity
Apple CoSold Apple Co Sold 5-Jan 10
Apple CoSold Apple Co Sold 5-Feb 20
Apple CoSold Apple Co Sold 5-Mar 30
Orange CoSold Orange Co Sold 5-Jan 20
Orange CoSold Orange Co Sold 5-Feb 30
Orange CoSold Orange Co Sold 5-Mar 40
Peach CoSold Peach Co Sold 5-Jan 30
Peach CoSold Peach Co Sold 5-Feb 40
Peach CoSold Peach Co Sold 5-Mar 50
Apple CoDiscarded Apple Co Discarded 5-Jan 5
Apple CoDiscarded Apple Co Discarded 5-Feb 5
Apple CoDiscarded Apple Co Discarded 5-Mar 5
Orange CoDiscarded Orange Co Discarded 5-Jan 10
Orange CoDiscarded Orange Co Discarded 5-Feb 10
Orange CoDiscarded Orange Co Discarded 5-Mar 10
Peach CoDiscarded Peach Co Discarded 5-Jan 15
Peach CoDiscarded Peach Co Discarded 5-Feb 15
Peach CoDiscarded Peach Co Discarded 5-Mar 15

It is possible to do this sort of data manipulation inside of Access, but I
mention Excel because most people are more familiar with it, than with Access.

"Dan" wrote:

> thanks for the posts, much appreciated.
> However, i believe i need to make my issue more clear.
> The data is from a download from a general ledger system. It cannot be
> changed in its download form. It would look like it is below (i added the
> concatenate myself to create a unique field).
> So im just trying to create a query where i can search by Concatenate and by
> month.
>
> Concatenate Business Category Jan 05 Feb 05 Mar 05
> Apple CoSold Apple Co Sold 10 20 30
> Orange CoSold Orange Co Sold 20 30 40
> Peach CoSold Peach Co Sold 30 40 50
> Apple CoDiscarded Apple Co Discarded 5 5 5
> Orange CoDiscarded Orange Co Discarded 10 10 10
> Peach CoDiscarded Peach Co Discarded 15 15 15
>
> Thanks!
> -Dan
>