Hi Edgar,
it is nice to hear such enthusiasm! Rather than building on queries that are already there... go to the sources. I say that since I am not familiar with your database ... it is just too easy to make too many queries! Later on, after the development is to a plateau (never done ;), you can optimize the queries you are using.
try something like this:
SELECT fieldname1, fieldname2, fieldname3, ServiceDate
FROM tblInventory
WHERE
Month(tblInventorytblInventory ServiceDate) = 7
AND Year(tblInventory.ServiceDate) = 2011
AND CustID IN (SELECT CustID FROM Addresses WHERE City = "San Juan")
AND CustID IN (SELECT CustID FROM Customers WHERE IsActive=True)
WHERE
- ServiceDate is the fieldname in tblInventory that you are filtering on
- City is the city field in your Addresses table
- IsActive is in your Customers table.
- IsActive=True represents the logic to get active customers
Once you get the query working, add other tables to the design to get other information like customer name
~~~
this is not the most efficient way ... the master of SQL is John so if he gives you something different, I'd go with it ;)
Warm Regards,
Crystal
Access Basics by Crystal (Bill Mosca's site)
http://thatlldoit.com
Free 100-page book that covers essentials in Access
*
(: have an awesome day :)
*
--- On Thu, 6/30/11, rivera_vazquez_edgar wrote:
> Hello again to everyone!
>
> I've taken off like a rocket with this database, and
> there's no stopping me! That is, if you guys give your usual
> support...
>
> I need to make my report. I call it "The Rute" because it
> should be made up of all the customers that have a service
> due for the current month in a given city, so I know who to
> visit. So I need a single report for every city I choose to
> print. But the thing is, I need to figure out how to
> structure the report so I know what queries to make.
>
> The idea I have is the following, please correct me:
>
> Make a report using qryActiveCustomers with a subform using
> tblAddress to show the address for each active customer and
> a sub-subform for the inventory of that particular
> address. The thing is, the tblInventory is the one
> that has the service due date in the field "NxtServDate".
> How would I filter it so I only get, for example all the
> customers in the city of San Juan with a next service date
> of July? The parent form would have to be filtered by the
> grandchild form, is this posible? If so, How?
>
> I'm thinking of using a user input in the qryActiveCustomer
> to ask for the City name...
>
>
Tidak ada komentar:
Posting Komentar