Search results

My two cents worth - You never know the future! if the two companies merge in the future (it happened to me 3 times) it will be MUCH easier if the data is in 1 database (just update Company number & hey, presto - data merged) Doing it from seperate databases will be a LOT more time & effort

Don't lose heart, we are competing with fully trained sql / vba programmers trying to pick up what we can from incomrehensible help screens and not much of an idea of what can be achieved with wizards (if only somebody would explain in English, what it all means) Keep battling & exploring...

It's frustrating, you know exactly what you want to do, but the rules of Access stops you doing it. But I guess it makes sense, if you do a Group By, you could get 1000 results that meet the criteria, how is Access to know exactly which of these records you want to update

The only way I know is to append the calculated data into a temporary table and then Update from there

Thanks for that. In a moment of 'inspired genius' I was able to meet the requirements and got down to 4 nested queries (down from about 10!) using a combination of count and a few search options

The only way I know is to append the results of the grouping query into a table, then extract it into the queries run in the macro

A fully 'automated' system is what you are after and should be possible, although it will require VBA skills I don't possess. Why it doesn't work as such in the model supplied is an Access system thing where to find the highest rating employee, there is a Group By query to finf the Max rating...

Curious about macro not working (double checked & works OK for me) It just runs the Append qry and then the Update qry - you could run these manually & see if you get error Is it possible for Access to assign a true value to a check box with a query or as part of a query expression? Not sure...

This is cumbersome, but at least should show the principle - open the 2qryEmployeeAllocation which selects the most appropriate employee per position, the run the Allocate macro, which then updates the Allocated checkbox Run 2qryEmployeeAllocation and the next most appropriate employee is...

This sounds like a classic case of the blind leading the blind, but I'll give it a go

Given the nature of what you are designing your database for, I don't think my solution is advanced enough. It is more a technique that could work and ensure your tables have all the tools necessary to be able to obtain your goal. Ideally, it would be VBA code with loops to run through the...

I am thinking just as a yes/no box - the employee is allocated or not

Can you post your database so I can see your data? This will make it easier to explain - bear in mind, my expertise in VBA coding is virtually non existant. If it were me, I would create a series of action queries and use a macro to run them in sequence - True experts will frown on this...

If the criteria of the query is Allocated = False, and after allocation, it is updated to True, then on the next cycle, this employee would not be available for Allocation to another position

A checkbox in the employee table to indicate Allocated?

Thanks for this, I will investigate. Database attached - To explain, all info required is in this one table, and objective is: For AnimalGroup=1 on any given SightDate, were any 2 (or 3 or 4 etc) Species (so could be 1 and 2, or 1 and 3 or 4 and 5 etc) Sighted>1 (1 = 0 sighted) for a given lake (sl)

Thanks for the tips, but maybe I'm not clear enough in my explanation. What I'm after is eg any 2 of the species are found Just using standard queries, I always end up with way too much data, or way too many queries

Scenario: A group of 6 (but can grow) animal species with records giving results of sightings. Requirement: Search records and find where any 1, 2, 3, 4, 5 or 6 of these species where found. I have a solution - a whole stack of individual queries which are combined to give the report. This...

I think you have answered your own question: I import the schedule from the time clock and I want to run reports from this. If the primary purpose of the database is to report on the schedule, that should be your primary table. You can have many Departments, Employess etc but only 1 Schedule

Problem resolved, although cause and solution a bit of a mystery. Imported objects into new database and all now works correctly. We had done this previously, although I suspect it works now because of Trusted Location established this time