Search results

Please note that if the customer only has one purchase it will not show in the query above, since they do not have a second to last purchase to reference. If however you want to show the only (one purchase) purchase a customer made along with the second to last purchase of customer who made...

You can do this using a query alone and it would be much faster than anything VBA can put together. First You need to create a query that gives you the last two orders that a customer purchases. SELECT Table1.ID, Table1.CustomerIDNumber, Table1.DateOfPurchase, Table1.Item FROM Table1 WHERE...

http://www.mvps.org/access/modules/mdl0008.htm dallr

If you structure is similar to this : tbl_Members MemberId (PK) TransportID (FK) FirstName LastName DOB tbl_Transport TransportID (PK) TransportType If I understand your requirements correctly you can use. SELECT COUNT(M.TransportID) As RecordCount, T.TransportType FROM Tbl_Members M INNER...

Hello Cait, I looked at the information you provided and have some comments. I started to build the data model but I wanted some feedback to clarify certain issues. 1. You mentioned that you wanted to track contractors that and the amount of times they visited a location. I think this should...

Hey Bob i was just passing by and saw the good news. Let me say congratulations on this wonderful achievement. Keep up the good work. Dane a.k.a dallr

You can do this in one query but i decided to do it in 3 just so you can understand the steps. You can always put it into one query afterwards if you wish. Step 1. Create a query that will show the first time a port and a connexion was created as a record. I called this query...

There are many different ways to do this here is another one Public Function TblExists(strTableName As String) As Boolean 'ADO Method Dim obj As AccessObject Dim dbs As Object Set dbs = Application.CurrentData TblExists=False For Each obj In dbs.AllTables If obj.Name = strTableName Then...

By having the name of the criteria that is doing the filtering exactly the same you can achieve this. It will only pop up twice instead of 4 times. use: WHERE (((IntakeCall) Between [FirstDate] And [LastDate]) AND ((DropOutCall) Between [FirstDate] And [LastDate])); If you are using a forms...

Hello Cait, please forgive me for not posting on the day i said i would post. I was working on a project and had a very tight timeline. I am a man of my word and i promised to help. The project was finished 2 days ago and will to assist. So, do you still need help on your table structure? dallr

Hello Cait, I just wanted to report back in. Sorry for not responding as yet but over the pass few day have been busy. I will print out your attached details and email you from home later. Dallr

Ok my bad. I misunderstood what you said, so i do appologize. Cait , Feel free to continue in this thread if you wish re: normalization. Dallr

Hum..... Don't mean to get touchy here but, I never stated that I was not up to the task. Maybe I being judged on my post count in this forum..... :( I am above average at normalization to say the least. One can say "self praise is NO praise". :) Although that might can be the case, I am...

Even if the aforementioned can happen, the first set of SQL I posted in my last response will cover this without needing to store the year separately. Because, when you do a “datediff()” on the last service date “max(Serviced_date)” The property would not show up on your listing as needing a...

"else why not Year_Serviced" It all depends on the Business rules that need to be applied. However, from the brieft description noted by Cait, I still say that the service_year is irrelevant. If you want to find Property that are overdue for a service then Use the following SQL. SELECT...

Glad you got your solution from Mile-O. However, I want to endorse what he mentioned in his first post. Your database is not properly normalized and you should stop if you can and correct this before moving further. This would save you plenty of problems and workarounds in the future...

Cait, why do you have a service date and a service year. Having a service_year is redundant data when the year can be calculated from the Service_Date. Dallr

Try not to use marcos and focus on learning VBA instead it will be more beneficial in the long run. Dim SQL as string 'insert the data into the tblarchive SQL ="SELECT tblTray.* INTO tblArchive FROM tblTray" CurrentDB.Execute SQL 'Delete the records from the old table. CurrentDB.Execute...

I would recommend that you actually import the data. As Moniker mentioned linking to these types of files can cause problems. From personal experience i have seen where linking to a text file caused just one (1) record to not to show. When looping through the recordset the count would have been...