Gamecommunity =GCHQ=
http://forum.gamecommunity.co.uk:8080/

Microsoft Access
http://forum.gamecommunity.co.uk:8080/viewtopic.php?f=6&t=46965		 Page 1 of 1
Author:  Caspius=GCHQ= [ Thu Nov 01, 2007 10:06 pm ]
Post subject:  Microsoft Access
Anyone here leet at Access databases, I'm talking about dynamic queries/searches across multiple fields level.

Mainly so I can pick someones brain for information, solutions to problems I get stuck at.

Casp
Author:  Ezer =GCHQ= [ Thu Nov 01, 2007 10:13 pm ]
Post subject: 
I used to teach it and do some VBA stuff but have got a bit out of practice the last 2-3 years, might be able to help.. :)
Author:  Holy Monkey [ Thu Nov 01, 2007 11:48 pm ]
Post subject: 
I might be able to help, what's the problem?
Author:  Caspius=GCHQ= [ Sat Nov 03, 2007 11:40 am ]
Post subject: 
At the moment I basicaly struggling with how to make a query that searchs how many records there are for each month in the current year and the current year.

Also making a multiple field query, similar to Googles advanced search but with five fields. :?

Anyway I'll be in touch.

Thanks. :D
Author:  Holy Monkey [ Sat Nov 03, 2007 3:15 pm ]
Post subject: 
Don't know the names of your tables or columns but it's not too difficult if you create your query in SQL view, I've done it in two queries for clarity:

SELECT count(*) FROM Table WHERE year(Date) = currentYear;

This will return the number of records there are for a given Year where 'Table' is the name of your table, 'date' is the name of your date column and 'currentYear' is the year you want to select (leave the lowercase year part as that is part of the query). You may need to include currentYear in ' ' depending on how the data is stored.

SELECT count(*) AS "No. Records", month(Date) AS "Month" FROM Table GROUP BY month(Date)

Again, fill in the names of your columns where appropriate, leaving the lowercase month in as it's part of the query.


This should work, but without having the DB right in front of me I can't really tell. If you run into any problems, let me know.
Author:  Caspius=GCHQ= [ Sat Nov 03, 2007 3:42 pm ]
Post subject: 
Holy Monkey =GCHQ= wrote:
Don't know the names of your tables or columns but it's not too difficult if you create your query in SQL view, I've done it in two queries for clarity:

SELECT count(*) FROM Table WHERE year(Date) = currentYear;

This will return the number of records there are for a given Year where 'Table' is the name of your table, 'date' is the name of your date column and 'currentYear' is the year you want to select (leave the lowercase year part as that is part of the query). You may need to include currentYear in ' ' depending on how the data is stored.

The second one is a bit trickier and I'll post it in a couple of minutes :P


This is the actual database, http://casp.gamecommunity.co.uk/database.gif I'm just working on the reports at the moment. Its just sussing out how you create sql quiries on the fly.

Thanks for your help anyway. 8)
Author:  Holy Monkey [ Sat Nov 03, 2007 3:48 pm ]
Post subject: 
Updated my post with the second query, dunno why I thought it was gonna be trickier! :roll:

The month query I included will return every month at once, if you want to seperate it you can just add a where clause to the end and change the number for each month.

SELECT count(*) AS "No. Records", month(Date) AS "Month" FROM Table WHERE month(Date) = 1



EDIT: Just noticed your post, that's a bit different to what I was thinking, mine was more back-end query it yourself kinda thing. It's probably easier from your point of view to use the Access query designer as I don't know how much manually inputted SQL Access can take.

If I was doing it through a SQL query I'd be declaring parameters for the WHERE clause and having the search fields input into the params. Like I said, might be easier through an Access design tool. Need to ask someone else about those though, sorry!
Author:  Caspius=GCHQ= [ Mon Nov 05, 2007 11:04 pm ]
Post subject: 
Well , with help from http://www.access-programmers.co.uk/index.html I sussed the first one out, the second is proving a little more challenging.

Code:
=DCount("[dt]","[tblMain]","[dt] between dateserial(Year(Date()),1,1) and dateserial(Year(Date()),1,31)")
8)

This is how to do one with preset criteria but how you integrate it into the form and pull the criteria from the text boxes I dont know.

Code:
SELECT tblMain.ID1, tblMain.Dt, tblMain.Dt, tblMain.Coverage
FROM tblMain
WHERE (((tblMain.Dt)=#5/10/2006#) AND ((tblMain.Dt)=#5/10/2007#) AND ((tblMain.Keyword) Like "*health*") AND ((tblMain.Coverage)="Balanced"));
Author:  Holy Monkey [ Tue Nov 06, 2007 1:03 am ]
Post subject: 
Again, this is more like SQL than Access:

-- Declare your variables
DECLARE beginDate as date
DECLARE endDate as date
DECLARE keyword as varchar(50)

-- pass the values from your fields into your variables
SET beginDate = dateField1
SET endDate = dateField2
SET keyword = keywordField

--Select your data
SELECT tblMain.ID1, tblMain.Dt, tblMain.Dt, tblMain.Coverage
FROM tblMain
WHERE tblMain.Keyword LIKE ''' + *keyword* + ''' AND tblMain.dt BETWEEN beginDate AND endDate



That won't actually work, but it's a start to show how you can pass your data straight into a piece of dynamic SQL. Tbh, you can probably pass your fields straight into the query without bothering about declaring variables. It just tends to be more extensible this way, especially if you are running multiple different types of query. Then you can just alter the variables rather than have to alter a potentially complex query.

EDIT: Btw, bit tired at the mo so that may be completely awful syntax...
Author:  Caspius=GCHQ= [ Wed Nov 07, 2007 9:06 pm ]
Post subject: 
Well I got everything working. This is the 'formulae' to filter the 5 fields.

Code:
SELECT tblMain.ID1, tblMain.Dt, tblMain.Tm, tblMain.Organisation, tblMain.Media, tblMain.ContactNo, tblMain.Email, tblMain.Keyword, tblMain.Enq, tblMain.CurrentStatus, tblMain.TeamLead, tblMain.Action, tblMain.Lead, tblMain.Statement, tblMain.Iname, tblMain.Iposition, tblMain.Itraining, tblMain.Coverage
FROM tblMain
WHERE ((([Coverage]=[forms]![frmSwitchboard]![TextCoverage] Or [forms]![frmSwitchboard]![TextCoverage] Is Null)=True) AND (([organisation]=[Forms]![frmSwitchboard]![TextOrg] Or [Forms]![frmSwitchboard]![TextOrg] Is Null)=True) AND (([Keyword] Like "*" & [Forms]![frmSwitchboard]![TextKey] & "*" Or [Forms]![frmSwitchboard]![TextKey] Is Null)=True) AND (([DT]>=[Forms]![frmSwitchboard]![TextDateFrom] Or [Forms]![frmSwitchboard]![TextDateFrom] Is Null)=True) AND (([DT]<=[Forms]![frmSwitchboard]![TextDateTo] Or [Forms]![frmSwitchboard]![TextDateTo] Is Null)=True));


The only thing I'm now having problems now with is the dates, I've tried 'between' and '>= or <=' but neither of them seem to be working and I'm not sure why. :?
Author:  happyslappy [ Wed Nov 07, 2007 10:25 pm ]
Post subject:  dates
are a pain , can you change them to a epoch so it miles easer ?
Author:  Caspius=GCHQ= [ Wed Nov 07, 2007 11:17 pm ]
Post subject:  Re: dates
happyslappy wrote:
are a pain , can you change them to a epoch so it miles easer ?


Totally. I've found out what the problem is, Access likes to work its dates as mm/dd/yyyy even though it displays them as dd/mm/yyyy. Just need to work out how to sort it out. :evil:
Author:  LeBeourfCurtaine [ Wed Nov 07, 2007 11:22 pm ]
Post subject: 
Why not just use SQL server...?
Page 1 of 1 All times are UTC [ DST ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/