Search results for query: *

Hi all, Hope someone can offer a suggestion with this one. I have been tasked with designing a database from a paper-based form. However, the paper-form has not been designed with databases in mind. Specifically, I have one question on the form as follows: Tick all that apply (then lists 15...

Thing is the DateTimeY field is how I determine which records I want to keep. DateTimeY contains the date that the record was generated. I need to keep later records and delete older records that are meant to be replaced. The model may be rubbish but unfortunately that's what I have to work...

Well I made it work, using a bit of a fudge admittedly. I altered the table structure to add an Autonumber field based on a replication id. Then I amended JonFer's query above to: SELECT Max(A1.ID), A1.[PATID], Max(A1.DATETIMEY) as DATETIMEY,A1.[SPELLNO], A1.[CONSEPNO], A1.[STARTDT] FROM...

JonFer, Thanks for your suggestions. I tried your SELECT query and it gave me the correct number of records (15356). However, when I expanded the query to include the rest of the columns in the table, the record count went up to 15566. Have you got any ideas as to why this happened? PompeyJon

I want to retain the records selected in the query you suggested and delete all other duplicates. I also obviously need to retain all unduplicated records in the table. PompeyJon

Thanks - that seems to select the correct records. I guess the easiest thing to do would be to use that SQL as the basis for a MAKE TABLE query? Or would it be possible to use the above SQL in a DELETE query (e.g. DELETE records where NOT IN the recordset)? PompeyJon

Sure: This is what I'm currently getting with the existing SQL: PATID SPELLNO CONSEPNO STARTDT NUMBEROFDUPS 000031 123 01 20040401 4 000056 456 01 20040402 2 What I want is DATETIMEY PATID SPELLNO CONSEPNO STARTDT 20040501 000031 123...

Hi Folks, I have the following query: SELECT First([APCGeneral].[PATID]) AS [PATID Field], First([APCGeneral].[SPELLNO]) AS [SPELLNO Field], First([APCGeneral].[CONSEPNO]) AS [CONSEPNO Field], First([APCGeneral].[STARTDT]) AS [STARTDT Field], Count([APCGeneral].[PATID]) AS NumberOfDups FROM...

I know that linked tables would solve my problem - however, the user in question doesn't seem to understand that linked tables are still "live" - we have had situations where records have been deleted, etc. What I really need is a way of executing an SQL query on the SQL2K database, but using...

Hi All, My situation is thus: I have a SQL2K database that I need to access from Access2K. Now I know I could setup lots of linked tables using ODBC, but it is essential that 2-way access to the SQL2K database is not in effect. I was thinking of designing a macro that would first make a copy...

Thanks for the responses so far everyone. To add a little more to the discussion. I work for an NHS (UK National Health Service) organisation. The data I will dealing with is records of patient episodes in hospital. The specifics of the data is not as important as the counts for various...

Hi all, I would welcome any and all suggestions on the following topic. I have been commissioned to design a Data Warehouse from scratch for our organisation. The particulars of the project are as follows; 1) Annual amount of data approx. 2.5Gb 2) Potentially 50-100 end users 3) Requirement...

OK. I solved my own problem. The syntax I actually needed was as follows: =DLookUp("PCTDesc","GeneralPractitioners","GPCode = '" & Forms!Main![GPCode] & "'") As per usual, it was a case of not specifying that GPCode is actually a text field and so...

Ken, Not sure what you mean. The GPCODE field is a combo box with a Row Source of: SELECT DISTINCTROW GeneralPractitioners.GPCode, GeneralPractitioners.GPName FROM GeneralPractitioners ORDER BY GeneralPractitioners.GPName; Do you mean I need add PCTDesc into the query above and add a text...

Hi All, I am trying to get the following DLookup function to work, but it's not happy: I have a form (MAIN), based on the table (COMMISSIONING). The form contains a simple lookup to a field (GPCODE) in a second table (GENERALPRACTITIONERS). I would like my DLookup control to return a text...

Hi everyone, Can someone tell me how to the following with VBA (I know how to do it in SQL!!!). Private Sub Form_Current() ' If value in GPLookup text box is not a Sandwell PCT ' display value in GPLookup in red and display FinLiability label. If...