Access Query SOS!!!

[ADL]

Sorry for the dramatic title but last time I posted in here, you all were incredibly helpful and I need to call on that help again.

Right, I'll be the first to admit I'm not very well versed in databases (that was proved by my last question on here) but I have a problem that the boss is screaming about saying we need and I am at a loss as to how to do it despite trying all sorts of different things.

The database in question is designed to hold fleet vehicle lists for all our customers and all the times they have been inspected. As it stands, there are about 3000 vehicles and each need inspecting every month so we're dealing with a lot of data. The database has many tables but the two we need to concern ourselves with are tblVEHICLE and tblINSPECTION. As you'd imagine, the vehicle table holds a list of every single vehicle we're charged with looking after including Reg number, location and the frequency with which it gets inspected. The second table (inspection) contains every single inspection on these vehicles. So one inspection = one record. On this we have pretty much all the data ranging from the reg, to the dealer that inspected it to the inspection date. Because we look after fleets that span the county, and because all we can do is "request" inspections from independent dealers, we're kinda in their hands so it turns out that some vehicles have lots of inspections against them (the good dealers) whilst some have NEVER been inspected (the bad dealers). The query/report we need is to show all the vehicles in a particular fleet with the dates they have been inspected so that at a glance, we can see if they've been inspected in the last 30, 60 or 90 days.

Now then, I have tried everything and I just can't get it to work. My latest masterplan was to copy EVERY vehicle from the vehicle table to the inspection table and assign it an inspection date of 1980 so I could concentrate on the one table but I ran out of steam once I did this and I'm a bit lost. I'm sure it's possible. My boss, like anyone who doesn't understand anything like this thinks it's a simple 2 minute job so me struggling to do it makes him think I'm not trying.

Can anyone help me out? I'd owe you quite a few pints if you managed it.

Many thanks for any help and advice you can offer.

Adam

 

[GravyMonster]

First off, it'd be wroth noting that Access is in no way designed to hold 3000 vehicles * god knows how many inspections worth of data. If the database is over 10mb, move to a proper database engine such as MSSQL, Oracle or MySQL.

Could you give us a basic outline of your table structure so we can see how the data fits together for the query? It sounds like you'll need a subquery to get the last date of inspection, and then another query to check if that date is within the last 30/60/90 days.

 

[Dolph]

It's possible, you're just going about it wrong I'd imagine.

You've got the reg number in both tables, so this is going to be the key that links the two (it should already really, as a one to many relationship).

What you need to do is create a query that pulls all the inspection dates, and tthen summarizes them to give you the most recent inspection per car and then sort the results using the date criteria to give you a list of inspections that are overdue.

Basically, start by creating your select query containing reg numbers, inspection dates and location. Then (are you using access 2003 or 2007?) go to view, enable the totals column, set the reg number to 'group by' and the date to 'last' and it should give you a list of each car that has been inspected with their last inspection date. Use this as a base, and create additional queries for each location and date range (or use a form to pass details that you enter into it), and you should have exactly what you want.

To find the ones with no entry, create a find unmatched query using the wizard to find all the reg numbers in the tblvehicle table that have no entry in tblinspection.

Hope that helps some, anything else, let me know

 

[ADL]

Hi,

Sorry for the delay in the reply - only just managed to get on here.

Right - progress report! Firstly, you don't have to tell me about the size of the thing. What started out as a temporary measure is now the crutch the business uses to hobble alon....sorry, I mean operate professionally.

I took what Spunkey said on board, downloaded MySQL, and had a go at that but 3 hours later, 2 cups of coffee and a lot of desk slamming, I'm no further on so I abandoned that for a moment and tried a different approach. I'm using Access 2007 which may have a feature unique to it (or it might come with every Access!) that lets you "split" the database. If I do this, all the tables only make up around 2MB which leaves the (I'm embarrassed to admit to this), remaining 52MB made up of reports, queries and forms. Now I'm no expert (as if you hadn't already noticed) but that surely takes some of the strain away from the multple users of the database if they all have the big file saved locally?

With regard to the "Missing Vehicle" report, thanks to your help, I've now sorted it using the painfully easy "unmatched query" process as Dolph said. The 30 60 90 is proving more difficult however.

If I've understood correctly, then I've created a 306090 master query and then a 0-30 query which has the parameter "Between Date()-0 And Date()-30" based off the 30 60 90 master. Then a 30-60 query and a 60-90 query with the date parameters changed respectively. This gives me (in query form) every vehicle that has been inspected between those time periods. However, putting that in a report is proving difficult. As it stands, I have to open each query for whichever time period I want so putting it on a report would make it much easier. Whatever I try to do this though, doesn't work. If I try to do it using the query wizard then it says something about using queries and tables and just to use one instead.

Then, add to that, is there any way of including in these queries every vehicle that hasn't been seen rather than having it in separate reports? Am I asking too much from something as humble as Access? Am I being annoying? Am I being stupid?!

Whatever I'm being, even if we can't get this sorted, thanks very much for all your help as it's made the world of difference.

Thanks a lot,

Adam

 

[suarve]

Hi,

Sorry for the delay in the reply - only just managed to get on here.

Right - progress report! Firstly, you don't have to tell me about the size of the thing. What started out as a temporary measure is now the crutch the business uses to hobble alon....sorry, I mean operate professionally.

I took what Spunkey said on board, downloaded MySQL, and had a go at that but 3 hours later, 2 cups of coffee and a lot of desk slamming, I'm no further on so I abandoned that for a moment and tried a different approach. I'm using Access 2007 which may have a feature unique to it (or it might come with every Access!) that lets you "split" the database. If I do this, all the tables only make up around 2MB which leaves the (I'm embarrassed to admit to this), remaining 52MB made up of reports, queries and forms. Now I'm no expert (as if you hadn't already noticed) but that surely takes some of the strain away from the multple users of the database if they all have the big file saved locally?

With regard to the "Missing Vehicle" report, thanks to your help, I've now sorted it using the painfully easy "unmatched query" process as Dolph said. The 30 60 90 is proving more difficult however.

If I've understood correctly, then I've created a 306090 master query and then a 0-30 query which has the parameter "Between Date()-0 And Date()-30" based off the 30 60 90 master. Then a 30-60 query and a 60-90 query with the date parameters changed respectively. This gives me (in query form) every vehicle that has been inspected between those time periods. However, putting that in a report is proving difficult. As it stands, I have to open each query for whichever time period I want so putting it on a report would make it much easier. Whatever I try to do this though, doesn't work. If I try to do it using the query wizard then it says something about using queries and tables and just to use one instead.

Then, add to that, is there any way of including in these queries every vehicle that hasn't been seen rather than having it in separate reports? Am I asking too much from something as humble as Access? Am I being annoying? Am I being stupid?!

Whatever I'm being, even if we can't get this sorted, thanks very much for all your help as it's made the world of difference.

Thanks a lot,

Adam

Well firstly you should at least post up your table structure

I've never used the database splitter wizard in Access but I assume this is the same as moving all the tables to one database (E.g. the 'backend') and queries, reports etc to a seperate file that is l8inked to the prev file.

As for taking away stain on local users, in essence you're only saving them 2MB worth of data on the machine but getting the benefit of all users being able to update data at the same time (you'll always have the latest version of the tables in backend file). However, running queries against those linked tables will probably take longer and there is a limit of 255 simultaneous users accessing the backend file (if you have that many users).

I also notice you decided to use function's in your new query. Using them locally is ok, but across a network they are painfully slow and you should try to avoid using them (when we had a similar problem at work a few years ago i remember leaving a query open while i went to lunch).

All of what you're saying is probably possible using Access but it would be a total pain in ass to do and would run slowly. It sounds like a case where a proper database engine with some stored procedures should be used.

Still, post your table structure as you have me intersted now

 

[Dolph]

Hi,

Sorry for the delay in the reply - only just managed to get on here.

Right - progress report! Firstly, you don't have to tell me about the size of the thing. What started out as a temporary measure is now the crutch the business uses to hobble alon....sorry, I mean operate professionally.

I took what Spunkey said on board, downloaded MySQL, and had a go at that but 3 hours later, 2 cups of coffee and a lot of desk slamming, I'm no further on so I abandoned that for a moment and tried a different approach. I'm using Access 2007 which may have a feature unique to it (or it might come with every Access!) that lets you "split" the database. If I do this, all the tables only make up around 2MB which leaves the (I'm embarrassed to admit to this), remaining 52MB made up of reports, queries and forms. Now I'm no expert (as if you hadn't already noticed) but that surely takes some of the strain away from the multple users of the database if they all have the big file saved locally?

That's going to be a longer term project, and that's assuming that your boss is willing to approve it, there is a big difference between ideal and practical in the real world.

With regard to the "Missing Vehicle" report, thanks to your help, I've now sorted it using the painfully easy "unmatched query" process as Dolph said. The 30 60 90 is proving more difficult however.

No problem.

If I've understood correctly, then I've created a 306090 master query and then a 0-30 query which has the parameter "Between Date()-0 And Date()-30" based off the 30 60 90 master. Then a 30-60 query and a 60-90 query with the date parameters changed respectively. This gives me (in query form) every vehicle that has been inspected between those time periods. However, putting that in a report is proving difficult. As it stands, I have to open each query for whichever time period I want so putting it on a report would make it much easier. Whatever I try to do this though, doesn't work. If I try to do it using the query wizard then it says something about using queries and tables and just to use one instead.

What are you trying to do with the report? There's a few ways to deal with it, you can create a form that shows each record, you could put the lot on a spreadsheet or you can do further manipulation of them to get them into a more useful format. (if you want, for example, to create a spreadsheet to give you the numbers of vehicles overdue an inspection for each location, you could export the results to excel and create a pivot table based on it).

Then, add to that, is there any way of including in these queries every vehicle that hasn't been seen rather than having it in separate reports? Am I asking too much from something as humble as Access? Am I being annoying? Am I being stupid?!

Whatever I'm being, even if we can't get this sorted, thanks very much for all your help as it's made the world of difference.

Thanks a lot,

Adam

It can be done quite easily, but the best way to go about it depends on how you want to present the end results and for what purpose.

I agree with the need to post your tables, and also give us a better description of exactly what you need in the report and what purpose it's to serve.

 

[Participant]

Surely Access can handle this, as long as the relationships are set up correctly and the appropriate normalisation / data redundancy has been performed?