report question

Posted on 2003-02-26
Medium Priority
Last Modified: 2010-05-01
hi all the experts i respect:
i have a question regarding the data reports :
i have a database with the following data:
techno   date       item    repaireddate     warranty
t1       2/1/2003   tv 14"  4/1/2003         yes
t2       4/1/2003   tv 14"  5/1/2003         yes
t1       4/1/2003   tv 14"                   no
t1       5/1/2003   tv 15"  6/1/2003         yes
t1       6/2/2003   tv 16"                   no

the data reports that i want :
for the month january

techno   item     in     out   warranty      outstanding
t1       tv 14"   2      1     1              1  
         tv 15"   1      1     1              0

t2       tv 14"   1      1     1              0  

another question is how to detect the date of the month like every end of month the report will generate automatically
how to create this reports ...please help me.thank you very much for your time
Question by:wong_k
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 53

Accepted Solution

Ryan Chong earned 160 total points
ID: 8030512
Try use the Group By to group the data, example:

Select techno, item, count(warranty) as [warranty] from table group by techno, item

>>another question is how to detect the date of the month like every end of month the report will generate automatically
Since data are kept, there is no need to generating reports, unless you need to print the report out or export the data to other format. And you can do a startup check to determine whether it's end of the month, if return true then do the task to 'generate' reports.

Expert Comment

ID: 8031538
First problem
you need a picture box and a command button


Private Sub Command1_Click()
    Dim techno(1 To 5) As String
    Dim dat(1 To 5) As Date
    Dim item(1 To 5) As String
    Dim repairdat(1 To 5) As Date
    Dim warranty(1 To 5) As String
    Dim colwidth(1 To 5) As Integer
    Dim fmt(1 To 5) As String
    Dim col(1 To 5) As String
    Picture1.Font = "Courier"
    col(1) = "techno"
    col(2) = "date"
    col(3) = "item"
    col(4) = "repairdate"
    col(5) = "warranty"
    colwidth(1) = 6
    colwidth(2) = 10
    colwidth(3) = 10
    colwidth(4) = 12
    colwidth(5) = 9
    techno(1) = "t1"
    dat(1) = "2/1/2003"
    item(1) = "tv 14"""
    repairdat(1) = "4/1/2003"
    warranty(1) = "yes"
    techno(2) = "t2"
    dat(2) = "4/1/2003"
    item(2) = "tv 14"""
    repairdat(2) = "5/1/2003"
    warranty(2) = "yes"
    techno(3) = "t1"
    dat(3) = "4/1/2003"
    item(3) = "tv 14"""
    repairdat(3) = Empty
    warranty(3) = "no"
    techno(4) = "t1"
    dat(4) = "5/1/2003"
    item(4) = "tv 15"""
    repairdat(4) = "6/1/2003"
    warranty(4) = "yes"
    techno(5) = "t1"
    dat(5) = "6/2/2003"
    item(5) = "tv 16"""
    repairdat(5) = Empty
    warranty(5) = "no"
    For i = 1 To 5
        For n = 1 To colwidth(i)
            fmt(i) = fmt(i) + "@"
        Next n
    Next i
    Picture1.Print Format("techno", fmt(1));
    Picture1.Print Format("date", fmt(2));
    Picture1.Print Format("item", fmt(3));
    Picture1.Print Format("repairdate", fmt(4));
    Picture1.Print Format("warranty", fmt(5))
    For i = 1 To 5
        Picture1.Print Format(techno(i), fmt(1));
        Picture1.Print Format(dat(i), fmt(2));
        Picture1.Print Format(item(i), fmt(3));
        If repairdat(i) = Empty Then
            Picture1.Print Format(" ", fmt(4));
            Picture1.Print Format(repairdat(i), fmt(4));
        End If
        Picture1.Print Format(warranty(i), fmt(5));
        Picture1.Print Chr(13);
    Next i
End Sub


Assisted Solution

simon780 earned 160 total points
ID: 8031547
forgot to say you must use Courier font in order to put the data in formated style
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 8031566
forgot to say you must use Courier font in order to put the data in formated style

Expert Comment

ID: 8031572
The below codes determine if today is the end of the month
    If Format(Date + 1, "d") = 1 Then
        'print your report
    End If

Author Comment

ID: 8039409
what i mean is if my data or record at database ....

Expert Comment

ID: 8901054
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
LVL 49

Expert Comment

ID: 8983512
Moderator, my recommended disposition is:

    Split points between: ryancys and simon780

DanRollins -- EE database cleanup volunteer

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question