Help writting simple query

  • Thread starter Thread starter Rick Cass
  • Start date Start date
R

Rick Cass

I would like to query all records in table tblInvoices
for a date range on a specific customer. Date field is
called TrnDate and format in 3/3/2004 and the customer
name field is called CustomerName.

Example: Give me all records between 02/01/2004 -
02/15/2004 for customer nFocus.

If someone wants to take on a small project that we have
going on send me an email and I will give you all the
specs and you can bid on doing the work. For a Access
programmer this would not take very long.

Thanks,

Rick Cass - (e-mail address removed)
Bytecafe, Inc.
317.418.0797
 
Hi Rick

happy to help with writing the query

go to the query section,
choose create new query in design view,
double click on tblInvoices in the choose table dialog box to add it to the
table section of the query grid
chose the choose table dialog box

double click on the fields that you want to see in your query's answer -
make sure you include TrnDate and CustomerName

now under CustomerName in the criteria line type the name of the customer
you want to find
then under TrnDate in the criteria line type Between 2/1/2004 and 2/15/04
click on the red exclaimation mark and there's your answer

now this can be made much more "user friendly" by creating a form where the
customer name can be chosen from a drop down box & the date range can be
entered and a report produced with the query results ... but try out the
query first (and save it) as this will form the basis of the "user friendly"
approach and then post back.

Cheers
JulieD
 
I would like to query all records in table tblInvoices
for a date range on a specific customer. Date field is
called TrnDate and format in 3/3/2004 and the customer
name field is called CustomerName.

Example: Give me all records between 02/01/2004 -
02/15/2004 for customer nFocus.

If someone wants to take on a small project that we have
going on send me an email and I will give you all the
specs and you can bid on doing the work. For a Access
programmer this would not take very long.
Click to expand...

How about free, and under a minute?

Create a Query based on your table.
On the Criteria line under TrnDate type
= CDate([Enter start date:]) AND < DateAdd("d", 1, CDate([Enter end date:]))
Click to expand...

and under CustomerName type

[Enter customer name:]

Run the query, or (better) use the Report Wizard to create a Report
based on the query.
 
In the design view of the query, under the date field, set
the criteria to:
#2/1/2004# And <#2/15/2004#
Click to expand...

This should work.

NOTICE: if you want to include the records with dates
2/1/2004 and 2/15/2004 you must start the criteria one day
earlier and end it one day later. The result should be:
#1/31/2004# And <#2/16/2004#
Click to expand...

I hope this works.
 
Thanks for you help.

Here is my current problem that is up for debate.

To view my database, I striped it down for size
restrictions. Please download from
http://www.dbtrigger.com/support/bytecafereportingdb031120
04.zip

We are attempting to write a report from Quickbooks in
Crystal Reports. We have a problem with Item Groups on
the invoices. So we need to import the data into MS
Access and massage the data so that it is usable in
Crystal Reports.

One invoice for a customer might have two or three item
groups and there are multiple invoices that we submit
every two weeks to our customer. Well the customer would
like us to submit one report with a breakout of each
invoice by Item Group. This way they can enter the data
per customer (Each group is a customer of theirs).

The problem with the data coming out of Quickbooks is
Item Groups on invoices are not able to be grouped
themselves. No two fields in the record match and allow
us to put the groups in order. So we have to break up
each invoice separately by group. This way we can put
all the item groups together and then write our reports.

Here is the goal:

1. Import Current InvoiceLine table from quickbooks.
Now its manual.
2. Query invoices between two date, 02/01/2004 and
02/15/2004 for a specific customer, nFocus Technologies.
3. Delete certain records - remove blank links on
the invoice. To test for blank line on the invoice,
InvoiceLine:InvoiceLineType = Item.
4. Update LineGroupRefFullName. With the same name
that is in the GroupHeader where date, RefNumber, and
LineSeqNo are the same.
5. Then take from each invoice the different items
by groups, see a group example below, and total them.


Actions Taken:
1. Import InvoiceLine table from Quickbooks -
manually. Would like to link or make this automatic.
Linking a table into access is slow.
2. Setup a make table query to get all the invoices
between a range of dates for a specific customer.
o Make Table Name: tblCustomInvoices
o TrnDate: >=CDate([Enter start date:]) And <DateAdd
("d",1,CDate([Enter end date:]))
o CustomerRefFullName: [Enter customer name:]


Any Help would be appreciated.

If you need anything else please email me.

Rick Cass
Bytecafe, Inc.
 
Here is my current problem that is up for debate.
Click to expand...

Hm. This, of course, bears essentially NO relation to the question you
originally posted. That one was a minute's work; this looks like
several hours, including delving into the Quickbooks object model (a
daunting task right there!)

It's a lot more than I'd be able to tackle on an unpaid-volunteer
basis, for sure!
 
I would like to send you all the information on the
project were trying to get accomplished. Then you can
review and give us a price. We are willing to pay
something, but can't pay a lot. Let me know if you're
interested. There are some basics in a later posting by
me.

Please let me know.

Rick Cass
 
I would like to send you all the information on the
project were trying to get accomplished. Then you can
review and give us a price. We are willing to pay
something, but can't pay a lot. Let me know if you're
interested. There are some basics in a later posting by
me.
Click to expand...

Drop me an email at jvinson <at> WysardOfInfo <dot> com. I'll be glad
to send you my resume and terms and to give you an estimate.
 
Back
Top