Getting data from multiple queries for a form

  • Thread starter Thread starter PaulHilgeman
  • Start date Start date
P

PaulHilgeman

I have a couple of standard queries that run to update the total number
of scheduled yards of installation per day.

I would like to display this data on the main page of the database so
that all users can see how many yards are scheduled and if it is too
much they wont schedule any more.

There are a couple of ways of tackling this problem.. 1) How can I
calculate these values in one query... Basicaly now I am doing a filter
by date and then sum of the yards.

or

2) How can I include data from multiple queries on to one form?

Thanks!!!

Happy New Year!
 
PaulHilgeman said:
I have a couple of standard queries that run to update the total number
of scheduled yards of installation per day.

I would like to display this data on the main page of the database so
that all users can see how many yards are scheduled and if it is too
much they wont schedule any more.

There are a couple of ways of tackling this problem.. 1) How can I
calculate these values in one query... Basicaly now I am doing a filter
by date and then sum of the yards.
Click to expand...

If you share your table structure, that should be easier to answer.
2) How can I include data from multiple queries on to one form?
Click to expand...

I think that for your purposes you can only have one query per form.

HTH;

Amy
 
Thanks for the response Amy,

The table is structured as a list of jobs. Each entry contains a
variety of info, including the installation date and also how many
yards the installation is.

I would like to be able to write a query that will show how many yards
are being installed today, tomorrow, the next day and so on.

So far I have not been able to do this. What I am really looking for
is a 'subtotal' function like in Excel, where at each change in date,
it can sum the yards for that date.

Thanks!!!
 
Paul,

What are the names of the fields in your table, data type, etc. from which
you'd like to query

For example:

T_Jobs (name of table)
JobID (PK - autonumber)
InstallDate (Date/Time)
InstallAmount (Integer)
etc.

Really helps when you provide pertinent info.

Brian
 
Table1 (name of the table)
ID (autonumber)
InstallDate(Date/Time)
Yards(Integer)

Thats it, it looks like you get pretty close with your guess.

-Paul
 
Hi Paul,

to just get the sum of yards scheduled for the current day, this should
work:

SELECT Sum(Table1.Yards) AS SumOfYards
FROM Table1
WHERE (((Table1.InstallDate)=Date()))
GROUP BY Table1.InstallDate;

Brian
 
Where does this go? Into the source code of a text box?

Thanks, that makes perfect sense to me, but I am just not sure where in
access it goes... in the form, right?

Thanks
 
If you want the amount displayed somewhere on your main form, you don't need
a query at all. Simply create an ubound text box on your main form with its
control source set to:

=DSum("Yards","Table1","InstallDate=Date()")

Again, this will only show the current days install amount.
Will that work for you?

Brian
 
Where does this go? Into the source code of a text box?

Thanks, that makes perfect sense to me, but I am just not sure where in
access it goes... in the form, right?

Thanks
Click to expand...

It would be a new Query. Paste it into the SQL view of a new query
(changing table and fieldnames as appropriate). You can then base a
Form or Report on the query.

John W. Vinson[MVP]
 
Back
Top