Solved

Reports do not load records peoperly

Posted on 2011-09-26
33
276 Views
Last Modified: 2013-11-27
I feel like I am digging myself into a deep hole.  I have created a db that keeps track of repair service that is performed on customer vehicles. However, my queries are not giving an accurate portrayal of customer invoices.

If you click frmMenuMaster and then click “Customers – Add/Edit” you will bring up the form that keeps track of customer contact data and the customer’s vehicles. If you click a vehicle and then click “Print Repair Order” you will bring up a form that should display all of the repair orders for that particular vehicle. You will note that if you go to Jerry Spencer, you will see that none of his vehicles are displayed, despite the fact that he has a number of repair orders However, if you close frmCustomerContactData and go back to the “Switch Board – Main” and click “View Customer Invoices”, a report is opened that displays customer name, vehicle and date of repair for vehicles that have had a service performed on them.  The first seven records appear to be information relative to invoices for services performed on George Hopkins’ vehicles; however, records 60, 66, and 67 belong to Jerry Spencer, which you will be able to see if you click one of the record numbers and click “Open Invoice”. And records 53, 58 and 68 belong to David Robbins. Consequently, although George Hopkins appears to have 7 invoices for vehicles repaired, he only has one: Record 59. And although David Robbins only appears to have three invoices, he really has 6.

This has been baffling me for quite some time so I hope that I can put it to rest with this question.

Thank you for your help in advance.

r/David
Independent-Auto---EE---9-26-201.zip
0
Comment
Question by:yddadsjd95
  • 17
  • 11
  • 3
  • +2
33 Comments
 
LVL 7

Expert Comment

by:shaydie
ID: 36621176
You have customer ID's in both your vehicles table and jobs table and they are different values. So for each job you have, you have a vehicle ID which has a customer ID and you may have a different customer ID stored in the Job record. In the underlying query for this form you are pulling the customer ID from the vehicles table, if you pull it from the jobs table your results would be different. In the vehicles table, vehicle 5 belongs to customer 4. In the jobs table the job with vehicle ID 5 lists customer 1. Why you have the customer ID stored in both places and why are they different, I don't know, but that looks like the cause of your problem. I would take a look at the process you use to enter the job records.. also I would probably get rid of the customer ID in the jobs table and just pull it from the vehicles table.
 relationships data
0
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 36621522
The issue is in your ResourceSource for frmRepairOrder-Select4Report2. Change your SQL from:

SELECT tblVehicles.CustomerID, tblMasterCustomer.LastName, tblMasterCustomer.FirstName, tblVehicles.ModYear, tblVehicles.MakeID, tblMakes.Make, tblVehicles.ModelID, tblModels.Model, tblJobs.RepairDateIn, tblJobs.VehicleID, tblJobs.JobID, tblJobs.JobTypeID, tblJobs.PartID FROM tblMasterCustomer INNER JOIN (tblModels INNER JOIN (tblMakes INNER JOIN (tblVehicles INNER JOIN tblJobs ON tblVehicles.VehicleID=tblJobs.VehicleID) ON tblMakes.MakeID=tblVehicles.MakeID) ON tblModels.ModelID=tblVehicles.ModelID) ON tblMasterCustomer.CustomerID=tblVehicles.CustomerID ORDER BY tblMasterCustomer.LastName, tblMasterCustomer.FirstName, tblJobs.RepairDateIn;

to:

SELECT tblVehicles.CustomerID, tblMasterCustomer.LastName, tblMasterCustomer.FirstName, tblVehicles.ModYear, tblVehicles.MakeID, tblMakes.Make, tblVehicles.ModelID, tblModels.Model, tblJobs.RepairDateIn, tblJobs.VehicleID, tblJobs.JobID, tblJobs.JobTypeID, tblJobs.PartID
FROM (tblMakes RIGHT JOIN (tblMasterCustomer INNER JOIN (tblModels RIGHT JOIN tblVehicles ON tblModels.ModelID = tblVehicles.ModelID) ON tblMasterCustomer.CustomerID = tblVehicles.CustomerID) ON tblMakes.MakeID = tblVehicles.MakeID) LEFT JOIN tblJobs ON tblVehicles.VehicleID = tblJobs.VehicleID
ORDER BY tblMasterCustomer.LastName, tblMasterCustomer.FirstName, tblJobs.RepairDateIn;

and you'll see Jerry's cars. The issue is with your data integrity rules. In the first query you require make, model, and at least one Job for each Vehicle. The second query returns each vehicle regardless if it has a make, model, or job using LEFT and RIGHT joins.

Hope this helps,

MV
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36681135
<I feel like I am digging myself into a deep hole. >
I advised you from the beginning that something like this would be difficult if you are new to MS Access database design...

As I said, for something as specific/complex as car repair shop managenment, most business will simply buy a package, not make their own.
(Whey reinvent the wheel?)

An off-the-shelf product will often contain:
Support (In-line, web based, and phone)
Updates
Web interfaces
Auditing
Support for all versions of Windows
Security
Support for multiple users
Interface connections for HR, Tax, Accounting, Inventory, ...etc
...Will your db offer these features?

A business will often ask an associate if they can design a software system, because "we just need something simple".
IMHO, there is no such thing as a "simple" auto repair shop system.
As mentioned, these systems will often be called upon to interface with Payroll, Accounting, Inventory and HR systems.
Again, will your product do this?

So unless this system is *Very* basic (which apparently it is not)
It is typically cheaper for a business to buy a product and be up and running in a few days, ...and have all of the above options, ...than to pay a developer to build a similar system.

The last thing you want is for the client to say:
My competitor uses XXXXX it has more features, and it cost less than the cost of hiring you.

I am not trying to say that you should not peruse this.
The kicker here is that if you are not very experienced with Access, and a serious design issue (Bug) crops up, will you be able to resolve it quickly on your own?
If this issue/bug causes you client to lose money, will you be liable?
Do you have a verbal agreement with the business, or is there some legal agreement?
...etc

As you can see, I have been more than willing to help you with your questions here.
However, remember, anything we recommend here is based on our very limited knowledge of the full scope of this system.
One Expert's recommendation for one thing may cause unforeseen issues in another area of the app.
Prompting you to post even more questions (...ad ad infinitum...)

Finally note that all of this is even before we discuss the complex issues of:
Multiple users
Security
Installation/Distribution (MDE, Accde, runtime, ...etc)
Support form multiple versions of Access/Windows...
...etc

I don't know how far along this app is, in terms of development, ... I just though I would reiterate my initial thoughts FWIW

JeffCoachman
0
 

Author Comment

by:yddadsjd95
ID: 36686384
Thanks Jeff, you were right the first time and even now your suggestion hits home even more. However, there are a few things that are driving this application: 1. For reasons unknown to me, the owner of the shop is not renewing his current system; 2) Me and the owner are trading services for services on this app; and 3) I take these occasions to improve my limited programming skills

I do not program professionally, but picked up designing db under the now defunct dBase4 app. I do not want to cheapen the profession by calling myself a shade tree mechanic, no pun intended, but that's about what I am. I occasionally take on projects to try and enhance my limited skills, so every few years, I'll work on building an app for someone. I have a few apps out there that has made a few people very happy. Once I exterminate this bug, and assuming none others crop up as a result of fixing this one, the app will be ready for the owner to begin testing it by entering some data, and all I will be doing is creating a few management reports from there.

Programming 101 taught me that one must first ask the question "Is creating an application the most efficient way to go about the problem?" And in this case, the answer is probably "Yes" after taking a deep breath.

Certainly Jeff, when this app is completed you will have played a big part in getting it in the customer's hands. I am grateful for the assistance and the wisdom that you have parted my way. But I would still like to get this problem resolved :-).

r/David
0
 

Author Comment

by:yddadsjd95
ID: 36692436
Shaydie, you seem to have put me on the right track. By making your change, I see Jerry's name, but it still shows George's Mercedes, but when I open the invoice, Jerry's car, the BMW, is there.. I'll keep working on it.

MV, when I changed the SQL, Jerry showed up, but the JobID was blank for each record.

Thanks for your help. It appears that I am almost there.

r/David
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36707682
Dave,

No sweat, always glad to help.

To be clear, my post above was meant as a way for you to "protect yourself" from getting screwed.
It was not meant to say that you should not take on new challenges and improve your skills.

Only because it's happened to me a few times when I started out.
A client will want something "simple" because there current program "costs too much"
(read: they didn't pay the bill and the software vendor dropped them... ;-))
The catch is that the time, energy and Money that they have to invest to get something custom, will typically exceed what the cost of an off-the-shelf product would be.

So, if you are not being paid "incrementally", the client may come to you and say: "I don't think this is worth it", ...and refuse to pay you.

In this case the only solace you can take is having increased your skill level.
So in that regard, perhaps it would have been a worthwhile endeavor...

For example:
a lot of times here, I will spend a large amount of time researching an issue and creating a sample, only to have the OP accept another post.  
No big deal...Just because I spent a lot of time on a solution does not mean it will be the best solution.
However the experience I get researching the issues (and learning new things) will transcend any points I could have gotten.
;-)

In any event, I am not seeking any points for my posts, just FYI's.

I'll back out now and let you continue with mvasilevsky to avoid confusion.

;-)

Jeff
0
 

Author Comment

by:yddadsjd95
ID: 36709251
Jeff, What I love about the EE is that I get the benefit of other experts' ability. I had an issue concerning a filter that I created that was not filtering the records properly. Although one of the experts gave me a solution to my problem, another expert, NIck67 (I think), came by after the question was resolved and spent quite a bit of time making additional suggestions to me regarding my naming convention. Since the initial issue had been resolved, I could only offer Nick67 a hearty "Thank you!" The point that I am making is that experts such as yourself do more hand-holding than they really need to to assist programmers such as myself.

I appreciate the knowledge that you have been passing along to me since 2007.

I often wonder if the EE has a protocol on how to allocate points, because as you said, some experts come by and I can tell that they've spent a lot of time working out a solution for me, and then I see that someone else's solution works better, this is why I always attempt to try each solution that comes my way, so if they both/all work, I can split the points.

I think if the EE would consider adding post solution points to questions, like perhaps a maximum of 200, it could motivate other experts to do what Nick67 did for me in the post-answer stage.

Your words were spoken like the true Genius that you are :-).

Thanks again,

David
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36710219
I can only speak for myself.
But most top experts here really don't care about the points.

The one thing you want to avoid is giving an expert points just for "effort".
This issue there is that if the post does not lead  (at least indirectly) to the solution, it is confusing to other members searching for the same issue, why you selected it.

Again, you win some, you lose some, it all comes out in the wash.

I mean consider all the time I am spending here, and I still don't want any points...!
;-)

If you are feeling particular grateful, just hoot me a "Thank You' email...
;-)

So before mvasilevsky loses interest, I'll back out now and let you continue on with him

;-)

Jeff
0
 

Author Comment

by:yddadsjd95
ID: 36711453
Okay, I'm inching along. After making the change that shaydie suggested, I discovered that I had the number 5 in as a default value to VehicleID...not sure why that was, but it appears to have been part of the culprit for showing the George's Mercedes also belonged to David and Jerry at the same time. I ran the query and made the changes to Jerry's records and to David's records. However, when I attempt to add a new repair order I get the following error message:

Run-time error '3101':
The Microsoft Office Access database engine cannot find a record in the table 'tblVehicles' with key matching field(s) 'VehicleID'.

this is whether it is a repair order of a vehicle that has already been in the shop for repair, or a new vehicle that has not been repaired before.

Thank you in advance for your assistance.

r/David
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36712766
A refresher in data normalization as well as naming conventions may be in order.
The #1 site for that expired two days ago--which is a horror.
Here's the pdf from that site though.

Now, your problems are with how you are thinking about your data.
You have customers and you have vehicles
You are thinking that customers own vehicles--but that is not relevant to you
Customers pay bills.  Bills are generated for repairs to vehicle

Don't relate the customer to the vehicle directly.
What if they sell it?
What if one customer pays for another customer's (his kid's) bill?
Makes a mess--so don't do it.
Go to your Relationships and kill the one between customer and vehicle
And root VehicleID out of the customer table too--it shouldn't be there

Look at the bills paid for a vehicle
Look at bills paid by a customer
There is no need for you to directly relate customer to vehicle, they relate indirectly through the bill

Now,
That print repair order button.
It's supposed to bring up bills from a certain vehicle
    stLinkCriteria = "[CustomerID]=" & Me![CustomerID] & " AND [MakeID] = " & [Forms]!frmCustomerContactData!frmCustomerVehiclesSub.Form!MakeID
I don't see ANYTHING in here related to a VehicleID--which is the only thing that should be in it
Try
stLinkCriteria = "[VehicleID]=" & Me!frmCustomerVehiclesSub.Form!VehicleID
Then it works like you meant it to--showing the orders for the vehicle selected in the subform

Start there, while I look at more stuff


Normal-Forms-nf3.pdf
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36712794
Root the CustomerID out of tblVehicles too.
That doesn't belong there
A JOB has a customer and a vehicle
Customers don't have vehicles and vehicles don't have customers --for you
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36712990
Then your sample data is all messy
Look in tblJobs
Look at VehicleID = 5
CustomerID = 1 ,3 and 4 have paid the bill for that vehicle!

No wonder looking at the forms and reports gets confusing!

Here's your app back with frmMenuMaster tweaked.
I killed the unhappy relationship, but not the two bad fields
Create some nice sample data in it, and then repost it
auto.zip
0
 

Author Comment

by:yddadsjd95
ID: 36713219
Thank you Nick67. I'm on it, and I will study the information on data normalization.
0
 

Author Comment

by:yddadsjd95
ID: 36713299
Nick67, before I start entering new sample data, I have one question to ask: Should I delete all of the repair records in the db before entering the new sample data? I am asking the question, because, although we deleted the relationship between customers and vehicles, George's Mercedes is still looking as if it is owned by three people.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36713505
I'd wipe out all the data and then do a Compact & Repair.
That resets all the autonumbers to 1 as well, when the tables are empty.

In Access, you have three main stages to things
There's the data design, creating the tables--which you are well underway with.
The only bad one I can still foresee is Parts prices.
If something costs $x today but $y tomorrow, how do you handle that so that all of the old invoices don't reflect the new price, but what was charged at the time?
That can get ugly.

Next is data entry, creating forms to put stuff in.
Basically think about creating a form for each table FIRST
A form to enter customers, one to enter vehicles, one to enter in parts, and so on
Once you play with those, you'll get a feel for how they're going to relate on the next part...
Then you need the complex one--the one to start and display jobs--and it will have subforms.
Get your 'initial' forms built and tested, and make sure that individually they populate the tables the way you need them to
Worry about the complex form afterward.

Finally, there is data retrieval, creating forms and reports to SEE what you've got in the tables.
You're not there yet, so we won't borrow that trouble at the moment.

Lastly, there's making it pretty.
Worry about THAT after making it functional
0
 

Author Comment

by:yddadsjd95
ID: 36714258
Nick67, If I understood you correctly, I deleted the relationship between the vehicles and the customers on CustomerID. I used CustomerID to link the vehicles to each customer in the subform in frmCustomerContactData, my customer contact data form. The subform in frmCustomerContactData, frmCustomerVehiclesSub, is now no longer linked by CustomerID to the parent form. How do I get around this without restablishing the CustomerID link between customers and vehicles?

Now frmCustomerVehiclesSub is displaying weird data. Instead of David owning 3 vehicles - Lexus, GMC and Chevrolet, the subform now shows 6 vehicles - 2 Lexus, GMC and 3 Mercedes, which belong to George. And instead of Jerry owning a Very Berry and a BMW, Georges Mecedes (the SDL 350) shows up 3 times in his record. And instead of George owning 2 Mercedes, a SDL350 and an E300, he only owns the SDL350 now.

Thoughts?

r/David
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 26

Expert Comment

by:Nick67
ID: 36714430
You must NOT think <George owning 2 Mercedes>
What jobs did George pay the bill on?
What jobs had a Mercedes?
Those are the questions relevant to you.

When you get down to it, that form will need to be scrapped, or reworked extensively.
Jobs are the lynchpin of everything--not customers and vehicles
So it will be hard to create a form that relates customers and vehicles--because they aren't related directly.
And when you work your way through the data structure, you may find you don't need that form

First things first
Forms to add data to individual tables.
Then a form to create and display all the stuff related to a job.

Stop thinking about who owns what vehicle.
It's not relevant
Who paid the bill for a job done on a certain vehicle.
That matters
0
 

Author Comment

by:yddadsjd95
ID: 36815044
OK Nick67. I've deleted all of the customer data and vehicle data and then I did a compact and repair, and a compile. I put in fresh customer data and fresh vehicle data, and I did not link them by CustomerID. I would think that this is the data that will feed the Repair Order form (frmJob) that needs to be modified or scrapped and redone.

When I finally see how customers and vehicles will be linked, I suspect that it will hit me like E=MC(squared) did when I first saw it :-).

Thanks for your assistance thus far.

r/David
0
 

Author Comment

by:yddadsjd95
ID: 36815059
Oops here is the file!
Independent-Auto---EE---9-26-201.zip
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36816369
You've already seen it
They are always linked through a job, and only through jobs
relation
Very quick, very dirty, look at the new frmJobs
Those combo boxes add a customer and a vehicle to a job
Jobs is not likely ever to be a subform/report.
It's your main thing


Independent-Auto---EE---9-26-201.zip
0
 

Author Comment

by:yddadsjd95
ID: 36890779
Thank you Nick67, I think I am beginning to see the logic of this, but each job will have parts (oil, oil filter, air filter, etc.) and/or service (change oil, rotate tires, service air conditioning). This is why I had a parts subform and a service subform in my initial frmJob. I did a slight rework of frmJob: I split the one subform that showed customer data and vehicle info into two separate subforms. It seemed to work when I opened it to view an invoice. Here is my challenge right now - whether I use your form, frmJobs (with an 's') or my form, frmJob, I do not see how to create a new repair order that ties the job, the customer and the vehicle.

I looked at your screenshot of qryJobOrder, but I do not know how to use this query to create a new repair order.

Thanks for what you've done thus far.

r/David
0
 

Author Comment

by:yddadsjd95
ID: 36890793
Nick67, I do see now that you have placed two combo boxes that permit me to pull up a customer and a vehicle. Let me work with this and I'll keep you posted.

r/David
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36892369
You recall that I had advised you to create a form that would enter data into each table?
Each table that relates to a job will have a JobID foreign key in it
Drop a command button on the frmJobs
Use the wizard
Tell it to open a form (say parts used)
Tell it you want to display specific data
Highlight JobID in both and click the two headed arrow
Done!

Do that for all the forms you created that realte to JobID
I've done two on frmJobs, quick and dirty, for you to get a feel for that method
Independent-Auto---EE---9-26-201.zip
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36892373
<create a form that would enter data into each table>
Actually one form for each table
0
 

Author Comment

by:yddadsjd95
ID: 36892442
Hahahahahah. It is making more sense to me now. I'm working on it.

Thanks!

r/David
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36892529
Now,
I have put the combobox on the Jobs form for CustomerID and VehicleID
I put command buttons to separate forms on it for two more.
Then there are subforms.

All three are in some ways interchangeable, depending on how many records and values you need to see.
There's only one customer and vehicle per job, so a combobox was a reasonable UI
Parts and scope are more involved, so bring up a separate form made sense
If you want to SEE the data on the main form, then a subform makes sense.
0
 

Author Comment

by:yddadsjd95
ID: 36893052
Nick67, You have been a lifesaver. I am placing the data for parts and scope in a subform, because my parent form has a tally column that adds the cost of parts and labor. It just seems easier to do calculate those objects from a subform. It appears that the parts subform is working; the scope subform is working, and the customer subform is working (whether creating a new repair order or viewing one that has already been created.) However,  I am having trouble with the vehicle subform. The combo box that I created is not updating. In fact, I copied yours and pasted it into the form after I couldn't get any success working on the one I created, and I didn't work, but I will ask this under a new question and once I get the solution, I'll put in a few records and see what it does.

Thanks again.

r/David
0
 

Author Comment

by:yddadsjd95
ID: 36897564
Nick67, I've not received much assistance from my most recent question, but as I am working through this, it could be in the way I have laid out the table relationships in the relationship pane.

Should customers and jobs be joined through JobID or Customer ID?
Should vehicles and jobs be joined through VehicleID or JobID
Should Parts Used be joined through PartUsedID or JobID
Should scope of work be joined through WorkRequestedID or JobID.

I did notice that when I join customers and jobs through CustomerID I could expand the customer rows in tblCustomers with the plus sign in the left column and see the JobIDs; however, when I join the two tables through JobID, I do not see CustomerID when I expand tblJobs.

Still scratching my head over this one.

r/David
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36897672
Join on JobID.  Almost every table should have JobID as its second field, right after the primary key
0
 

Author Comment

by:yddadsjd95
ID: 36899392
Nick67, I am making some progress this morning, but on my way to Church. It looks promising. I'll keep you posted.

r/David
0
 

Author Closing Comment

by:yddadsjd95
ID: 36964945
Nick67, Thanks for the remedial course in data normalization. I can't believe how long it took me to figure this out, although you handed the remedy to me on a silver platter. After redoing my entire database based on your suggestion, I still could not get the data in frmJobVehicle Sub to load. I finally added this line...

Forms!frmJob.Form!tblJob_CusVehicleID = Forms!frmCustomerContactData!frmCustomerVehiclesSub.Form!CustomerVehicleID

behind the button that created a new repair order and it did the trick. I have spent the last two days creating test records and going back to tblJobs to make certain that the records were being created properly.

Thank you a million times.

r/David
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36964973
You're welcome.
Hope you've got it completely figured out!
0
 

Author Comment

by:yddadsjd95
ID: 36965000
It took quite some time to see it, but I think I have it now.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now