Search results

If you query the table containing position ids and LEFT (OUTER) join the table with Positions and incumbents, you should get All the positions regardless of whether or not there is an incumbent. i.e. in SQL SELECT p.PositionID, i.incumbentID, i.name FROM positions AS p LEFT JOIN incumbents AS...

Lancruiser, I agree with Spike and yourself based on what you're saying that Access is probably not a bad starting point if not a perfectly acceptable solution. Especially if you've got a pre-built front end application that would save you re-inventing the wheel in preference to delivering a few...

Because I work with Access and VBA (more than I'd like), as well as other platforms. I also have customers who I deliberately gave an Access Database backend to because it was the best fit for what they needed. Access is limited, so is VBA, but they're great low price, simplified offerings that...

half an hour to add a table column? that's not a weakness of the database or ASP.net imo. horses for courses in many respects. What do you need from your system? Access to a point can be quicker for a simple database because it provides a reasonable framework (VBA/forms/reports etc) to knock up...

doh, think I misread part of the intention, the original examples threw me a bit. In which case you're right, a simple GROUP BY isn't going to give you the right answer. can you elaborate a little on what it is this output gives you?

Sounds like a complicated way of doing a GROUP BY and COUNT to me :D What you do with the GROUP BY depends on the operator you [don't] use. GROUP BY basically says to the database "lump everything in this table together where these columns contain the same values". What you do with that...

Access has some weird and arbitrary seeming preferences for queries generally, its bracketing rules will, eventually, drive you insane and there seems to be no set rule on when you can use inline SQL (sub) querying and when you have to farm something out to its own Query.

It sounds like a GROUP BY should do it SELECT type, group, count(typeID) FROM tblTypes GROUP BY type, group

Glad you got it sorted out. :) You can leave the alises on the output columns, but you need to test the actual column names in the CASE statement, not the alias.

Without More information difficult to suggest, I've tested using both bit and varchar() values and the sample works.

So what happens when you run it, what error message(s) do you get? There appear to be a number of typos/discrepencies in your SQL at various points (past the obvious :eek:at the structure of it) but without some context past it doesn't work it's hard to suggest more.

I don't believe you can filter in a JOIN like that in access. You'd have to create a sub query filtering on Owner and OwnerCol first and join that query.

if col1 etc contain string values: CASE WHEN col1 = 'True' OR col2 = 'True' OR Col3 = 'True' THEN 'True' ELSE 'False' END as Result If you're using bit columns then replace 'true' with 1 and 'false' with 0 as appropriate.

I'd be inclined to go straight to the directory. You could store what was in the directory at that time in a table, but if someone goes into that directory and changes things, what's in the table doesn't match what's in the directory. The File System Object is useful for this kind of file...

The hardware limitations can pose performance issues for sure. There are performance monitoring tools (not least perfmon for windows itself) that can give you a better idea of where your performance bottlenecks are, a good place to start is also rechecking and tuning the SQL for views/stored...

Saves repeating myself.

The principle still applies though. It's hard to give any more detailed a suggestion with the information you've provided. but if they're typing in the same stuff over and over again that suggests a normalisation issue. Why is copying and pasting info, at least, not an option?

If they're having to type the same things again and again it suggests that the table structure isn't normalised. I.e. Those common choices should be in their own table and the user can then pick them (or a short code representation) from a list or dropdown menu.

It will do because of: WHERE z.fldPkg=qryDCFForecasts.fldPkg; If there's no record in qryDCFForecasts (ie fldpkg is NULL) it'll be filtered by this as NULL cannot be equal to a value from z.fldPkG even if z.flgpkg is also NULL. WHERE z.fldPkg=qryDCFForecasts.fldPkg OR...

You can write the code in either, I tend to "push" data from Access into Excel because most of the actual donkey work is being done in Access but you could easily do it the other way round. Cells(1,1) simply means Row 1, Column 1 on the worksheet.