I have two fields. A barcode field will have several instances of the same data, and the other field has different dates and times when it was scanned. I need to be able to query the barcode field and only return the record with the latest time. Can this be done in a query or do I need to do something else? Here is a sample of data I am working with. I attached a small sample of data as an example.
Thanks,
Hello All...
I'm very new to Access.. so please forgive my ignorance..
Here is the scenario..
I have a table with two fields named "item" & "description" that contains 10 records total.
I have another table with 100 records with the fields "invoice #","item", "price paid","date paid","time paid".
The item fields are linked between the two, and the 100 records contain different invoices for these ten items.
I want to pull the price paid off of the latest invoice, based on the time and date... What is the easiest way to do this?
Thanks!
Hi guys
I am trying to create a simple database to keep track of computer components that I order for custom PCs. The data is fairly basic Quantity, Description, Stock Code, Delivery Status, Cost, Cost+VAT, Date Delivered. The list is currently 500+ rows of data. I simply cut and paste this info directly from my online invoices.
What I want is to be able find the the last price paid for an item (often the same items have different prices week by week) Eventually I would like to be able to create a rough quote using the latest prices (plus mark up) and also view a price history.
I would also like to include a few pictures as URL links to the suppliers website ????
At present the data is in Excel and linked to a single table in Access (but this can be changed). I realise that I have to group the records somehow but cannot get it to work. At present it lists every record either in date order or product order.
The stock code should not change, but product descriptions do. So any links to pics would be based on stock codes
Someone please tell me how to display the full list of products but only the latest price/date
If you think the current format is rubbish then feel free to suggest an alternative. I much prefer Excel but have never really got to grips with Access (used to be a vba programmer with Excel). To link to pictures and hide data that customers might see Access seems the obvious choice. So I try again!!!!!
Thanks
Steve
I am working on a jobs database where employees enter information where the job is being handed off to. I want to create a table showing the latest job entry by date. The jobs are listed by "Job Number" and when I try to create a table and remove the duplicate "Job Number" it does not always remove the oldest entries.
Any help would be appreciated.
I have a database that has 2 forms. After submitting the first form, the user should complete the second form within 24 hours. The first form stores the Date/Time the form was submitted. I want to be able to run a query and have a column in the query that is "Time Remaining". In non-technical terms, this column would be: Date/Time form submitted + 24 hours - Current date/time.
View 3 Replies View RelatedI have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
How do I make an append query only append the records beginning with the next autonumber?
I have linked tables in db...one db is for warehouse employees to input their orders and the other table is in the original db for managers to use for reports. I need to append the records to original db throughout the day, but the records cannot be deleted from warehouse db because it keeps running total of order minutes (for production purposes) so employees know how many minutes they have (and need to make up) through the day. Also, managers need up to date db so they can see if they are on track.
The autonumber field is my primary key..
I hope this all makes sense.
Thanks in advance
Noreene
Hi,
I am working on a report and I am stuck with the following problem:
Here at work we use two tables. One for creating records and for the updates of the created records. In the attachment you see a part of the table 'updated'. What you see is one ServiceNumber (1-551747091) which is changed users (Eigenaar.) three times.
I want to query the latest user of each ServiceNumber. So that the result is:
ServiceNumber | User
1-551747091 | MKB_VERHUIZEN
etc.
How to do that? :confused:
Thanks!
I am having difficulty formulating this query.
I have 2 tables... 1 is customer info (name, address, etc...), and the second is shipments (ShipToCustomerName, ShipmentDate, etc...).
Now, I wish to query the shipments table to get ONLY the LATEST shipment information for EACH customer.
If customer A has a shipment in Jan 1, Feb 2, and March 3... while customer B has a shipment in April 4 and May 5. then the result of the query should be
customer A - March 3
customer B - May 5
Is there a simple way to construct such a query?!?
Thank you for your help,
- arm
I'm fairly new to SQL and have a problem finding the latest record.
Lets say I have a development project that has many disbursal payments to be sent. I want to find the latest disbursal record created for that one project instead of having all of the disbursal records show on the query.
I'm trying to work out how to do this in a query but haven't got a clue where to start.
Please, if anyone can help I'd be very grateful!:D
I am trying to pick the latest record from "tblEmpVac.id" field but I keep getting syntax errors. I am thinking if the "tblEmpVac.id" = Count('tblEmpVac.id') then that will show the most current record as it will count and match the last record correct?
Another problem is if I put it into the WHERE section, it will give a conflict. So I don't know where to put it. Below is the SQL, if anyone can help it would be great.
SELECT DISTINCTROW tblEmp.position, tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.region, tblEmpWorkHistory.[current store], tblEmpVac.current_year, tblEmpVac.id, tblEmpVac.entitlement, tblEmpVac.days_taken, tblEmpVac.days_carryover, [days_carryover]+[entitlement]-[days_taken] AS total
FROM ((tblEmp INNER JOIN tblEmpVac ON tblEmp.ssn = tblEmpVac.ssn) INNER JOIN tblEmpWorkHistory ON tblEmp.ssn = tblEmpWorkHistory.ssn) INNER JOIN tblEmpWorkHistoryData ON tblEmpWorkHistory.ewh_id = tblEmpWorkHistoryData.ewh_id
WHERE (((tblEmp.position)<>"Terminated") AND ((tblEmpVac.current_year)="2005"))
ORDER BY tblEmp.position, tblEmpWorkHistory.region, tblEmpVac.id;
Two of the fields in my query are for Progress Note and Progress Note Date. Each client has several progress notes. How can I have the query show only the Progress Note with the latest date?
:confused:
My table shows
ID ID_Clients EndDate
A particular value of ID_Clients can recur many times, but with different EndDate value each time.
I want to create a select query that displays
ID_Clients EndDate LatestPreviousEndDate
Any help with this would be much appreciated.
-Thanks
I have two tables. The first is called Drawing Register and contains the fields Drawing No (primary field) and Drawing Title.The second table is the Drawing Register Details table which contains the fields Drawing No (joined field - Drawing Register table), Revision, Revision Notes, Date Issued.
I have a query and report which will list all the revisions for each drawing. This is very important and useful. However I want a summary report which will only show the last revision for each drawing.
I copied the original query, turned on the Totals option and under the Date Issued field changed Group by to Max. However it is still returning all results for each drawing instead of only the last issue date for each drawing.
I need to find the latest number using a query.
All of the numbers follow this format:
[Doc No]
ABCD123
but the tricky part is the number could be suffixed with a revision. It is the latest revision I would like to find. For example, If I have a number followed by 3 revisions I would have:
[Doc No]
ABCD123
ABCD123+200
ABCD123+300
ABCD123+400
I need a query to give me the latest revision, in this case
[Doc No]
ABCD123+400
There is a possibility there are no revisions in which case the latest will be
[Doc No]
ABCD123.
How is this possible in a query???
My table appears like this with the following columns:
Name| CUSTNo| ORderNo.| AcctDate| OrderDate| OrderDescription
I would like to write a ms-sql query to return the records of each customer number with the latest OrderDate. One more thing, if the customer has two separate order numbers on the same date(should be the latest date), the query should be able to pull up both the records.
I tried with the code below but its taking a long time to execute and finally hanging up MS ACCESS.
SELECT * FROM TableName AS a WHERE Not Exists (SELECT *
FROM TableName b WHERE b.CUSTNo = a.CUSTNo AND b.OrderDate >= a.OrderDate);
How do I query from this table:
ORIGINAL_table
FName | END_Date|
--------|---------------|
John| Oct 09, 2014|
John| Oct 15, 2014|
John| Oct 25, 2014|
Mike| Dec 10, 2014|
Mike| Dec 15, 2014|
Mike| Dec 20, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 10, 2014|
Jimm| Dec 15, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 01, 2014|
Alex| Dec 05, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|
Thom| Oct 10, 2014|
Thom| Aug 10, 2014|
The output will be:
FName | END_Date|
----------|---------------|
John| Oct 25, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|
The query table(output) will just have to be latest date("END_Date") per "FName"
I try to make a query that can show me data from the lastest and max date from the table i have ( see attchment file )
I try using max but still the result is not what i am looking for...
I have a table of accounts and a table of rates. There is a one-to-many relationship between them (i.e. each account can have multiple rates, each with their own - unique - effective date)
I'm trying to build a query which will show me all of the accounts in the accounts table and the most recent rate (based on the effective date) for each of those accounts.
This is as far as I've gotten with the SQL :
Code:
SELECT [tblAccounts].[AccountID], [tblAccounts].[AccountNumber], [tblAccounts].[AccountName], [LatestRate].[IntRate], [LatestRate].[EffectiveDate]
FROM [tblAccounts]
LEFT JOIN
(SELECT TOP 1 [tblRates].[AccountID], [tblRates].[IntRate], [tblRates].[EffectiveDate]
FROM [tblRates]
ORDER BY [tblRates].[EffectiveDate] DESC) AS LatestRate
ON [tblAccounts].[AccountID] = [LatestRate].[AccountID]
But this can't work because the [LatestRate] subquery can only ever return one record (i.e. the most recent rate across all of the accounts)
I need the most recent rate for each of the accounts in the main query
(FYI - I use an outer join as it is possible for no rate to be available in the rates table for a given account, in which case I want to return the null value rather than omit the account from the resulting dataset...)
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 Replies View RelatedHello.
I have been given a set of pricing tables in Excel that, based on the type of work done, show a dollar amount for time ranges. In other words, if WorkType = A, the appropriate pricing table is referenced and the amount of time in minutes is found within ranges ("0 - .3333", ".3333 - .6500", etc) shown in hour decimals (.3333 = 20 minutes), then an associated dollar amount is reported back. (I know... very awkward, but not my choice). The time ranges are not consistent in size either within a particular pricing table nor across tables. The person using these tables is doing this manually now.
What I need to be able to do is enter the WorkType and duration, and get the dollar amount from the table, even though the duration is not going to exactly match the value in the table. In Excel's VLOOKUP, you can put in amounts that not exact, and the function finds the highest value less than your entry. Can Access do that? I tried reading up on DLOOKUP, but I don't think that is what I need (or maybe I just don't understand it). I'm not sure of an efficient table setup or how to get the correct output.
Thanks for all your help!!
Tom
I'm making a dispatch call database that will be sorted at the end of the month based on call types and which shift that call was originated on. Our shifts are 6a-2p, 2p-10p, 10p-6a.
I am figuring that the time would be converted to minutes and then the display would be figured based on which times the minutes occured between. IE: the call origintaed at 0100hrs = 60 minutes. It would figure that 60minutes translates to "Night Shift".
How would I convert time into minutes and how would the if/then be established?
Thanks so much for all your help
Dave
Hey all,
I'm trying to find a solution to a problem. Is there any way using VBA to generate a random number, based off the current system date and time? The theory is that a random number seeded by the system date/time will be truly unique and the possiblity of it ever occuring again will be nearly nil.
Or is there a better way to create unique ID's (such as member ID's, or something similiar) based off the system date/time that can be used as a primary key in a relational db.
Hi
How do you run a procedure that activates at a set time?
I want to run a procedure that moves a log file to an archive directory at midnight (or at anytime after this when the PC is next turned on).
Thanks for any help...
Mat
I have the following query:
TRANSFORM Count([Copy of qryDailyCountOfValidations].Staff_Number) AS CountOfStaff_Number
SELECT [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time]
FROM [Copy of qryDailyCountOfValidations]
GROUP BY [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time]
PIVOT Format([Start Time],"Short Time");
it shows the number of employees that start a shift at a particular time and the time the shift ends, on any day.What i need to show on a graph is the count number of employees starting at x time and plotting the number of hours the employees are on shift until they leave or another employee starts....
-the y axis is the count of employees
-the x axis is the start time of their shift
10
9
8
7 xxxxxxxx
6 x x
5 x x
4 xxxxxxxx x
3 xxxxxxx x
2 xxxxxxxxxx
1
0
00:00 03:15 05:30.........................23:59
So the above mock up says 3 employees start at 00:00 and continue until 03:15 then another employee starts, then 4 employees continue 06:00, then 3 more employees start, 7 all together, then 5 employees leave later etc...
-the level of detail needed is to plot for every 15 minutes.
-The [end time] is not strictly needed, just included to show what columns i have available
achieving this on a graph on an access 2010 report..i have updated a test database so you can see what the data looks like.
I will simplify the situation as I can , I have one table in my DB with the following fields :
ID , APP_DATE , APP_TIME , DETAILS , APP_STS
I want to have a pop-up form 5 minutes before the appointment time !!! (That is it)
- I know it's better to manage the appointments within outlook.
- I do not want to connect my DB with outlook appointments.
- I want the reminder form to have a "snooze for 5 minutes" button and "Done" Button to change the appointment status (APP_STS) to done.
- I think we are going to use on timer event (which is totally new for me)