I have a table called SWAPS which contains information on each user and the different equipment he has been issued. Query at the moment contains 5 fields
Payroll, Surname, Firstname, Mobile No and Date sent.
SQL for this query is as follows
Select Swaps.PR, Swaps.surname,swaps.firstname,swaps.[mobile no],Max(Swaps,[Date Sent] as Lastdate
From Swaps
Group by swaps.pr, swaps.surname,swaps.firstname,swaps.[mobile no]
Order by swaps.pr;
Now when I run this I get the results I want by date so I get
123456 Smith Fred 0788411025 12/3/8
456789 West Bob 012555447854 13/1/8
So it correctly ids all the last dates for me as I want to see the latest issue date for that user. Now I want to add in another column to this query which contains the serial number of the equipment issued and heres where the problem lies. If a user has been issued more than 1 piece of kit, I get all the serial numbers irrespective of date. How do I get it to display the serial number for the latest date?
I have a table of order lines. Each line of data the order number is repeated for every line. I need to just pull out one line for each order number. I assume a query would do this but I don't know how to create it. The order number is numeric. Can some one show me the way?
I have one table with 250 fields and hundreds of records. Each field represents a computer we have and the records in that field represent the type of applications we have on that computer. So the table looks like this:
Microsoft...............Google.................... .Paint.....................Microsoft Windows Hotfix......Security Updates.......Java ......................Google Java.....................Mediaplayer.............. Google.....................Java
As you can see, most computers have the same applications, but some have applications that others do not. I just need the list of applications we have from all computers. Meaning each app is listed only once.
I just need each application listed once. So I was trying to come up with a way to delete all the duplicates or use a Totals query to group the apps but since my table has 250 columns I'm not really sure how to go about it. What kind of query would be best to make this work?
I have a MS Access 2000 database with 136 data tables in it. What I would like to do is execute a piece of VBA code which will list for me in another table, (for example: Field1: TABLE NAME Field2: FIELD NAME 1 Field2: FIELD NAME2 etc), starting left to right, how many fields would have to be combined in each table to represent a unique record.
For instance:
TYPE ID TEXT 1 1 "Cats" 1 2 "Dogs" 2 1 "Rabbits"
In this example a combination of the fields TYPE and ID give a unique record.
I have developed a very basic contact DB to record enquiries coming into my business. The last field on the DB is for comments, so that anyone entering the DB can see the latest conversation we have had with a particular customer. What I want to do is create a means whereby I can find a customer within the DB and then be able to add the contents of our last conversation into the Comments field. Would there be any way of doing this via a switchboard??? I am a very inexperienced Access user so the simpler the answer the better. Many thanks in anticipation Keith:(
To prevent duplicate records, I use multiple fields indexes, which worked fine until now. I learned that each "empty" fields are consider unique by Access, so not the best in an index to prevent duplicate records. I managed to have it worked using the default value property to give each "empty" records the same value.
Now my problem is that I have a date field which is optional, but I need to use it as a unique identifier in an index. I could again use a default value, but since it has to be a date, I find it confusing. I would rather have a default value of "No date specified" or "-" or even better, "". Unfortunately none of those works with date fields.
Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?
I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...
I wonder if someone can help with what must be an easy solution to this problem.
I have a table with 350,000 records, but without a unique identifier field. I just want a simple incrementing number field to become the primary key but dont know how to add this in. I cannot add an autonumber as there are two many records - it falls over.
Ive tried exporting it all to excel, but as well as being cumbersome, something went wrong and so I'm back to square one.
I think there must be a way to add an incrementing number either via a query or in VBA but I dont know how. Please can somebody provide a solution.
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
I am an Access novice and have managed to create a DB to record all incoming enquiries to my business. The enquiries are entered via a form onto a table and one of the fields is the date of the enquiry, which is entered as 01-Jan-07. What I want to do is build a query that will find all enquiries in a given month. Has anyone got a simple soultion, please?
My database holds details of visits made to customers by support staff. I am running a query which returns details of the last visit made to a customer as below with results displayed on a webpage.
SELECT Visits.Date1, customers.CustID AS customers_CustID, customers.Customer, Visits.CustID AS Visits_CustID, Visits.Visitee, Visits.VisitType, customers.RegionID, customers.NextVisit, customers.NextVisitBy, customers.Confirmed FROM customers INNER JOIN Visits ON customers.CustID = Visits.CustID WHERE (((Visits.Date1)=(SELECT MAX (Visits2.Date1) FROM Visits AS Visits2 WHERE Visits2.CustID = Visits.CustID))) ORDER BY customers.RegionID, customers.CustID
SELECT MAX on Date1 is used to select the most recent (largest) date. The problem I have is that if 2 different staff members visit the same customer on the same day, the query logically returns 2 MAX date results for that customer, whereas I only want 1 result per customer. As the dates are the same I am not sure how to proceed. It doesn't matter which of the 2 results are returned. Does anyone have any ideas? Thanks in advance :)
ps - I can't perform MAX on the VisitID as the support staff don't always enter the visit data in the correct order meaning that an older visit date might have a higher VisitID than a more recent visit.
i have 2 fields in a form - 'Balance' and 'Date'. Quite simply I need the date field to change automatically to todays date each time the data in the 'Balance' field is changed. Can anyone advise me on how to accomplish this.
There are three tables. An [Action Register] table, a [Calls] table and a [tblContacts] table. The Contacts are common to both.
The Calls table records calls to customer by date
The Action Register table records issues that Customers send in by Open date.
I am trying to make a query where we see the latest date the customer was contacted regardless of which table.
I created two queries.
qryLastCallDate finds the max date from the Call table:
SELECT Max(Calls.CallDate) AS MaxOfCallDate, tblContacts.ContactName FROM Calls LEFT JOIN tblContacts ON Calls.ContactID = tblContacts.ContactID GROUP BY tblContacts.ContactName ORDER BY Max(Calls.CallDate);
qryLastIssueDate finds the max date from the Action Register table:
SELECT Max([Action Register].Open) AS MaxOfOpen, tblContacts.ContactName FROM tblContacts RIGHT JOIN [Action Register] ON tblContacts.ContactID = [Action Register].Contact GROUP BY tblContacts.ContactName ORDER BY Max([Action Register].Open);
The problem I am having is that if I use Left Join I can see all the records from the Calls table but not all from the Action Register table. And vis versa if I use Right Join. This is because sometimes we have calls but no issues in the Action Register table and sometimes issues with no calls.
This is my Left Join query using a Min Max Module I found here: [URL] ....
SELECT qryLastCallDate.ContactName, qryLastCallDate.MaxOfCallDate, qryLastIssueDate.MaxOfOpen, qryLastIssueDate.ContactName, DateValue(MaxOfList([MaxOfCallDate],[MaxOfOpen])) AS [Last Contact], ([Last Contact]+21) AS NextCall FROM qryLastCallDate LEFT JOIN qryLastIssueDate ON qryLastCallDate.ContactName = qryLastIssueDate.ContactName;
How do I get to see ALL the records from both queries.
I have a database that is used (partially) to enter appointments during a day. In the appointment column I've entered the date and time as dd-mmm-yyy-hh-nn-ss. I'm now trying to search for appointments entered on a specific day through a user input ([Enter Date] in the query criteria) Problem is, this only returns records where the time hasn't been entered and the time shows as 00:00:00.
I've tried CDate which gives me add/mmm/yyy return. if I try to search against that I get nil returns though. As a test I put DATE() in the criteria but it then says that it's an invalid foremat. I've also tried various machinations of "Like" and "Between" without success.
I'm wondering what would be the best way to update a list of records based on today's date. Right now I have it to open the form based on the LAST record and then navigate to the record they would like to update, I would only like to show what was entered that day.
I am trying to use a query to find the previous record of a transaction and provide an additional field that computes the difference in the dates in days. (The days from TransferID 23 to TransferID 24)
So I'm trying to do this database for my ICT coursework and its a full system for dog kennels.
So in actuality the rooms are kennels.
I have a table tblbookings that amongst others has fields:
Kennel No Date In Date Out
I need a way of users entering the requested dates for a new booking and getting an output of a list of all kennels that are available to book for that full date range or even better, a way of running this straight from the form for a new booking frmbookings to just leave the first available kennel no. in the field KennelNo?
trying to get something working on a form. When creating a new client, due to privacy, we have to create a Unique ID for each one. The ID consists of their initials, the year, a serial number and the location. I have it mostly working except for the year. Let me show you what I have and the results, then what I would like the results to be.
How do I now get the report to generate off the ID and not the date?
No sure if it matters, but my form is populated by choosing a name in a combo box then the date in another combo box. Its the date combo that finds the record for the form.
This works but the problem i have is, when i add an entry via a form or do anything else via a form. This does not get updated?. I check the properties of the table and that isn't either.
When i then modify something manually in the table (go in and change it). It then updates.
Is there something im doing wrong or is there a better way to check the last updated.
I have a report that is pretty complicated in the page numbering, since it's grouped by Aisle Number (it's a report of hazardous products in a store), and the Aisle Number and the "Page x of x" is in the header.Someone here actually created all of the logic for me last year.how a particular table is being populated with the information.Here's the Event Procedure for the "On Open" event of the report:
Code:
Private Sub Report_Open(Cancel As Integer) 'when the report opens the temporary table needs to be cleared CurrentDb.Execute ("Delete * From ztblAislePages") 'delete records from table ' the recordset object needs to be opened so it can be used and accessible in the group footer and header sections Set GrpPages = CurrentDb.OpenRecordset("ztblAislePages", DB_OPEN_TABLE) 'open the ztblAislePages recordset GrpPages.Index = "Aisle" 'set the index so procedure knows what field to search
[code]....
ztblAislePages is a permanent table that holds temporary information...it's populated with information only for this report, and as you can see from the code above, it's cleared at the beginning of the process.where this information is coming from to populate the table, however.
Code: Set GrpPages = CurrentDb.OpenRecordset("ztblAislePages",DB_OPEN_TABLE)
The table itself has no source data that I can find, unless I'm not looking in the right place.I've done a search for dependencies and can't find anything. All I know is that when I choose a store, the table IS being cleared, and it IS being populated with new information.
I am trying to find duplicate data from two columns and I want to query the all the duplicates. I tried the query wizard but it only finds duplicates in the column itself. I am trying to compare the columns together and see what is found in column A that is also found in column B and also Vice Versa.
I am trying to create a DB that would calculate the totals of different projects and also calculate the global total (i.e. the total of all the projects combined together)
So far I have created a query that can calculate the total of the projects in a field which I have named Total1. But now I want to calculate the sum of the field Total1, i.e. add all the totals of the projects up.
Sorry my explanation does seem quite lengthy, any help would be much appreciated.
