Access Halp

Post by **Joose** » May 27th, 2010, 12:19

OK, so im building an Access database for something at werk. It doesnt have to be overly pretty, as im going to be the only one using it, its not "official" in any way.

Ive hit a bit of a problem though. My brain seems to have forcibly ejected all knowledge I ever had about databases. I cant remember large amounts of how the fuck this works. Im getting to grips with most of the basics again, but I cant for the life of me remember how to deal with this one problem.

Ive got two tables of data. One is full of information about devices, the other is full of information about engineer calls for those devices. All the devices have serial numbers and all the engineer calls have the serial number of the device they are against. All good so far. The trouble is, some of the serial numbers start with letters, and in the engineer table these letters are replaced with 0's. The resulting thing is still unique, but obviously doesnt match up any more. How the fuck do I make a query that takes information about, say, the devices location and match it up with the engineer calls, when the serial numbers are not *exactly* the same? Im buggered if I can remember.

Post by **Dr. kitteny berk** » May 27th, 2010, 12:27

... Got any jam?

amblin · Post by **amblin** » May 27th, 2010, 12:30

Post by **Joose** » May 27th, 2010, 12:46

Aaaah, new it was something simple. OK, that should work. I'll give it a go. Prepare for subsequent questions!

Post by **deject** » May 27th, 2010, 13:03

Sounds like a regex problem, in which case

have fun with that.

spoodie · Post by **spoodie** » May 27th, 2010, 13:11

First IIS and now Access, you're some kind of techno-masochist.

Post by **deject** » May 27th, 2010, 14:14

Maybe next he will start using Lotus Notes.

Post by **Joose** » May 27th, 2010, 15:26

Gah, for fucks sake. Im really wishing I paid more attention in the database lectures at uni now.

So, ive now got that working. Like you suggested, I made another query with an iif in it to match up proper serial numbers with buggered about with serial numbers, that works fine. What im now trying to do is get a nice little thing that shows the device details, with all the calls for that device underneath. My first thought (which appears to be backed up with what I have googled) was a form for the device with a subform at the bottom for the engineer calls. Trouble is, when I try to do that, Access shouts at me because the devices table and the engineer call table are not related. They *are*, but only through the query that sorts out the serial numbers. OMG WTF.

amblin · Post by **amblin** » May 27th, 2010, 16:27

Post by **FatherJack** » May 27th, 2010, 20:52

Without seeing the actual data, it sounds like you roughly need a make-table query (to save on constant reprocessing) which generates a superset of all the data with the serial numbers of the two table linked with perhaps a "contains" query, so that 2476892 matches ABCD2476892, stripping the leading zeroes by making the serial number field numeric.

Set the either or both of the "unique records only" properties of the make-table query until you end up with the right number of records, then use this ubertable to work with.