Recent content by plog

You need another table---VacancyCandidate. Since a single vacancy can have multiple candidates and a single candidate can go to multiple vacancies you need a table to sort all that out. This is what that table looks like: VacancyCandidate vc_ID, autonumber, primary key vc_DateTime, datetime...

Words rarely working in describing how you want a query to work. Example data is the way to go. Give us two sets of data: A. Starting data from your data source. Include data source and field names and enough rows to demonstrate all cases B. Expected results from A. Show us what data...

You're asking us how to use duct tape to fix the wood putty that you used to fix your cracked foundation. Actually, that's not a great analogy because your root issue isn't as bad as a cracked foundation. It's actually really trivial to fix. Don't store this value, calculate it. You make one...

First, I don't think your products table is a products table, it's an inventory table (e.g. QuantityUnit, WHID). You are storing a ton of duplicate data that should go into an actual products table (BarCode, ProductType etc.)I think you do need a products table--one that just lists all the info...

Think we need more specifics. I have two hunches based on nothing you've said, just experience here: 1. Your math could be more efficient. 30 fields based on 15 inputs is a lot, and then you say that some calculations feed into other calculations downstream. Perhaps some of those...

You're printing out a value on the form, not a value from the record set.

"R915" doesn't look like a number to me. Are you using base 28 or more? In a table, you should store your values with the data type they need to be. If you will be doing math on data then you should store them as numbers. That means the "915" part goes into a field by itself as a number, and...

You can't have it both ways. Either you get a list of unique artists because the artist names are distinct. Or you get duplicate artist names because record_id/artist name is not distinct to artist.

Is 'artist_name' a field in qryRecord_Search_Form_record2? My guess is no, it's that horrible one with a slash and space in it.

Agree with MajP and my guess as to where it is going wrong is that you are counting gifts, not donors. [PY Gifts] is most likely a list of gifts which means if a donor gave multiple gifts they are showing up multiple times in that table and you are counting them each time they gave a gift...

With those database constraints I was wrong. Cheeky's solution will work. Sorry.

Fails if 2 bike/car records and no bike/car records. False positive. Fails if a bike record and a car record and then matching records (bike bike car, car car bike, car car bike bike). False negative.

Is your number 2 in response to my #2? If so you missed the point. Reread my explanation. tblCategoryTypes is a subset of tbl_ContactCategories. Every field in tblCategoryTypes is in tbl_ContactCategories. That's wrong.

Not that simple actually--but not too hard. Gonna take a subquery. There's a few ways to do this so I'm betting this turns into another pedantic coding contest. Here's my entry: SELECT ContactID, MAX([Category Type]) AS Type1, MAX([Category Type]) AS Type2 FROM tbl_Contact_Category WHERE...

The +1 needs to go to the interval not the dateofinspection.