Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Problem with combining same field query!!!!!

Discussion in 'Information Technology' started by Kwgame, Jul 28, 2006.

  1. Kwgame

    Kwgame
    Expand Collapse
    Guest

    I am creating a database for my job that deals with getting information about
    workload in different areas. What I want to do is bar chart the information
    in way that would compare each area but what I need to do first is create a
    query that has each area listed so I can chart each individual field. The
    comparison all comes from one field (Design Hours) which is located in my
    table and I need to come up with a new query that will divide the Design Hour
    field into three different areas. So to sum up what I want, basically I want
    to filter out the Design Hours field from the table and then in the query
    have Area1 field and the design hours for that area, Area2 field and the
    design hours for that area, and Area3 field and the design hours for that
    area. I am an intern and I am extremely new to access, this is my first
    database so can anyone help me please!!!!
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Wolfgang Kais

    Wolfgang Kais
    Expand Collapse
    Guest

    Hello "Kwgame".

    "Kwgame" wrote:
    > I am creating a database for my job that deals with getting information
    > about workload in different areas. What I want to do is bar chart the
    > information in way that would compare each area but what I need to
    > do first is create a query that has each area listed so I can chart each
    > individual field. The comparison all comes from one field (Design Hours)
    > which is located in my table and I need to come up with a new query
    > that will divide the Design Hour field into three different areas. So to
    > sum up what I want, basically I want to filter out the Design Hours
    > field from the table and then in the query have Area1 field and the
    > design hours for that area, Area2 field and the design hours for that
    > area, and Area3 field and the design hours for that area. I am an
    > intern and I am extremely new to access, this is my first database
    > so can anyone help me please!!!!


    How is the table designed? Does it contain an area field?

    --
    Regards,
    Wolfgang
     
  4. Kwgame

    Kwgame
    Expand Collapse
    Guest

    Hey "Kais"

    "Wolfgang Kais" wrote:

    > Hello "Kwgame".
    >
    > "Kwgame" wrote:
    > > I am creating a database for my job that deals with getting information
    > > about workload in different areas. What I want to do is bar chart the
    > > information in way that would compare each area but what I need to
    > > do first is create a query that has each area listed so I can chart each
    > > individual field. The comparison all comes from one field (Design Hours)
    > > which is located in my table and I need to come up with a new query
    > > that will divide the Design Hour field into three different areas. So to
    > > sum up what I want, basically I want to filter out the Design Hours
    > > field from the table and then in the query have Area1 field and the
    > > design hours for that area, Area2 field and the design hours for that
    > > area, and Area3 field and the design hours for that area. I am an
    > > intern and I am extremely new to access, this is my first database
    > > so can anyone help me please!!!!

    >
    > How is the table designed? Does it contain an area field?
    >
    > --
    > Regards,
    > Wolfgang
    >
    >
    >
     
  5. Wolfgang Kais

    Wolfgang Kais
    Expand Collapse
    Guest

    Hello "Kwgame".

    "Kwgame" wrote:
    >>> I am creating a database for my job that deals with getting information
    >>> about workload in different areas. What I want to do is bar chart the
    >>> information in way that would compare each area but what I need to
    >>> do first is create a query that has each area listed so I can chart each
    >>> individual field. The comparison all comes from one field (Design Hours)
    >>> which is located in my table and I need to come up with a new query
    >>> that will divide the Design Hour field into three different areas. So to
    >>> sum up what I want, basically I want to filter out the Design Hours
    >>> field from the table and then in the query have Area1 field and the
    >>> design hours for that area, Area2 field and the design hours for that
    >>> area, and Area3 field and the design hours for that area. I am an
    >>> intern and I am extremely new to access, this is my first database
    >>> so can anyone help me please!!!!


    >> How is the table designed? Does it contain an area field?


    > Hey "Kais"


    This is too little information to answer your question ;-)

    --
    Regards,
    Wolfgang
     
  6. Kwgame

    Kwgame
    Expand Collapse
    Guest

    Thanks for your response Karl. I implemented the SQL but I keep getting
    that I have to enter the parameters. I changed Cutomer to my table name.
    What I want my new query to look like is I want field 1 to be sorted with the
    names that are in charge of that area ("name", "name2") and then in the
    second field in that query I want have all the Design Hours associated with
    those names. I don't even need to sum them up cause that automatically
    happens in the chart which is where I want to take the information from the
    query. The two fields I am trying to take from my table are User Contact
    ("name") and Design Hours. Can you please tell me what I need to do?

    "KARL DEWEY" wrote:

    > Substitute your table name for Customer.
    >
    > SELECT Customer.Area, Sum(Customer.Design_Hours) AS SumOfDesign_Hours
    > FROM Customer
    > GROUP BY Customer.Area;
    >
    >
    > "Kwgame" wrote:
    >
    > > I am creating a database for my job that deals with getting information about
    > > workload in different areas. What I want to do is bar chart the information
    > > in way that would compare each area but what I need to do first is create a
    > > query that has each area listed so I can chart each individual field. The
    > > comparison all comes from one field (Design Hours) which is located in my
    > > table and I need to come up with a new query that will divide the Design Hour
    > > field into three different areas. So to sum up what I want, basically I want
    > > to filter out the Design Hours field from the table and then in the query
    > > have Area1 field and the design hours for that area, Area2 field and the
    > > design hours for that area, and Area3 field and the design hours for that
    > > area. I am an intern and I am extremely new to access, this is my first
    > > database so can anyone help me please!!!!
     
  7. Kwgame

    Kwgame
    Expand Collapse
    Guest

    Here is a little more help with showing you what I want.
    from table:
    UserContact DesignHours
    Name1 34
    Name1 56
    Name2 55
    Name3 23
    Name2 65

    new query:
    DesignHoursName1 DesignHoursName2 DesignHoursName3
    34 55 23
    56 65 22
    66 36 44
    45 25 27

    I want each field to be sorted by the name associated with those Design
    Hours. So basically I want each field to be based on two field from the
    table and then output to the new query.

    "KARL DEWEY" wrote:

    > Substitute your table name for Customer.
    >
    > SELECT Customer.Area, Sum(Customer.Design_Hours) AS SumOfDesign_Hours
    > FROM Customer
    > GROUP BY Customer.Area;
    >
    >
    > "Kwgame" wrote:
    >
    > > I am creating a database for my job that deals with getting information about
    > > workload in different areas. What I want to do is bar chart the information
    > > in way that would compare each area but what I need to do first is create a
    > > query that has each area listed so I can chart each individual field. The
    > > comparison all comes from one field (Design Hours) which is located in my
    > > table and I need to come up with a new query that will divide the Design Hour
    > > field into three different areas. So to sum up what I want, basically I want
    > > to filter out the Design Hours field from the table and then in the query
    > > have Area1 field and the design hours for that area, Area2 field and the
    > > design hours for that area, and Area3 field and the design hours for that
    > > area. I am an intern and I am extremely new to access, this is my first
    > > database so can anyone help me please!!!!
     
  8. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    This will give you one row in the results.
    TRANSFORM Sum(Kwgame.DesignHours) AS SumOfDesignHours
    SELECT Kwgame.x
    FROM Kwgame
    GROUP BY Kwgame.x
    PIVOT Kwgame.UserContact;

    Your results example shows four rows. What would make the output contain
    four rows?

    It would be a whole lot easier and less time consumming if you posted your
    table structure and a sample of data like Wolfgang suggested.

    "Kwgame" wrote:

    > Here is a little more help with showing you what I want.
    > from table:
    > UserContact DesignHours
    > Name1 34
    > Name1 56
    > Name2 55
    > Name3 23
    > Name2 65
    >
    > new query:
    > DesignHoursName1 DesignHoursName2 DesignHoursName3
    > 34 55 23
    > 56 65 22
    > 66 36 44
    > 45 25 27
    >
    > I want each field to be sorted by the name associated with those Design
    > Hours. So basically I want each field to be based on two field from the
    > table and then output to the new query.
    >
    > "KARL DEWEY" wrote:
    >
    > > Substitute your table name for Customer.
    > >
    > > SELECT Customer.Area, Sum(Customer.Design_Hours) AS SumOfDesign_Hours
    > > FROM Customer
    > > GROUP BY Customer.Area;
    > >
    > >
    > > "Kwgame" wrote:
    > >
    > > > I am creating a database for my job that deals with getting information about
    > > > workload in different areas. What I want to do is bar chart the information
    > > > in way that would compare each area but what I need to do first is create a
    > > > query that has each area listed so I can chart each individual field. The
    > > > comparison all comes from one field (Design Hours) which is located in my
    > > > table and I need to come up with a new query that will divide the Design Hour
    > > > field into three different areas. So to sum up what I want, basically I want
    > > > to filter out the Design Hours field from the table and then in the query
    > > > have Area1 field and the design hours for that area, Area2 field and the
    > > > design hours for that area, and Area3 field and the design hours for that
    > > > area. I am an intern and I am extremely new to access, this is my first
    > > > database so can anyone help me please!!!!
     
  9. Kwgame

    Kwgame
    Expand Collapse
    Guest

    What I really want to do is compare the hours for 4 different groups in a
    chart format and to do this I have to make a query with the 4 different group
    hours since I cannot take the DesignHour coloumn from four different query or
    I get a message
    that says I can't take fields from a table and a query based on a table.
    Access really sucks when it comes to graphing!!

    "KARL DEWEY" wrote:

    > This will give you one row in the results.
    > TRANSFORM Sum(Kwgame.DesignHours) AS SumOfDesignHours
    > SELECT Kwgame.x
    > FROM Kwgame
    > GROUP BY Kwgame.x
    > PIVOT Kwgame.UserContact;
    >
    > Your results example shows four rows. What would make the output contain
    > four rows?
    >
    > It would be a whole lot easier and less time consumming if you posted your
    > table structure and a sample of data like Wolfgang suggested.
    >
    > "Kwgame" wrote:
    >
    > > Here is a little more help with showing you what I want.
    > > from table:
    > > UserContact DesignHours
    > > Name1 34
    > > Name1 56
    > > Name2 55
    > > Name3 23
    > > Name2 65
    > >
    > > new query:
    > > DesignHoursName1 DesignHoursName2 DesignHoursName3
    > > 34 55 23
    > > 56 65 22
    > > 66 36 44
    > > 45 25 27
    > >
    > > I want each field to be sorted by the name associated with those Design
    > > Hours. So basically I want each field to be based on two field from the
    > > table and then output to the new query.
    > >
    > > "KARL DEWEY" wrote:
    > >
    > > > Substitute your table name for Customer.
    > > >
    > > > SELECT Customer.Area, Sum(Customer.Design_Hours) AS SumOfDesign_Hours
    > > > FROM Customer
    > > > GROUP BY Customer.Area;
    > > >
    > > >
    > > > "Kwgame" wrote:
    > > >
    > > > > I am creating a database for my job that deals with getting information about
    > > > > workload in different areas. What I want to do is bar chart the information
    > > > > in way that would compare each area but what I need to do first is create a
    > > > > query that has each area listed so I can chart each individual field. The
    > > > > comparison all comes from one field (Design Hours) which is located in my
    > > > > table and I need to come up with a new query that will divide the Design Hour
    > > > > field into three different areas. So to sum up what I want, basically I want
    > > > > to filter out the Design Hours field from the table and then in the query
    > > > > have Area1 field and the design hours for that area, Area2 field and the
    > > > > design hours for that area, and Area3 field and the design hours for that
    > > > > area. I am an intern and I am extremely new to access, this is my first
    > > > > database so can anyone help me please!!!!
     
  10. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    If you are unwilling to provide the sample data and table structure as I
    asked I can not help you.

    "Kwgame" wrote:

    > What I really want to do is compare the hours for 4 different groups in a
    > chart format and to do this I have to make a query with the 4 different group
    > hours since I cannot take the DesignHour coloumn from four different query or
    > I get a message
    > that says I can't take fields from a table and a query based on a table.
    > Access really sucks when it comes to graphing!!
    >
    > "KARL DEWEY" wrote:
    >
    > > This will give you one row in the results.
    > > TRANSFORM Sum(Kwgame.DesignHours) AS SumOfDesignHours
    > > SELECT Kwgame.x
    > > FROM Kwgame
    > > GROUP BY Kwgame.x
    > > PIVOT Kwgame.UserContact;
    > >
    > > Your results example shows four rows. What would make the output contain
    > > four rows?
    > >
    > > It would be a whole lot easier and less time consumming if you posted your
    > > table structure and a sample of data like Wolfgang suggested.
    > >
    > > "Kwgame" wrote:
    > >
    > > > Here is a little more help with showing you what I want.
    > > > from table:
    > > > UserContact DesignHours
    > > > Name1 34
    > > > Name1 56
    > > > Name2 55
    > > > Name3 23
    > > > Name2 65
    > > >
    > > > new query:
    > > > DesignHoursName1 DesignHoursName2 DesignHoursName3
    > > > 34 55 23
    > > > 56 65 22
    > > > 66 36 44
    > > > 45 25 27
    > > >
    > > > I want each field to be sorted by the name associated with those Design
    > > > Hours. So basically I want each field to be based on two field from the
    > > > table and then output to the new query.
    > > >
    > > > "KARL DEWEY" wrote:
    > > >
    > > > > Substitute your table name for Customer.
    > > > >
    > > > > SELECT Customer.Area, Sum(Customer.Design_Hours) AS SumOfDesign_Hours
    > > > > FROM Customer
    > > > > GROUP BY Customer.Area;
    > > > >
    > > > >
    > > > > "Kwgame" wrote:
    > > > >
    > > > > > I am creating a database for my job that deals with getting information about
    > > > > > workload in different areas. What I want to do is bar chart the information
    > > > > > in way that would compare each area but what I need to do first is create a
    > > > > > query that has each area listed so I can chart each individual field. The
    > > > > > comparison all comes from one field (Design Hours) which is located in my
    > > > > > table and I need to come up with a new query that will divide the Design Hour
    > > > > > field into three different areas. So to sum up what I want, basically I want
    > > > > > to filter out the Design Hours field from the table and then in the query
    > > > > > have Area1 field and the design hours for that area, Area2 field and the
    > > > > > design hours for that area, and Area3 field and the design hours for that
    > > > > > area. I am an intern and I am extremely new to access, this is my first
    > > > > > database so can anyone help me please!!!!
     
  11. Kwgame

    Kwgame
    Expand Collapse
    Guest

    I didnt know how to put up table structure, anyway ill give you a query and
    tell you what i want to do with it:
    SELECT Sum([Group1PTP-Tami].[Design Hours]) AS [SumOfDesign Hours],
    Sum([Group2PTP-Brenda].[Design Hours]) AS [SumOfDesign Hours1],
    Sum([Group3PTP-Sam].[Design Hours]) AS [SumOfDesign Hours2],
    Sum([Group4PTP-Larry].[Design Hours]) AS [SumOfDesign Hours3]
    FROM [Group1PTP-Tami], [Group2PTP-Brenda], [Group3PTP-Sam], [Group4PTP-Larry];

    now when I do this, it gives me a sum that is really really big for each
    column, what I want to do is get each column to correspond to only their
    table so I can get individual sums for each column. (ex.
    Sum([Group1PTP-Tami].[Design Hours]) AS [SumOfDesign Hours] only FROM
    [Group1PTP-Tami] not the rest, and so on for the other query field). Thanks,
    I missed the statement about posting sample data.

    "KARL DEWEY" wrote:

    > If you are unwilling to provide the sample data and table structure as I
    > asked I can not help you.
    >
    > "Kwgame" wrote:
    >
    > > What I really want to do is compare the hours for 4 different groups in a
    > > chart format and to do this I have to make a query with the 4 different group
    > > hours since I cannot take the DesignHour coloumn from four different query or
    > > I get a message
    > > that says I can't take fields from a table and a query based on a table.
    > > Access really sucks when it comes to graphing!!
    > >
    > > "KARL DEWEY" wrote:
    > >
    > > > This will give you one row in the results.
    > > > TRANSFORM Sum(Kwgame.DesignHours) AS SumOfDesignHours
    > > > SELECT Kwgame.x
    > > > FROM Kwgame
    > > > GROUP BY Kwgame.x
    > > > PIVOT Kwgame.UserContact;
    > > >
    > > > Your results example shows four rows. What would make the output contain
    > > > four rows?
    > > >
    > > > It would be a whole lot easier and less time consumming if you posted your
    > > > table structure and a sample of data like Wolfgang suggested.
    > > >
    > > > "Kwgame" wrote:
    > > >
    > > > > Here is a little more help with showing you what I want.
    > > > > from table:
    > > > > UserContact DesignHours
    > > > > Name1 34
    > > > > Name1 56
    > > > > Name2 55
    > > > > Name3 23
    > > > > Name2 65
    > > > >
    > > > > new query:
    > > > > DesignHoursName1 DesignHoursName2 DesignHoursName3
    > > > > 34 55 23
    > > > > 56 65 22
    > > > > 66 36 44
    > > > > 45 25 27
    > > > >
    > > > > I want each field to be sorted by the name associated with those Design
    > > > > Hours. So basically I want each field to be based on two field from the
    > > > > table and then output to the new query.
    > > > >
    > > > > "KARL DEWEY" wrote:
    > > > >
    > > > > > Substitute your table name for Customer.
    > > > > >
    > > > > > SELECT Customer.Area, Sum(Customer.Design_Hours) AS SumOfDesign_Hours
    > > > > > FROM Customer
    > > > > > GROUP BY Customer.Area;
    > > > > >
    > > > > >
    > > > > > "Kwgame" wrote:
    > > > > >
    > > > > > > I am creating a database for my job that deals with getting information about
    > > > > > > workload in different areas. What I want to do is bar chart the information
    > > > > > > in way that would compare each area but what I need to do first is create a
    > > > > > > query that has each area listed so I can chart each individual field. The
    > > > > > > comparison all comes from one field (Design Hours) which is located in my
    > > > > > > table and I need to come up with a new query that will divide the Design Hour
    > > > > > > field into three different areas. So to sum up what I want, basically I want
    > > > > > > to filter out the Design Hours field from the table and then in the query
    > > > > > > have Area1 field and the design hours for that area, Area2 field and the
    > > > > > > design hours for that area, and Area3 field and the design hours for that
    > > > > > > area. I am an intern and I am extremely new to access, this is my first
    > > > > > > database so can anyone help me please!!!!
     
  12. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    You need to change your database structure. Do not have a separate table for
    each individual but one table with a field for name and Design Hours.

    I do not not know what you use the data for besides tracking hours but you
    probably need multiple tables in a one-to-many relationship.
    People --
    EmpID - Autonumber - primary key
    FName - text
    LName - text
    MI - text
    Suffix - text (JR, SR, II, III, IV)
    Prefered - text (Bill, Willie, Jimbo, Sandy)
    DOB - DateTime
    Sex - text
    Position, Title, Education, etc.

    Project --
    ProjID - Autonumber - primary key
    Name -
    StartDate - DateTime
    ExpectEnd - DateTime
    Manager - Number integer - foreign key to People.EmpID

    PeopleProj --
    ProjID - number integer - foreign key to Project.ProjID
    EmpID - number integer - foreign key to People.EmpID
    WorkDate - DateTime
    DesignHours - Number - single



    "Kwgame" wrote:

    > I didnt know how to put up table structure, anyway ill give you a query and
    > tell you what i want to do with it:
    > SELECT Sum([Group1PTP-Tami].[Design Hours]) AS [SumOfDesign Hours],
    > Sum([Group2PTP-Brenda].[Design Hours]) AS [SumOfDesign Hours1],
    > Sum([Group3PTP-Sam].[Design Hours]) AS [SumOfDesign Hours2],
    > Sum([Group4PTP-Larry].[Design Hours]) AS [SumOfDesign Hours3]
    > FROM [Group1PTP-Tami], [Group2PTP-Brenda], [Group3PTP-Sam], [Group4PTP-Larry];
    >
    > now when I do this, it gives me a sum that is really really big for each
    > column, what I want to do is get each column to correspond to only their
    > table so I can get individual sums for each column. (ex.
    > Sum([Group1PTP-Tami].[Design Hours]) AS [SumOfDesign Hours] only FROM
    > [Group1PTP-Tami] not the rest, and so on for the other query field). Thanks,
    > I missed the statement about posting sample data.
    >
    > "KARL DEWEY" wrote:
    >
    > > If you are unwilling to provide the sample data and table structure as I
    > > asked I can not help you.
    > >
    > > "Kwgame" wrote:
    > >
    > > > What I really want to do is compare the hours for 4 different groups in a
    > > > chart format and to do this I have to make a query with the 4 different group
    > > > hours since I cannot take the DesignHour coloumn from four different query or
    > > > I get a message
    > > > that says I can't take fields from a table and a query based on a table.
    > > > Access really sucks when it comes to graphing!!
    > > >
    > > > "KARL DEWEY" wrote:
    > > >
    > > > > This will give you one row in the results.
    > > > > TRANSFORM Sum(Kwgame.DesignHours) AS SumOfDesignHours
    > > > > SELECT Kwgame.x
    > > > > FROM Kwgame
    > > > > GROUP BY Kwgame.x
    > > > > PIVOT Kwgame.UserContact;
    > > > >
    > > > > Your results example shows four rows. What would make the output contain
    > > > > four rows?
    > > > >
    > > > > It would be a whole lot easier and less time consumming if you posted your
    > > > > table structure and a sample of data like Wolfgang suggested.
    > > > >
    > > > > "Kwgame" wrote:
    > > > >
    > > > > > Here is a little more help with showing you what I want.
    > > > > > from table:
    > > > > > UserContact DesignHours
    > > > > > Name1 34
    > > > > > Name1 56
    > > > > > Name2 55
    > > > > > Name3 23
    > > > > > Name2 65
    > > > > >
    > > > > > new query:
    > > > > > DesignHoursName1 DesignHoursName2 DesignHoursName3
    > > > > > 34 55 23
    > > > > > 56 65 22
    > > > > > 66 36 44
    > > > > > 45 25 27
    > > > > >
    > > > > > I want each field to be sorted by the name associated with those Design
    > > > > > Hours. So basically I want each field to be based on two field from the
    > > > > > table and then output to the new query.
    > > > > >
    > > > > > "KARL DEWEY" wrote:
    > > > > >
    > > > > > > Substitute your table name for Customer.
    > > > > > >
    > > > > > > SELECT Customer.Area, Sum(Customer.Design_Hours) AS SumOfDesign_Hours
    > > > > > > FROM Customer
    > > > > > > GROUP BY Customer.Area;
    > > > > > >
    > > > > > >
    > > > > > > "Kwgame" wrote:
    > > > > > >
    > > > > > > > I am creating a database for my job that deals with getting information about
    > > > > > > > workload in different areas. What I want to do is bar chart the information
    > > > > > > > in way that would compare each area but what I need to do first is create a
    > > > > > > > query that has each area listed so I can chart each individual field. The
    > > > > > > > comparison all comes from one field (Design Hours) which is located in my
    > > > > > > > table and I need to come up with a new query that will divide the Design Hour
    > > > > > > > field into three different areas. So to sum up what I want, basically I want
    > > > > > > > to filter out the Design Hours field from the table and then in the query
    > > > > > > > have Area1 field and the design hours for that area, Area2 field and the
    > > > > > > > design hours for that area, and Area3 field and the design hours for that
    > > > > > > > area. I am an intern and I am extremely new to access, this is my first
    > > > > > > > database so can anyone help me please!!!!
     

Share This Page