query set up question

18 Jun 2009 3:46 PM

Selby

Access 2003
A query has been created that pulls inventory amounts per item by an "as of"
date for each individual warehouse. It is looking at various tables such as
invoice, date, product id, activity, etc... I am now needing a query that
just gives me one ending balance per item for all of the warehouses in total.
Is there a way to group the warehouses or do I need to start from scratch?

18 Jun 2009 4:35 PM

John W. Vinson

On Thu, 18 Jun 2009 08:46:01 -0700, Selby <Se***@discussions.microsoft.com>
wrote:

>Access 2003
>A query has been created that pulls inventory amounts per item by an "as of"
>date for each individual warehouse. It is looking at various tables such as
>invoice, date, product id, activity, etc... I am now needing a query that
>just gives me one ending balance per item for all of the warehouses in total.
> Is there a way to group the warehouses or do I need to start from scratch?

Please reread this message.
Clear your mind of all your knowledge of the structure of your database other
than what you've posted.

How on Earth could anyone answer?

"Group the warehouses"??? How is the warehouse represented in the table? What
do you mean by grouping?

Give us some help here please?

--

John W. Vinson [MVP]

18 Jun 2009 5:08 PM

Selby

I guess that is why I am posting in the New Users section.....

What do you mean by how is the warehouse represented in the table? There is
a warehouse table that has a warehouse number and a name that is associated
with the number. The current query will pull by the warehouse number.

By grouping, I mean instead of having a query that gives me an amount for an
item per one warehouse, I need to have the total amount for that item that is
in all warehouses. I was hoping that I could use the existing query just
tweek it to get the info.

I apologize for the lack of information. Perhaps someone on Earth will be
able to help me.............

Show quoteHide quote

"John W. Vinson" wrote:

> On Thu, 18 Jun 2009 08:46:01 -0700, Selby <Se***@discussions.microsoft.com>
> wrote:
>
> >Access 2003
> >A query has been created that pulls inventory amounts per item by an "as of"
> >date for each individual warehouse. It is looking at various tables such as
> >invoice, date, product id, activity, etc... I am now needing a query that
> >just gives me one ending balance per item for all of the warehouses in total.
> > Is there a way to group the warehouses or do I need to start from scratch?
>
> Please reread this message.
> Clear your mind of all your knowledge of the structure of your database other
> than what you've posted.
>
> How on Earth could anyone answer?
>
> "Group the warehouses"??? How is the warehouse represented in the table? What
> do you mean by grouping?
>
> Give us some help here please?
> --
>
> John W. Vinson [MVP]
>

18 Jun 2009 5:13 PM

KARL DEWEY

Try dropping the warehouse information completely from the query.

Show quoteHide quote

"Selby" wrote:

> I guess that is why I am posting in the New Users section.....
>
> What do you mean by how is the warehouse represented in the table? There is
> a warehouse table that has a warehouse number and a name that is associated
> with the number. The current query will pull by the warehouse number.
>
> By grouping, I mean instead of having a query that gives me an amount for an
> item per one warehouse, I need to have the total amount for that item that is
> in all warehouses. I was hoping that I could use the existing query just
> tweek it to get the info.
>
> I apologize for the lack of information. Perhaps someone on Earth will be
> able to help me.............
>
>
>
>
>
>
> "John W. Vinson" wrote:
>
> > On Thu, 18 Jun 2009 08:46:01 -0700, Selby <Se***@discussions.microsoft.com>
> > wrote:
> >
> > >Access 2003
> > >A query has been created that pulls inventory amounts per item by an "as of"
> > >date for each individual warehouse. It is looking at various tables such as
> > >invoice, date, product id, activity, etc... I am now needing a query that
> > >just gives me one ending balance per item for all of the warehouses in total.
> > > Is there a way to group the warehouses or do I need to start from scratch?
> >
> > Please reread this message.
> > Clear your mind of all your knowledge of the structure of your database other
> > than what you've posted.
> >
> > How on Earth could anyone answer?
> >
> > "Group the warehouses"??? How is the warehouse represented in the table? What
> > do you mean by grouping?
> >
> > Give us some help here please?
> > --
> >
> > John W. Vinson [MVP]
> >

18 Jun 2009 6:04 PM

Larry Daugherty

You're in one of the right places for your post but that doesn't
relieve you of having to communicate some relevant details - if you
want help.

Your schema is not described well enough for someone to provide you a
solution. Typically, you'd detail the names of the relevant tables
and the names and datatypes of relevant fields. In other cases you
might post the SQL of a query that is similar to the one you need. If
you designed your current query with the QBE designer then click View

| SQL and copy the SQL into your next post.

HTH

--
-Larry-
--

"Selby" <Se***@discussions.microsoft.com> wrote in message
news:B2355A60-58F3-4DF8-A897-714C133DC377@microsoft.com...
> I guess that is why I am posting in the New Users section.....
>
> What do you mean by how is the warehouse represented in the table?
There is
> a warehouse table that has a warehouse number and a name that is
associated
> with the number. The current query will pull by the warehouse
number.
>
> By grouping, I mean instead of having a query that gives me an

amount for an

> item per one warehouse, I need to have the total amount for that

item that is
Show quoteHide quote

> in all warehouses. I was hoping that I could use the existing query
just
> tweek it to get the info.
>
> I apologize for the lack of information. Perhaps someone on Earth
will be
> able to help me.............
>
>
>
>
>
>
> "John W. Vinson" wrote:
>
> > On Thu, 18 Jun 2009 08:46:01 -0700, Selby

<Se***@discussions.microsoft.com>

> > wrote:
> >
> > >Access 2003
> > >A query has been created that pulls inventory amounts per item by

an "as of"

> > >date for each individual warehouse. It is looking at various

tables such as

> > >invoice, date, product id, activity, etc... I am now needing a
query that
> > >just gives me one ending balance per item for all of the

warehouses in total.
Show quoteHide quote

> > > Is there a way to group the warehouses or do I need to start
from scratch?
> >
> > Please reread this message.
> > Clear your mind of all your knowledge of the structure of your
database other
> > than what you've posted.
> >
> > How on Earth could anyone answer?
> >
> > "Group the warehouses"??? How is the warehouse represented in the
table? What
> > do you mean by grouping?
> >
> > Give us some help here please?
> > --
> >
> > John W. Vinson [MVP]
> >

18 Jun 2009 6:36 PM

John W. Vinson

On Thu, 18 Jun 2009 10:08:01 -0700, Selby <Se***@discussions.microsoft.com>
wrote:

>I guess that is why I am posting in the New Users section.....
>
>What do you mean by how is the warehouse represented in the table? There is
>a warehouse table that has a warehouse number and a name that is associated
>with the number. The current query will pull by the warehouse number.
>
>By grouping, I mean instead of having a query that gives me an amount for an
>item per one warehouse, I need to have the total amount for that item that is
>in all warehouses. I was hoping that I could use the existing query just
>tweek it to get the info.

You can; just don't group by the warehouse in the query.

It would give us a view of your structure if you could open the query that you
now have in design view. Select View... SQL from the menu. Copy and paste the
SQL text of the query to a message here. It may look cryptic now but you'll
come to use SQL view often - it's the real query, whereas the query grid is
just a tool to build SQL.

--

John W. Vinson [MVP]

18 Jun 2009 5:37 PM

Steve

Just create another query based on your existing query. Only include the
item field and inventory field. Change the query to a Totals Query by
clicking on the sigma button (looks like capital E) in the menu at the top
of the screen. In the inventory field, change Group By to Sum.

Steve
san***@penn.com

Show quoteHide quote

"Selby" <Se***@discussions.microsoft.com> wrote in message
news:C03D5D25-FEAF-4B09-9939-F7E44C6331E2@microsoft.com...
> Access 2003
> A query has been created that pulls inventory amounts per item by an "as
> of"
> date for each individual warehouse. It is looking at various tables such
> as
> invoice, date, product id, activity, etc... I am now needing a query that
> just gives me one ending balance per item for all of the warehouses in
> total.
> Is there a way to group the warehouses or do I need to start from scratch?

18 Jun 2009 6:16 PM

Selby

Thanks Steve!!! That is exactly what I will do and I really appreciate your
helping me....

Show quoteHide quote

"Steve" wrote:

> Just create another query based on your existing query. Only include the
> item field and inventory field. Change the query to a Totals Query by
> clicking on the sigma button (looks like capital E) in the menu at the top
> of the screen. In the inventory field, change Group By to Sum.
>
> Steve
> san***@penn.com
>
>
> "Selby" <Se***@discussions.microsoft.com> wrote in message
> news:C03D5D25-FEAF-4B09-9939-F7E44C6331E2@microsoft.com...
> > Access 2003
> > A query has been created that pulls inventory amounts per item by an "as
> > of"
> > date for each individual warehouse. It is looking at various tables such
> > as
> > invoice, date, product id, activity, etc... I am now needing a query that
> > just gives me one ending balance per item for all of the warehouses in
> > total.
> > Is there a way to group the warehouses or do I need to start from scratch?
>
>
>