Problem setting criteria

[laurat]

I need to create a query that shows if any records with the same part number were created on the same day. I am not looking for a specific day or part number. I need it to go through all records in the table and only display those records that have the same part number AND were created on the same day.

Is there any way to do this??

 

[Jon K]

Paste in the SQL View of a new query (replacing with the correct table name and field names):

SELECT *
FROM yourTable
WHERE PartNumber & CreationDate in (select PartNumber & CreationDate from yourTable group by PartNumber, CreationDate having count(*) >1)
ORDER BY PartNumber;

 

[laurat]

Could you please type out exactly what I have to put in there. The fields are Part Num and Date Orig. I have not written SQL statements before so I am a little lost. Thank you so much for your help. I really appreciate it.

Laura

 

[Jon K]

SELECT *
FROM [table name]
WHERE [Part Num] & [Date Orig] in (select [Part Num] & [Date Orig] from [table name] group by [Part Num], [Date Orig] having count(*) >1)
ORDER BY [Part Num];


Use the correct table name. There are two instances.

Square brackets are needed if the table name and field names contains spaces, (though it is not considered good practice to include spaces in them.)

Select * will return all the fields from the table. If you only want to return the part number and creation date fields, you can use:-
SELECT [Part Num], [Date Orig]

 

[laurat]

Thank you so much. I tried it out and it did pick up some records with the same Part Num on the same day, however it did not pull out all of them. Any suggestions as to why it missed some??

 

[laurat]

I figured out the problem, no solution

I think I have figured out why it is not pulling all the records. For some reason in the combined field of Part Num and Orig Date that the query creates with the select statement, the date is displayed with the time as well (even though it is not like that in the Date Orig field), so the date may be the same but the times are off so the query sees the records as not matching. Is there a way to solve this??

 

[Cosmos75]

Short Date format?

Is you data formated as Short Date in the table? I think you can format the date in the query as a short date (using the graphical inteface and not the SQL view), but in SQL I am not sure (I am hopeless in SQL!).

 

[laurat]

Thank you. Well, the format was set to Short Date. However, I wanted it to automatically fill in todays date so I used the Now() not knowing that it put the time as well. I changed it to Date() about a week ago for another problem it caused so the most recent records do not include the time. I did go into the properties of the Date field but have found nothing that changes the already existing records with the time in the date. The only way I have figured out how to get rid of the time added to the end of the date is to manually delete it for each record. This would take some time though, there are about 3000 records. Any other suggestions would be greatly appreciated. Thank you.

 

[Cosmos75]

However, I wanted it to automatically fill in todays date so I used the Now() not knowing that it put the time as well.

Yes, I think using Now() does include the time and Date() doesn't.

The only thing I can thing of right now would be to export your
table as an excel file.
- Format you data column as a number (should be a number with some decimals).
- Add a column next to your Date Column and add the formula Trunc(DateColumn) where DateColumn is D1, D2... (depending where you date colum shows up in your excel file.
- Copy the values from the Trunc column to the Date column (remember to use PASTE VALUE and not PASTE FORMULA after choosing Paste Special).
- Delete the Trunc Column and past back the data into your Access Table.

Might be able to do this in Access but I am not sure how...

 

[Cosmos75]

Create a query that takes your original date (I'll refer to that field as [Long] ).

And use this formula

TNum: Int(CDbl([tblNAME]![Long]))

And then copy the query column for TNum into the table with [Long]

Now, go back to the table in design mode and format [Long] as a Short Date.

Lemmw know if that works.

As always, make a back-up of file BEFORE you try this in case anything goes wrong!

EDIT: Anybody know of a way to do this with an update query??

 

[laurat]

I am not understanding entirely, could you be a little more specific. Do I create a new query with only the date field in it and put that formula in that query?

 

[Cosmos75]

Yes, Create a new query with the date field in it and add an expression in your query using the formula I gave to you.

If you don't know what I am talking about, here goes.

- Click the design new query button
- It'll ask you to add table/query, go ahead an select the table with the date field in question and click OK.
- Drag the date field into the query grid
- in a column next to field enter the formula I gave you (replacing the table name and field name with your own field names
- Run the query
- Copy the TNum column by clicking the header, right-clicking and choosing Copy.
- Check to see if your results are OK, if so move on...
- Go to your table, click the date field header, right-click and choose paste.

Let me know if this does or doesn't work.

 

[laurat]

I don't think it worked, in the field that was added with the formula I am getting values like 37216, 37219 when I run the query.

 

[Jon K]

In table design, change the Data Type of the field to Date/Time.

37216 is 21/11/2001 and 37219 is 24/11/2001


(In fact there is an easier way to get rid of the time of a Date/Time field. In table design, change the Data Type of the field to Number, Field Size Long. Save the structure. Reopen in table design, change Data Type back to Date/Time.)

 

[Cosmos75]

laurat,

Sorry, you need to add another field to the query (name it whatever you want), make that equal to TNum and format it as a short data ans use that to copy back into your table.

You may want to try Jon K's aproach! (mine's kinda convoluted)

 

[laurat]

Thank you both so much for all your help.