Why?

[Sam Summers]

This is like REALLY important but should also be straightforward?

All i want to do is to display either:

Only Trade1 Employees OR

Trade 2 Employees OR

Trade1 and Trade2 OR

Trade1 and 'Level' OR

Trade2 and 'Level' OR

Trade1, Trade2 and 'Level' OR

'Level'

All of these options must also have a 'MobDate' less than todays date or no date.

TradeName1, TradeName2 and Level are all comboboxes.

I have tried all sorts of permutations with this and its still not working?

I also get multiple displays of the same person displaying all Trades that they have?

Many thanks in advance if you can shed SOME light on this one?

 

[pr2-eugin]

This is like I have no clue what you are talking about, like really really no clue. Like what have you done so far? like have you really tried something? Like you can show us the query?

Take back one step, explain the structure of the form you have, what is the result you are trying to achieve, show us some sample data.

 

[Sam Summers]

Ok,

I have tried many things for the last two months.

I have started with one thing at a time and added, changed and tried different things.
I have searched online and tried other things but still not there.

I have a form with 3 comboboxes on it:

TradeName1
TradeName2
and 'Level'

i want to be able to select all or any combination of the 3 or any single one and show Employees with these skills or Level.

But all Employees have to be available i.e. not on a job hence the date parameter.

i am attaching a document of what the SQL for the query i currently have that is not working correctly.

 

[Galaxiom]

Sounds very much like your data is not correctly structured. Numbered repeating fields indicate a classic misconstruction.

The trades should be a single field in a related table.

I think you had better attach your database with a few anonymised records as examples.

 

[Sam Summers]

Thank you,

Did you see my SQL attached?

I have an 'Employee' Table
and 'EmpTrade' Table
and a 'Trade' Table

'EmpTrade' is an intermediate Table to give me a many to many relationship

 

[Galaxiom]

Sorry, too much Chardonnay to understand what you mean by "not working correctly".

 

[Sam Summers]

Can anyone help? Please

 

[plog]

You're going to have to give us a better idea of your database. Your first post contained a description of what you want which was very hard to follow because of all the or/and permutations. Your next post contained SQL which perfectly matched the description in your first post. And by perfectly matched I mean it too was hard to follow because of all the or/and permutations.

You are going to have to give us some sample data. Show starting data from your tables, then run us through a few example inputs and what data those inputs should produce based on that sample starting data.

 

[jdraw]

Sam,

Pretend we're a bunch of 10 year olds.We don't know you or your issue or the subject matter you are dealing with.

Start at the beginning --simple plain English - the old who,what, where, when, why, how much and how often.

Once reader get some idea of what your dealing with, I'm sure options/recommendations will follow.

 

[Sam Summers]

Thank you very much for replying,

I have 3 comboboxes, TradeName1, TradeName2 and Level

TradeName1 and 2 look up values from a table while Level has a value list I, II, and III

TradeName1 and 2 are working fine but Level makes no difference as it displays all levels.

Basically I want to display records matching:

Either 1 or 2

Or

1 and 2

Or 1, 2 and Level

Once the selection has been made the user clicks on a button 'find matches' which displays a form based on the query.

As I said, its only 'Level' that does nothing?

 

[plog]

I disagree with jdraw. English isn't going to help, we need data. Post sample data from your tables, then run a few scenarios and show us what data should be returned.

 

[jdraw]

???How are the combos related? What exactly does this mean

Once the selection has been made the user clicks on a button 'find matches' which displays a form based on the query.

Selection of what??

Sam, this will be quicker if you tell us exactly step by step by step- or send us a copy of the database.

We only know what you tell us and then we guess what you mean or where it fits. More detail re the process involved.

 

[Sam Summers]

Thank you guys,

Hopefully this will make it clearer?

 

[Mihail]

As Galaxiom already said, your data is not correct structured.
Because you are lucky to have a very skilled guys from this forum here, I'll not comment. Just I converted your DB in A2003 version and I added all the tables to the Relationships window.

 

[Sam Summers]

OK, thank you.

I thought this would be simple but it doesn't appear so?
I think the best thing i can do is delete the 'Level' option completely seeing as it doesn't work.

Thanks for your help guys

Sam