Quering With Relationships And Finding Only The Last Event

Jun 28, 2005

hi guys, i have a table that stores information about packages

[autonumber],[pack number], [county], [uber number] ( the uber number is made up of the pack number and the county ), etc

and i have a history table so that when anything is done with a package it is recorded in the history.

[autonumber],[uber number],[event],[date],[user]

they are linked by the [uber number] with a one to many relationship so that is shows all the events for a specific pack.

but now i need to create a list of all the packs that are at certain events

but if i just create a query with the packs info and the event number from the history i get all the packs that have been at that state and not just the packs that are at that state.

how can i accomplish this?

best regards

stuart

View Replies


ADVERTISEMENT

Finding A Minimum Time For A Swimming Event

Feb 21, 2006

I am trying to produce a database that will select a swimmers, best time for each event, and will select the overall best time done by any swimmer in the events.

At the moment I am having some trouble with my queries.

When trying to get the query to select a swimmers best time for the events, It will work when selecting min for the time field and including the following fields:
Forename
Surname
Stroke
Distance
Age group
Gender

However I also wish to include the name of the swimming pool the time was done at and the date at which it was achieved. My problem is that when I add these fields into the query, it looks for the swimmers minimum time in the event, on the date, and at the venue and consequently comes up with all the times the swimmer has done, rather than just the best ones.

I would Really appreciated if anyone had any suggestions as to how I can get over this problem

Thanks in advance

Jennifer

View 14 Replies View Related

Quering Based On Distinct Values

Aug 24, 2005

Hi all,

I had table with following data

Table

f1 f2 f3
1 10 AB
1 11 WW
2 10 DD
2 11 DD

f3 is memo field

I had to retrieve data by grouping records based on f1 value

and i had to display f3 value as "-" if all f3 values for a f1 are distinct.
If all f3 values contains the same value then i should display that value.

I should get output like this

f1 f3
1 -
2 DD


I tried a lot by taking the distinct count of the records and displaying - when count is > 1

but i am not able to succeed.


please any one give the solution.

waiting for your help.


Thanks

View 2 Replies View Related

Quering On A Portion Of Data In A Field

Jan 29, 2007

Hi All:

I have no idea how to start this task.

Table 1:
Students that are registered this semester. The unique Identifier = full 9
digits of SS#'s

Table 2:
Students and regular people current working for the college. Unique identifer
= only the last 4 digits of their SS#'s

Objective:
I need to know what registerd students or regular people are currently
working in the college.
In other words, compare table 1 and table 2. The problem is I do not have a
common unique identifier in both tables.

Additional Info:
Each table also has:
* Last_Name
* First_Name

I was thinking many do a DUAL comparison...Let's say compare the last four
digits numbers and the last and first name from table 2 vs the last four
digits and the last and first name from table 1.

Can this be done?

Thank you very much in advance for any ideas.

MRZ

View 2 Replies View Related

Finding Help?

Aug 21, 2007

Hi all:)

I am looking for some advise, I work for a small service company and we our trying to computerize our service jobs through the use of access 2007, We have completed 80% of the database but are having a few problems completing, for example the search function on some forms etc, I have searched the web for help and have posted the problem on this forum a number of times but I am still unable to get it to work myself as I am new to access , the problem is I need the database completed asap before we take on a bigger contract and I am running out of time, so I was wondering if anyone knew of anyone / or any company out there that could have a look at the search form, I would obviously pay for it, I have tried to approach a few companies but they will not take on such a small job, they only want to take on the development of the entire database?
Anyone know of anyone?

Jackie :)

View 2 Replies View Related

Finding #

Nov 4, 2005

I'm searching through some text fields like the following:
Ann12 #1
Ann12 #2
Ann12 #3
Ann1 #12
Ann2 #14
ect...
I'm doing a like query
Like "*#*2*"
However, the # is the keyword for Date, so what do I use instead?

View 1 Replies View Related

Finding ABC

Aug 17, 2006

Hi

I hope someone can help with this as I can't seem to get this query to work.

I am trying to use a query to search for the highest value in a column but based upon a specific criteria.

i.e. in the column will be ABC1234, ABC1235, ABC1236 and BBC1234 and I want to get the highes 'ABC' value

I looked on the forum and found this code

Dim strSQL As String
Dim MyValue As DataType 'Replace the proper datatype of field
Dim rs As ADODB.Recordset, Cn As ADODB.Connection
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection
strSQL = "SELECT tableName.ColumnName FROM tableName ORDER BY tableName.ColumnName DESC"
rs.Open strSQL, Cn, adOpenStatic, adLockReadOnly
MyValue = rs!ColumnName
rs.Close
Set rs = Nothing

I've modified the query to this

strSQL = "SELECT tableName.ColumnName FROM tableName WHERE tableName.ColumnName Like 'ABC*' ORDER BY tableName.ColumnName DESC"

This doesn't bring anything back but I was expecting 'ABC1236'

I'm new to using 'Like' so this could be the problem.

Your help is much appreciated.

View 6 Replies View Related

Finding BAC-5, BAC-8 And BAC-12???

May 3, 2007

Greetings all,
It's been a while since I've had to turn to you for help. I'm usually able to find what I need in searching through previous posts, but no luck this time around.

I am trying to write a query criteria that will return

BAC-5, BAC-8 And BAC-12

I can get the -5 and -8 with:

Like "BAC-[58]"

But I can't figure out how to get the -12...

The actual part numbers are quite a bit longer and there are hundreds where I only need specific dash numbers returned.

I'm sure this one will be simple, I just couldn't seem to figure out how to search for it.

Thanks in advance!

View 3 Replies View Related

Finding Something Thats Not There?

Sep 27, 2007

Hi guys

I have a DB for insurance permits.

tblSubtractors
ID
NAME
ADDRESS
PHONE

tblPolicy
ID
SubbyID
POLICYID
Insurer
PolicyNumber
ExpiryDate

tblPolicyTypes
ID
Name


Most subcontractors will have a policy number for each type of policy in tblPolicy. But is there a way I can run a query to show each subby that doesnt have one of the policies? As an example, the query would show me that ABCcontractors doesnt have Marine Transit insurance. XYZContractors doesnt have Public Liability, etc

I know how to show all the policies that have expired, but I'm looking to identify which ones dont have a policy at all

:confused:

View 7 Replies View Related

Finding ID

Aug 6, 2006

Hello,
I have this code that I use to update a record:

Code:<%Dim idid = session("loginID")Dim rsSaveSet rsSave = Server.CreateObject("ADODB.RecordSet")rsSave.open "log", conn, adOpenDynamic, adLockOptimistic, adCmdTablersSave.find "id=" & idDim Datumlogout, Uurlogout, loggedoutOKrsSave("Datumlogout") = datersSave("Uurlogout") = timersSave ("LoggedoutOK") = ("true")rsSave.updatersSave.closeset rsSave=Nothing%>

Can someone help me please, I would like to have the code changed to:

When the ID not excists, that there is a new record written.
Thanks in advance!

View 1 Replies View Related

Finding An Average

Aug 8, 2007

I have a customer concerns database that contains the dates for when the concerns were reported and tyhe dates for when the concerns were resolved. I am trying to make a query that finds the average of how long it takes for the concerns to be resolved. How can I do this?

View 1 Replies View Related

Finding The Last Row In Worksheet

Feb 29, 2008

Now this is not like me, posing a question, but I thought its about time I codes the app correctly.

My app opens up any given spreadsheet. The first task is to establish the last row used in the spreadsheet. Normally you would loop until there is nothing in a cell in column A, whatever.

However the way the spreadsheets are provided there may be pleanty of blank rows. I have tried using the Edit GoTo Special LastCell and converting it to a macro

But when I run this VB it does not like it for some reason.

I am sure someone out there has come up with a solution by now.

CodeMaster::cool:

View 4 Replies View Related

Finding Current_Date?

Nov 2, 2005

I have a table, with 4 records in it. 2 records have the "date" field for 01/11/2005, and 2 dates are for 02/11/2005.

Is there a way to just query the "current" date? Weather it be in a pop-up box once the query is selected, asking for the date? or is there a restricted word within access2003 that lets you find the current date?

I would essentially like a command button within a form to allow the user hit teh button, and it will automatically find those records within the DB with the current date(set to system clock) and export it to excel(i have this portion, the exporting to excel complete)

Any help would be greatly appreciated.

Connor

View 7 Replies View Related

Finding Max Date

Jul 21, 2006

ok ihopefully i wont make this too complicated to under stand.

i ahev a form which will populate a form from a query. the query is pulling info out a table included int hti sinfo is the date when it was entered. in the criterai of the query i hve added this code
Between [Forms]![Boxes Reviewed]![txtFromDate1] And [Forms]![Boxes Reviewed]![txtToDate1]
whcih means that the date must be between the two differnt dates that must be entered on the form.

the problem is, is that a value can be entered more that once with different dates. i woul dwant the report just to shouw the most recent date. i considered doing a query which requested all max dates from the table and then having a query calling forma query. but i do not know enough on how to get just the most recent date.
so if i wrote a query which held

box num, shelf num, resp_person, and date. what would i have to put int he criteria to only bring back the entery witht he most recent date.

sorry if i have rambled and look forward to your help

View 1 Replies View Related

Finding First Records Only

Nov 1, 2006

lots of customers. lots of orders. i need to query for the first order of each customer only. thoughts?

View 1 Replies View Related

Finding A New Record

Feb 24, 2005

Hi

I apologise if this is really basic - I am learning as i go along. I have designed a database. Most of the info is stored in the clientinfotbl. On the main form, I would have provided an option for users to search for a record based on its ID number and then pull up various forms accordingly. I am using the "find and replace" option to do this. My first question is, is there a better way? This feels a bit clunky!

Second question is, once I have entered a new record in the "newclientfrm" and go back to the mainfrm and search for that record, it cannot be found (once I have closed db and gone back in, it can then find it). Is there a way round this?

Any help would be hugely appreciated :)

View 2 Replies View Related

Not Finding Other Form

May 12, 2005

I would like to make a value equal to another value in another form, but it keeps telling me that it can't find the Form.

newID = Forms![frmLogIn]![UserID]

Error message is 'Access can't find the form 'frmLogIn' referred to in a macro expression or Visual Basic code'.

I have rechecked the spelling and caps on the form name 5 times so that's not it :(

James

View 5 Replies View Related

Finding The Subform????

Sep 1, 2006

Here's a real stumper... I have a form with a subform based on the account number in the main form. This form has been running without a hitch for maybe two years! Now, while updating an unrelated form in the same database, I test the new additions. I get an error message that the main form cannot find the subform, and it goes on to display a blank white box instead! Here's what I have already tried:

1. Checked the name of the subform. It's correct, just like it's been for 2 years.
2. Running compact/repair in case of corruption
3. Deleting, then re-placing the subform on the Main Form.

One other wierdism - after getting the white box where the subform belongs, and after being told that the [subform name] can't be found, I can right-click on the white box, select the "Form" option, and the subform appears! To me, that means that the "can't find" error message is a misnomer, but I'm at a loss as to where to look from here.

I guess what I'm asking is if anyone has seen this behavior of a form "not finding" its subform, yet it can be forced to display it with a popup menu? Also, any experiences with this or any related information would be appreciated.

Thanks-
Scott

View 3 Replies View Related

Finding The Last Day Of A Month

Oct 4, 2004

I need to create a function that will return the last day of a month, regardless of which month it is and, in the case of February, which year it is. (Gilbert & Sullivan got it right - the powers that be played a cruel joke on us with leap years!) I could hard-code in the months into my code, but there's got to be a way to do it without typing in everything manually...right?

View 1 Replies View Related

Finding My Posts

Sep 4, 2006

Is there a way to find all my old posts except looking at all 381 pages?

View 4 Replies View Related

Finding A Record

Feb 5, 2007

Hi guys, need a bit of help with my Database. I have a Table with has ever player to play for my football club (1700 records in total), now i have another table called games (which has a record for every season played for each player, and is linked to the first table using the relationships and a field called ID. Make sense so far?

Well i then use a query to combine and calculate totals of certain fields and then i can use the query to show every single statistic possible on ASP pages. But when viewing my query in Access only 1699 records show up. So my thinkin is that either two records are identical (which is impossible because every p;layer has an ID and they cant be duplicates) OR a player has ZERO games in all years (in other words the player has no records related to it in the second table called games).

My question is, instead of searching 1700 records manually to find which one is there any way i can find which player would have NO records from the related table joint to it?

View 2 Replies View Related

Queries :: Finding The Next Min?

Jul 6, 2013

Im looking to display the 4 lowest figuers obviously Min is the lowest but how would you dind the 2nd 3rd and 4th Min or is not possible?

View 14 Replies View Related

Queries :: Finding First And Last Value?

Oct 8, 2013

I have a table with assignments if employees to teams.

PositionsID(autowert),EmployeeID, timefrom, timeto, TeamNo

3, 123, 13:30, 16:45, 4
4, 123, 17:00, 19:00, 7
5, 432, 17:00, 20:00, 8
6, 987, 17:00, 19:00, 9
7, 987, 13:30, 16:45, 5
....

Some of the employees can be assigned to more than one team, the assignement is not chronicaly, meaning an earlier timefrom can be assigned later (see Employee 987)

As a resultlist I need following:

123, 13:30, 19:00, 4
432, 17:00, 20:00, 8
987, 13:30, 19:00, 5

I can get out MIN(timefrom) and MAX(timeto) per EmployeeID but not the first teamNo, which is the first team he starts his duty!!

View 11 Replies View Related

Finding Info In Another Table

May 11, 2005

Please note that I am self taught (90% of what I have learnt has been off these boards!). I did make this form with info I found on this forum.
I am having problems with a log in box for a database, the line of code is -
If Me.txtpassword.Value = DLookup("password", "customers", "[customerID] =" & Me.EbayName.Value) Then
This code is in a Form; "password" is the value in the table of "customers"; [customerID] is the primary key value I wish to remember; Me.EbayName.Value I assume is the value in the table that access is looking for.

Questions -
A) It is not looking up the value for [customerID] - what have I done wrong?

B) In the line of Me.EbayName.Value what does the 'Me.' part tell Access to do? I assume it's a pointer to it's own form? What part of the line do I need to change to make it point to another form (or table)?

C) When this is finished how do I get the program to remember the 'customerID' while the customer is fillling out other forms?

James

View 2 Replies View Related

Finding Third Wednesday Each Month

Nov 14, 2005

This seems easy, but I can't get it right. I need to find the DATE of the THIRD Wednesday each month. (Actually, it is for a query criteria that will return TRUE if Now() is a "third Wednesday.") The problem is that depending on the month starting date, number of days in month prior etc. this date can be either in the 3rd or 4th week. Also, you can't just add 28 days to the prior month because it can vary.

View 5 Replies View Related

Finding An @ In An E-mail Address.

Feb 22, 2006

having a prob with the above. not sure what is the best way to find out what comes after the @ in an e-mail address.

For example, if i was to have the following e-mail address@

firstname.lastname@myemail.com

is there an expression to use to shrink the above so that i can only see the "myemail.com"?

thanks for your help.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved