Queries :: Transaction Table - Find Last Record For Each Day

Oct 7, 2013

I have a table that contains records of transactions. I need to find the last record for each day.

DBTransIDtransDay UID
1931219/17/07 8:50 AM128
1932879/17/07 9:13 AM128
1932929/17/07 9:14 AM128
1933049/17/07 9:16 AM128
2388149/18/07 4:54 PM128
2388449/18/07 5:06 PM128
2388459/18/07 5:06 PM128
2388469/18/07 5:07 PM128
2389299/18/07 5:45 PM128
2389309/18/07 5:45 PM128
2389319/18/07 5:45 PM128
2391299/19/07 8:55 AM128
2391389/19/07 8:57 AM128
2391399/19/07 8:57 AM128
2391409/19/07 8:57 AM128
2391419/19/07 8:57 AM128

A script that I found that appeared to do what I needed it to do:

SELECT [TT Transactions - KT].DBTransID, [TT Transactions - KT].transDay
FROM [TT Transactions - KT]
JOIN (SELECT MAX(transDay) Max_transDay_By_Day
FROM [TT Transactions - KT]
GROUP BY convert(varchar, transDay, 112)) t2
ON t1.transDay = t2.Max_transDay_By_Day

But it returns a "Syntax error in FROM clause" that I can't figure out.

View Replies


ADVERTISEMENT

Queries :: Find Records Without Associated Record

Aug 3, 2013

I have the two tables in my access DB:

Property Information
Lease Information

I need to write a query which shows where a property does not have an associated lease, or where there is no current lease associated with it.

Property Info:
PropertyID
Address

Lease Info:
LeaseID
LeaseStartDate
LeaseEndDate
PropertyID (linked field to property table)

How to show expired / not current leases, but can't seem to show where there has never been a lease on a property.

View 4 Replies View Related

Queries :: Find Previous Record In A Query?

Jun 13, 2015

How do I find the previous record in a query using the autonumber field?

View 10 Replies View Related

Find The Last Record In A Table

May 30, 2005

Hi,
Newbie problem. I want to display on a form the last entry or (record) from a particular field of a table
(the date field of the last club event). I have set the Form Property of 'Data Entry' to 'Yes' to prevent the user from
having to scroll through records to get to the next blank record. Could someone kindly give me a clue how to go about
displaying that last record of the field. Thank you.

View 1 Replies View Related

Transaction Table

Jul 14, 2005

Hi,
I don't want to abuse anybody's patience but I have yet a couple of other quick question about transaction table.
I am using a summation query for the stock on hand where by keeping it simple: stock = sum of movement in the table. What hapens if a simple function is used to calculate the stock. ie: Stock=[Qty Declared-(Qty Declared-Qty Received)-Rejects]-Qty Allocated

All those Qty belong to the same field then I can't just run a sum.

My other concern is that:
Qty Declared
Qty Received
Qty Rejected

are three values entered at the same time but in the same (transaction type) field so data like the date would need to be entered 3 times for each item received?

Thank you very much for your time and patience again,

View 3 Replies View Related

How To Find Record On Form Based On Multiple Queries

Jun 23, 2006

I currently have a form to manage inventory. On the left side of the form, I have a list box that shows all of the items in the database and finds the record on the form based on the selection.

I would like to have toggle boxes or something underneath the list box that when clicked will sort the box by vendor and item and another that when clicked will sort the list by part number and then select the record on the form based on the selection at that time.

I have no idea how to accomplish this...can anyone help?

View 1 Replies View Related

Queries :: Detect When Query Fails To Find A Record

Oct 29, 2013

I have ASSET_TYPE, MANUFACTURER and MODEL tables.

I have a Table ASSET_COMBO that links to the above tables.

I have a form to create amend an ASSETS table. The form uses combo fields to filter the options available to the user. I.E the User Picks Manufacturer then selects from list of Asset Types provided by that Manufacturer, then selects from list of Models.

I want to create a form to allow create and amend records on the ASSET_COMBO Table.

The problem I have is that I want to ensure that there are no duplicates on ASSET_COMBO Table. I know that I can Select Multiple keys from the table with no duplicates allowed. But from what I have read this can cause problems. Is there a simple way that I can detect that a query on the ASSET_COMBO Table has returned a valid record or has not found a record.

View 3 Replies View Related

Queries :: Search Many To Many Relationships To Find Single Record

May 22, 2013

I am trying to use a query to search 2 many to many relationships but i keep getting duplicate results because each record is slightly different.

I have a table with cameras, another users and another formats. I have a table linking cameras to users and another linking cameras to formats so it looks somthing like this:

tblCameras - tblFormatlink - tblFormats
tblcameras - tblUserslink - tblUsers

When i add all the tables into a query i get loads of results as expected because i get a result for every possible combination.

What i want is when i search for all cameras that are suitable for a user who is a beginner is to get just a list of unique cameras. When i do that search at the moment i get a duplicate record for each different format the camera can do.

View 4 Replies View Related

Queries :: Monthly Transaction Of Fee - Arrears To Be Shown

Jul 31, 2013

I have a query which is compiling monthly transaction of fee, the students paying through voucher. To every month the query is building records for students (Grouped), my problem is i have to show the arrears in the same query which would be on minus/plus of paid amount from actual fee both are in this query i can populate the arrears by simply subtracting both fields but the thing is that i am looking for a arrears which would be the adding total of previous arrears as to date transaction.

View 5 Replies View Related

Transaction Table Probs.

Feb 2, 2005

Hi,

I'm developing a database to handle various aspects of quality control reporting. I've got a working structure however, i think, i have gone off the track slightly... First I'll show you part of the structure:

(please see attached .jpg)

(hope that makes sence)... so effictively I have a many to many relationship.

I have forms for each of these entities (frm_parts, frm_Suppliers). The problem i have noticed is you can assign the part a supplier in frm_Parts but it does not appear in the transaction table as a record. Is this because i have based the field "Part Supplier" (a combo box) in frm_Parts on a query? If this is not the right way to go about it. how do i go about it?!
I have an idea... on this.. If i create a new combo box on the form I can use the wizard to select the supplier ID from the Supplier table (i believe) and "store" it in the transaction table... i think! But I want the user to select the Supplier by drop down list showing Supplier Number and Supplier Name.. not the ID.
Any help is much appriciated!

Next job...

I have a form where I would like the user to be able to enter, for example, a Supplier number and see if a record of the Supplier exists yet. Next I want the user to be able, assuming the Supplier record exists, to search for a record of a Part number from that supplier. Next I want them to be able to "select" that part and be able to open a form with a new record using that part number (this form holds details of a report rasied against that part).
See what I'm getting at?
I've had a go at this but it's been a couple of years since i did any detailed work on an Access database so somethings are a bit hazy!
I'm not asking you to do this for me, but I would much appriciate some pointers on how to create this sort of thing!

Many Thanks

Rob

View 6 Replies View Related

Queries :: Calculated Field To Find Latest Date For Each Record

Jul 11, 2015

If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:

LatestDate: MAX(Astart, Bstart, Cstart, Dstart).

View 2 Replies View Related

Queries :: Find Data In One Table And Put In Field Of Another Table Then Update

Nov 7, 2013

I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.

I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".

Order Details Table Fields and conditions/criteria:
ID - primary key
DiscountID - only when the DiscountID = 92
Voucher - only populated when Discount ID = 92

Codes table Fields and conditions/criteria:
ID - primary key
code = text field with a code like "einstein01", "einstein02"
Allocated = False

Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.

Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.

View 1 Replies View Related

Form To Find Record And Move To Another Table

Oct 11, 2004

I'm trying to make a form with a single text box that will search for the contents of the text box in a table. Once it finds a match, it moves the record into another table. Anyone know how to do this?

View 4 Replies View Related

Transaction Table For Inventory Control

Oct 27, 2005

After a lot of searching, printing and highlighting I want to double check that I have the right idea.

I have a simple inventory control database. From reading the forum, it seems the best approach is to drop the detail lines table of the purchase order (incoming) and the detail lines table of the sale invoices (outgoing) and make one table for all transactions.

The Transactions Table would also keep records of inventory adjustments from inventory counts, etc. Each transaction would be given a type so I know in which direction the stock count moves in (adding or subtracting) when I sum the totals for each item.

Otherwise, I need union queries to ensure all tables are considered in my calculations.

My database is simple (thankfully). Let me know that I am on the right track I like the solution but like any new student, a little reassurance is appreciated.

View 3 Replies View Related

Queries :: Make Table Query - Find Table Name

Oct 14, 2013

How can you determine the name of a table that has/is going to be created by a make-table?

View 2 Replies View Related

Modules & VBA :: How To Find Record In Table And Use One Of Its Field For If Statement

Jun 6, 2014

I have a table called login and inside that table is three columns: username, password and admin.

I have the username saved in a global variable called GsUser. How can i find the record in that table with the same Username as the string stored in GsUser and use that record for an if statement which sees if the value of the admin column is "Yes". Im trying to do it using VBA. Im not using a form where everything is bounded.

View 2 Replies View Related

Queries :: Create A Query Of All Employees Doesn't Have Any Transaction For A Certain Range Of Date

Mar 30, 2013

i'm trying to create a query of all employees doesn't have any transaction for a certain range of date and will also shows the last transaction date they have.i have two databases one is the transaction file and the other is the user file.

View 7 Replies View Related

Queries :: Find Dates In One Table But Not Another

Mar 14, 2014

I've got two tables, both are indexed by customer ID, with a series of dates against the customer ID. One has a list of all dates a customer was visited, the other is a list of dates where activity happened on the customer account

I want to get a list of the dates when the customer was visited but where no activity happened on the customer account, i.e. where there is a customer visit date on the customer visit table but no record for that date on the activity table.

How do I do that? I can find all dates where was a date was on both tables, but how to find where its on one but not the other

View 2 Replies View Related

Queries :: Find Value In Another Table And Return ID

Aug 8, 2014

I'm currently busy with something for my thesis as a student and I need to use Access for this. I'm not too new at access, I know how to do the very basics, let's say on the level of [if field contains *"text*", return x].

However I am struggling right now on something that shouldn't be too hard... I could do it immediately in Excel if there werent millions of rows..I have 2 tables. Table 1 regards a list of patent publication numbers (eg. WO2012024604A3) and additional data (publication date, title, etc), only the publication number matters for me now.

Code:
Table 1
publication numberWO2011085209A2
WO2011100754A1
WO2011112983A3
EP2342192A4
EP2342192A2
EP2205725A2
EP2205725A4
WO2012006540A3
WO2010008486A3
WO2012083136A1

Table 2 contains another list of patents that might cite/refer to Table 1's patents and additional data such as publication date.

Code:
Table 2
Publication Number Citing PatentsPublication Date Cited Refs - Patent

AU2001287375B2 1998-12-01 US5178882A | US4225581A | WO1998001161A2
AU2001288365B2 1990-02-24 US5967154A | WO1996039117A2 | US3699979A | US3943949A | US3838702A
AU2001288437B2 1999-03-09 US6087157A

[code]....

Now what I'd like to do is to create a third table which has for each of [Table 1].[Publication number]:

Column 2) A count of how many times the [Table 1].[Publication number] is found in [Table 2].[Cited Refs - Patent] ...

Column 3) In case a patent is cited more than once, return the [table 2].[publication Number Citing Patents] value of the earliest citing patent (so with the lowest Publication Date value).

For Column 2 I had expected it to be an easy count(iif( [Table 2].[Cited Refs - Patent] = "*"&[Table 1].[publication number]&"*")) command but apparently it's harder than that..

View 2 Replies View Related

Queries :: Find Records In One Table And Not In Another

Apr 2, 2014

I've got 2 tables, same structure, one [T-temp-Target] holds number of training units split by module a trainee needs to finish the course, the other [T-temp-Actual] holds what they've completed so far.

Both tables have structure
TRAINEEID
MODCODE
CountOfUnits

I'm trying to find the modules that they've not done yet so I can add up the units for them, only modules that have been started are recorded in the table of what they've done [T-temp-Actual], modules they haven't started yet aren't included in it. Here's the SQL

Code:
SELECT
[T-temp-Target].TRAINEEID,
[T-temp-Target].MODCODE,
[T-temp-Actual].MODCODE
FROM [T-temp-Actual] INNER JOIN [T-temp-Target] ON
([T-temp-Actual].TRAINEEID = [T-temp-Target].TRAINEEID) AND
([T-temp-Actual].MODCODE = [T-temp-Target].MODCODE)
WHERE ((([T-temp-Actual].MODCODE) Is Null));

View 2 Replies View Related

Forms :: Find Record In Table That Matches Textbox Input

Jan 5, 2014

I'm trying to build a search form.- Access 2007

I have a table (Table1), contains a 'part' field.

I wan to build a form with a blank text box (txtbx1) where users can enter a part description, this will search records in Table1>part and return a list of parts that match the textbox input.

Here's what I've done -

txtbx1 - property sheet - control source - part
event - on enter - ...

I've built an expression -

[Table1]![part] = [txtbx1]

hoping that this would match records 1n Table1 to txtbx1

View 10 Replies View Related

Tables :: Transaction Table Setup To Adjust Inventory

Apr 22, 2013

I have a database that I use to put in orders for our shop and keep track of our part informations as well as paint and packing materials. Everything works good on this but I am trying to create a table for adjusting quantities on hand for packing material based on the part quantities and for adjusting paint in stock based on information given to me after the job is run.

There is already a relationship between parts and packing material as well as parts and paint. When I put an order in I would like to have it adjust out that many packing materials that are related to that part. Once a job is run I need to be able to adjust out the amount of paint used.

For the paint side of this I want it to track the paint used by order, we are trying to get a grasp on how much paint we are using for parts so it is important for me to know how much and when.

I am thinking I need a table that connects paint to orders and has quantities in it, then create a query and do the calculations from there... I do not know how to accomplish that but it sounds like it could be right...

View 1 Replies View Related

Forms :: Opening A Form Based On Transaction Table

May 5, 2014

I wish to open a form based on the transaction table populate one of the foreign key fields with a selected value from a combo box from the switch board (with going to a mainform&subform). I know how to get the value of the combo box on the switchboard. I just know how to then store this value in the foreign key field. stSelectedCustomer = Forms![Switchboard].[CustomerID] gives me the value from the combo box on the switchboard. I then want to open the transaction form to open a new record and have Customer ID on this form be the stSelectedCustomer storing this value in the transaction table when the user enters values in the other fields.

View 2 Replies View Related

Queries :: Inventory Transaction Form - Append Query Data Type Mismatch

Dec 8, 2014

I'm receiving an error indicating there is a data type mismatch when running a query named qappInventoryTakeOn.

Data is entered into the Inventory Transaction Form. If the transaction type is "Take On", when the update button is clicked the record will be saved to tblInventoryMovements and then qappInventoryTakeOn should run to update tblInventory, but I keep running into the aforementioned error.

View 2 Replies View Related

Queries :: Find Duplicate Records In Table With Two Fields

Aug 29, 2013

I want query to find duplicate records, i have two field in one table

Cusip and category

cusip and category are many or duplicates

but in one cusip category should be the same if not then provide the cusip which has different category used

like this
CusipCategory
123R
456P
123R
456P
678Q
678Q
123A

result should be

CusipCategory
123R
123R
123A

View 6 Replies View Related

Queries :: Find A Way Of Overwriting Records In Table With New Data

Aug 6, 2014

I have a table where csv files get imported to on a daily basis. The key fields I am working with are a supply number and date. The problem I have is that sometimes the csv file will contain information that is correcting/updating information held in the table. This is creating duplicated records.I need to either create a query to find records that have the same supply number and date or find a way of overwriting the records in the table with the new data.

View 2 Replies View Related







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