Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2016
    Posts
    5

    Unanswered: Help with Report

    Good Day

    I need help with a report where i need 2 or more tables/queries.
    I have a simple Db which I am practicing on and want to create a report that looks like an Invoice. I,ve created a parent report from a query to get Customer details etc and a Sub report from a query with Billing Details.

    My problem is I can,t get the link to work, I have watched tutorials and as far as I can see done every thing correct. I know the problem is with the relationship or Master Link Field/Child Link Field I just cant,t work it out.

    It,s driving me crazy is there anyone out there willing to look at my Db and show what I am doing wrong.

    Regards
    Mike

  2. #2
    Join Date
    May 2016
    Posts
    89
    Provided Answers: 4
    Hiitsmemike

    Could you uoload your DB?

  3. #3
    Join Date
    Sep 2016
    Posts
    5

    Help with Report

    Hi Thanks for your reply, I have attached the Db for you to look at.

    I have been playing around with it now for a couple of days so there maybe a few things wrong.

    Feeling very frustrated so hoping you can point me in the right direction.
    Attached Files Attached Files

  4. #4
    Join Date
    May 2016
    Posts
    89
    Provided Answers: 4
    Hi

    First, I didnt' find any data in CustVehichleID field in workSchedule & billing table. So impossible to link a car to a billing or to workschedule record.

    And personaly, I will model lile this

    Conceptual & logical data model
    Click image for larger version. 

Name:	billing_cdm.jpg 
Views:	2 
Size:	65.6 KB 
ID:	17150 => Click image for larger version. 

Name:	billing_ldm.jpg 
Views:	2 
Size:	51.6 KB 
ID:	17151


    It means that your application will generate at first a bill with a status
    1. Quote
    2. Invoice
    3. Other ???


    On this invoice I'll link different reparations/actions

    If it's a quote and the client is Ok, you'll just change status for invoice

  5. #5
    Join Date
    Sep 2016
    Posts
    5

    Help with Report

    Hi, appreciate your reply and help.

    I like your suggestion for the layout and will have a go at it. First I need to sort out the missing ID,s.
    I have had this problem before so should have seen it, however I don,t know how it happened, any suggestion? As you can see from the amount of customers that I copied the tables from a previous Db I created and was just using it to practice some new techniques I have been learning. I assume that if I created a new Customer the ID,s would be there, need to test that.

    Would appreciate your comments on the following:

    1/ Do I have the PK and FK correct in each table
    2/ Is there an easy way to put the missing ID,s in the tables, maybe a query
    3/ Confused on why, if there are missing ID,s my forms work, for example, I can move from Customer Details to Work Schedule and then to Billing and the data is correct.

    Regards Mike

  6. #6
    Join Date
    May 2016
    Posts
    89
    Provided Answers: 4
    Hi

    I found error source. You use as FK PlateNo, not CustVehiculeId.=>A particular reason?
    You can correct your DB with a query, no problem !

    I noticed redundant FK in tblWorkSchedule. With CustVehiculeId, you don't need PlateNo.

    I'll try with PlateNo to generate an invoice;

    About report, the general idea is
    1. to use a query
    2. add the children records in the detail part
    3. work with grouping criteria (see on the report bottom)



    The approach is different between form and report. It's my feedback but perhaps some others could show us an example with a sub report!

    There is your DB with report Ok.

    I cannot explain you what I corrected so be patient and analyze my uploaded db and try to understand my corrections! Don't hesitate to consult websites help you to understand them!

    Take care I code with VBA under events

    I suggest you to take this opportunity to reconsider your User interfaces and their kinematic

    Bon courage






    Attached Files Attached Files
    Last edited by informer; 10-01-16 at 14:51.

  7. #7
    Join Date
    Sep 2016
    Posts
    5

    Help with Report

    Hi
    Thanks for your help,advice and suggestions they are much appreciated.

    I will go back a few steps and correct my errors, sometimes a fresh pair of eyes work wonders. I am angry with myself that I did not see the errors, now I know I need to work out what I did wrong when I copied the tables over.

    I have reduced the number of records so it will be easier to work with and correct any errors, later I will work out how to import all the records.

    Again many thanks and I will let you know my progress.

    Regards
    Mike

  8. #8
    Join Date
    May 2016
    Posts
    89
    Provided Answers: 4
    Hi itsmemike

    Your are welcome!

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •