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

 

[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