Getting Only The Last Date Per Record In A Primary Table
Jul 27, 2007
I need to build the following query:
I have 2 tables:
tblBusiness
tblInspection
tblShops have an ID, an adress, bussines name, owner name, etc
every Shop can be inspected many times. A inspection can be futile if the store is closed. If the store is open, the inspection is made, and as result with can have “with irregularities” “without irregularities”. Two or more inspections can be made the same day (if the n – 1 time the store was close).
So, here is the basic select:
SELECT tblBusiness.idBusiness, tblBusiness.BussinessName, tblInspection.InspectionDate, tblnspeccion.InspeccionTime, tblInspection.Open, tblInspection.Irregularity
FROM tblBusiness INNER JOIN tblInspection ON tblBusiness.idBusiness = tblInspection.idBusiness;
What I need is to get the LAST inspection where the bussines was open and with irregularies. One inspection per Business, but of all business. Ah, and if there ir a newer inspection but the business was close, it doesn´t count.
I tried “Group by MAX” with InspectionDate, but it just didn´t work. For example, it showed me two inspection of the same business, not “the last” of that bussiness (it was easy to filter the Open field and the Irregularity field).
I suspect that what I need to solve this is to use two or more SQL stament, two in one., something like that. But I´m in a hurry and I just can seem to get it right.
I hope you can help me with this. Thanks for reading anyway.
P.S.: the fact that there can be more than one inspection per day can be a bitch =(
but there is a "InspectionTime" that can save that". Mmm the problem is that one inspection can be made 1 july around 20:00, but the seccond on 2 july 01:00. But we store the two inspections in 1 july... so... that mens problem.
But that issue is not so important as the previous problem up there (well, in order to solve the seccond I need to solve the firts...)
Hi How can I make the primary key (participant #) in my table equal the record number on the Form? In other words, when I open the form, I need two things to always happen: 1. the primary key must equal the record number (which is displayed on the form)& 2. when I click "Add New Record" button, I want a new customer # automatically generated and in the correct order.
I want to change misspelled records, but the misspells are in the primary key of my table. By changing the values of primary key fields, can I break table relationships? I'm not changing field names, just field values of a primary key field.
Hi, I have 7 tables in my database and 1 form corresponding to all the fields in these tables (linked by a query by recordsource). My problem is that the form will only show a record if ALL 7 tables have manually had the primary key entered (not good when i have information that needs to be added at different times). How can I make it such that if I create a new record on the form that all primary key fields will be updated, and this record will be present every time i open my the form??
I'm not sure if it's a simple task, keep in mind I'm new to using access. We have a need at work for a small db just to keep track of some things and access seems appropriate.
I was attempting to set up the primary key in a date / increment format.
Basically the format would be year, month, day, increment (starting at 001)
060125-001 060125-002 060125-003
etc
next day I would like the key to change according to date and reset the increment to start again at 001
060126-001 060126-002
etc
I do realize they are different data types, I'm just not sure how to attack setting it up. Thank you in advance for any help / suggestions. :)
I am creating a form to add records to my database. I have created a primary key that is not autonumber. When adding a new record, I want the form to put a primary key default value of the next number in sequence. How do I do this?
function. More specifically, I am using a bound form with a table that I need the date for. The date will be an integer from a specific record (based on a search) from a separate table, and then added to the current day's date (in days).
I have an access form which includes my primary key field "ID". Normally when I use this form to enter a new record this field displays "(New)" until I enter data in any other field, then the ID field updates to reflect the new value. This worked fine when the table I was adding the record to was a table linked from another access database.
I recently moved my data to SQL Server 2008, without making any other changes to the application, and this behavior has changed. Now when I enter data into a new record the ID field changes to "NULL". And I am not able to reference this value in my program (Me.ID) like I used to be able to. When I save the record the ID field gets assigned and everything works as normal.
So I am able to make my application work again by forcing the record to save before trying to reference Me.ID, but why the behavior changed when my table moved from Access to SQL Server.
Hello everyone. I am wanting to store the date the record was entered into the table. But I don't want the users to have to type this in; I thought that Access could grab the date from the system date.
I am new to this and I looked at the Date function but I am thinking it will just always update to the current system date.
Thank you very much if anyone could point me in the right direction.
I have this table that records sales events for properties, with multiple sales records for some parcels. Each parcel has unique field: MapTaxlot. I want to create a select query or make table query that shows only the most recent sale event for each parcel. Instrument_Date is the date field for the sales records.
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
I got this function which is supposed to find a record based on the primary key, and then give three fields a value if they should be empty.
Public Function DoesXrefExist(faPersonID As Long, faRoleCode As String, faDorpID As Long, faDorpCode As String) If (DLookup("PersonID", "PEOPLE", "PersonID =" & faPersonID) > 0) Then End Function
Thats as far as i have got...for the next part.I get the feeling that i need to use the recordset object that exists somewhere.How do i access that recordset object? I have not been using any DAO or ADO, just straight VBA. I know i can create recordsets and stuff using DAO/ADO, but there must be a recordset that has been created already by the Dlookup command...
I have an existing database with multiple tables. Two tables have the same primary key Cab#.
1. When user clicks on the form on the menu form, I want to request the Cab# value from the user. 2. Then I want to get the record from the first table CabData based on the entered Cab#. 3. Then I want to create a new record for the second table Rides with five fields from CabData table inserted into the Rides record. 4. Then I want to display the new Rides record to the user for updating. 5. Then I want to save the new record to the RIDES table.
What is the ACCESS termonolgy to do each step? Are there any special details I should watch for?
I have a database in Excel that contains farmers who can be identified by a Unique identifier: MZ-01-0001. The registration details for these farmers are not always up to date. Now when I try to append the data for the farmer with MZ-01-0001; with the primary key set on the Unique identifier, Access does not want to do it since there is already a record in the system. When I remove the primary key it doubles the record, which is also not what I want. So I am wondering what I need to do to append the record while keeping the primary key intact.
How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.
I've designed a DB in access which has a BackEnd and 2 FrontEnds (one person insert all the records and the others just keep inserting infos till the process is finished.The DB has 12 tables and we used it for about 6 months without having any trouble but recently (2 weeks ago) i've add 3 new tables and then related them to one table that already exist.
The DB was running smoothly for a week after the changes but last monday (09/15) the "Record is deleted" appeared. I've compacted and repaired an the following errors descriptions appeared:
ErrorCode: -1017 ErrorDescription: Record is deleted. ErrorTable: tblFatura
ErrorCode: -1053 ErrorDescription: Index or primary key cannot contain a Null value. ErrorTable: tblFatura
ErrorCode: -1630 ErrorDescription: You cannot add or change a record because a related record is required in table 'TblExpense'. ErrorTable: tblFatura
I've restored the file via IT using the Backup2 days before the error occurred but after 30 minutes the same error appeared! I dont know if it is related to the new tables that i have add or no?
I have a form that has a FIND button. I want the user to enter the 3 fields that make up the primary key, then find the associated record. How can I do this in Access - not VBA?
I am building a database to capture monthly statistics on a number of items. I want to ensure that users don't enter statistics for the same item for the same reporting period. I found the following instruction, but can't make it work:
It suggests that I create multiple primary keys in the table
When I do it, it comes back with an error: Index or primary key cannot contain a null value.
I have an Access 2010-form which inserts a record in a MS SQL 2008-database by using an ADODB-connection. I need to retrieve the primary key of the newly added record.
With code at the bottom I create a SQL-string which is stored in variable strSQL. If I execute the SQL-string directly in a MS SQL 2008 Query Window, the record is inserted and the MS SQL 2008-result pane shows a column "ID" with the primary key of the newly added record.
If I execute same SQL-string in MS Access 2010 the record is inserted. However, the code fails on Debug.Print rs![ID] with error "Item cannot be found in the collection corresponding to the requested name or ordinal". Same error appears if I use Debug.Print rs.Fields(0). I've enable the reference "Microsoft ActiveX Data Objects 2.8 Library". It looks as the recordset rs is closed as soon the command "Set rs = cn.Execute(strSQL)" finished.
Why I can't retrieve the Primary Key with VBA-code below?
Code: Private Sub cmdSave_Click() dim cn As ADODB.Connection dim rs As ADODB.Recordset set cn=New ADODB.Connection
My problem is that I am trying to update a field (called 'Sold' which is a yes/no checkbox column) for a specific record whenever an event is triggered. I have two forms (derived from two tables), one is called frmInventory and the other is called frmSales. In frmSales, I made a combo box called 'cboItemID' that allows the user to select from a list of items from my inventory table. Each selection from the list has 4 properties, the first of which is the 'Item ID' from the inventory table. Lastly, I have a field in both frmSales and frmInventory called 'Sold' as mentioned above. What I want to do is that whenever I check/uncheck the box in the 'Sold' field in frmSales, I want the 'Sold' field in frmInventory to check/uncheck as well, but only in the record with an 'Item ID' that matches the 'Item ID' from the combo box selection. In other words, I want to match the 'Sold' field in frmInventory with the 'Sold' field in frmSales, but for only the record that has the same 'Item ID' primary key as the one I picked from my selection in the combo box from frmSales.
how to reference another table and check whether or not it's 'Item ID' primary key is identical to the one I specified from the combo box, and then take action to update the 'Sold' field if the IDs match.
I have a continuous form that shows all the available lease numbers for a client. Each lease number has a monthly charge associated with it and has multiple cars associated with it. For example a client may have lease number 1, 2.5, and 3. Lease 1 may have 3 cars, lease 2 10 cars and lease 3 1 car.
I created a continuous form that shows each individual lease number for a client and the monthly charge. The form is based on a totals query where I use a count to get the total number of cars for the lease number. That all works fine and when i open the form it shows me all the lease numbers for the active client and the monthly charge like the table below.
Lease number Monthly Charge Total cars in lease total value of lease value left on lease
1 100 3 100x3=300
2.5 200 10 200x3=600
3 250 1 250x3=750
I am trying to do =Sum([CRevFromCustomer]*[CountOfLeaseNumber]) but it gives me the same value for all lease numbers. It gives me the total value of all the lease numbers for that client instead of showing me the total value each individual lease number. table below shows what I mean where 1650 is the total from the 3 different lease numbers and revenue added together.
Lease number Monthly Charge Total cars in lease total value of lease value left on lease
After a lot of reading and consolidating VBA codes for audit trail.How My Audit Trail Works..A module was made for a function named as "Changes" .Then inserted into before update event of a form where I will do the editing of the records.Then I made a table named as Audit.Inside this table I made all the fields I needed such as:
*AuditRecordId[autonumbered] *FormName[The name of the form for editing] *Index[The record ID of the record being edited] *ControlName[The Field being edited] *DateChanged[Date change was done] *TimeChanged[Time change was done] *PriorInfo[for the old value of data being changed] *NewInfo[For the new value of data changed] *CurrentUser[The user base on log in form that was set to Global into another module] *Reason[The reason for changing for future references]
And Here is the Function Code:
Code:
Option Compare Database Option Explicit Function Changes() Dim db As Database Dim rs As Recordset Dim strSQL As String Dim strCtl As String Dim strReason As String
[code]....
This audit trail function is valid only for one(1) form, due to the limitation of
Code: Screen.ActiveForm.Controls("SUP ID").Value
where "SUP ID" is the primary key of the record being updated/Change, so if there are Five(5) tables that needs audit trail, there will be also Five(5) forms, as well as Five(5) Function Changes namely; Changes(), Changes1(),Changes2(),etc... because all the table do have their own sets of primary Key.
Is there a shortcut, in such a way that the "rs!Index" will automatically return a value, equivalent to the Primary Key/Record Id of the record being updated/change, given that there are different updating forms for each table to be updated?
ok , i have a primary table that have a field of "job", then i create another table that have a field of "job" too , then how can i do this task?
Whatever i type a data into the primary table("Job" field) i want it to duplicate the data into the another table("Job" field) . is it possible to do this? can anyone guild me to do ?
Is it possible for the primary key of one table to act as the foreign key in another table for more than one columns? What I'm trying to do is create a table for a Committee which will have 1 student and 5 professors! So Can I import the faculty ID for each of the 5 faculty members?When I try creating the second relationship, access automatically creates a new Faculty table for the relationship!
