Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can this be done? (access 2000)

    1. I have a query where I

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can this be done? (access 2000)

    It isn't at all clear what you want to accomplish with this. Are you copying records between tables in the same database? If so, to what purpose?

    Normally you simply use queries to return records for a particular day, etc. You don't ordinarily copy whole records between tables because then if a record gets changed in one place or the other, the integrity of the data is compromised. The point of a relational design is to have the data in a single place and be able to query it when you need it elsewhere.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can this be done? (access 2000)

    Ok, let me start from the beginning. I am creating a report in Excel using the data in Access table. What I want to do is extract data that has been entered on a day. I could do that with a select query. But the thing is, if any of the data are an amendment of an old record, I want to go back and select the old record and display and it with the amended record. What I was wondering is that whether I could insert the old records ID number into a select query automatically so that every time there is an amended record, it could go back and select the old record. Can this be done? If so, how? Hope It makes some sense this time. Many thanks for the help.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can this be done? (access 2000)

    You have people entering new records to amend old records? If there's a key field in the record pointing to the old record, you can do an outer join from that table to another instance of that table joining the reference field to the primary key in the second instance of the table, but I don't that will give you quite what you seem to be looking for. You could probably do it with a subquery to filter the records in the query, though. Do you understand how to use subqueries as criteria?
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can this be done? (access 2000)

    Yeah, if there is an amendment to be made then the user calls up the old record and changes whatever and this is saved as a new record in the table and this record would have a feild which is calle "RecPrev" which has the "RecNo" of the old record. No, I can't do anything in VBA. Thanks for the help.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can this be done? (access 2000)

    I didn't say anything about VBA. Look up subquery in on-line help. Subqueries are embedded queries used to return a specific set of values, for example, a list of all non-zero RecPrev field values for the current date. You could use that in the criteria of RecNo with the In operator as an OR condition for returning records.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can this be done? (access 2000)

    (SELECT[ZZ_recordprevios]FROM[ExSFM]WHERE[ZZ_recordprevious]IsNotNull)
    Is this right? Is this how you say not null? ExSFM is my first query which extracts records for that day, ExSFM1 is the query which extracts the old record and I have this in the criteria for the RecNo of the ExSFM1.

  8. #8
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can this be done? (access 2000)

    Ok, there is no problem with the statement but its not fetching the RecPrevious number from ExSFM query. Why is this? What am I doing wrong?

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can this be done? (access 2000)

    Didn't you say that ExSFM returned the current day's records? Then I have two questions.

    First is, are there any records in the current day that actually have anything in ZZ_recordprevious? If not, you won't get anything in your query.

    Second, is there ever a null value in ZZ_recordprevious? If the field defaults to a zero (which it probably should), then it won't ever be Null and you need to select those that are <>0 instead.

    Does your criteria line read something like this?
    <pre>In (SELECT [ZZ_recordprevious] FROM ExSFM
    WHERE [ZZ_recordprevious] Is Not Null)</pre>

    What that does is tell Access to return all records in ExSFM1 that have a RecNo that is found in the list of ZZ_recordprevious values in ExSFM.
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can this be done? (access 2000)

    Hi Charlotte,
    Yeah I Have got some records in ExSFM that has been Amended and have the record number in the [ZZ_recordprevious] field, when the records aren

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can this be done? (access 2000)

    Post the entire SQL for the ultimate query. It's too hard to tell what you've done looking at bits and pieces. Just open the query in SQL view and copy and paste the whole thing into your post.
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can this be done? (access 2000)

    SELECT TradesToExport.ZZ_recordprevious, TradesToExport.MappedSectype, TradesToExport.ZZ_recordnumber, IIf([fund]="SFM_1","QP-01","QP-02") AS Account, TradesToExport.id, TradesToExport.cpty, TradesToExport.clr2, TradesToExport.td, TradesToExport.settles, TradesToExport.q, TradesToExport.tc, TradesToExport.tp, TradesToExport.tai, TradesToExport.tax, Nz(IIf(([MappedTT]="Buy" Or [MappedTT]="L" Or [MappedTT]="Buy Cover" Or [MappedTT]="BC"),(([q]*[tp])+[tc]+[tai]+[tax]),(([q]*[tp])-[tc]-[tai]-[tax]))) AS [NET AMOUNT], TradesToExport.fund, TradesToExport.cancel
    FROM TradesToExport
    WHERE (((TradesToExport.ZZ_recordnumber) In (SELECT [ZZ_recordprevious] FROM ExSFM WHERE [ZZ_recordprevious] Is Not Null)) AND ((TradesToExport.td)=#8/21/2001#) AND ((TradesToExport.fund)="SFM_1" Or (TradesToExport.fund)="SFM_2" Or (TradesToExport.fund)="test1")) OR (((TradesToExport.td)=#8/21/2001#) AND ((TradesToExport.fund)="SFM_1" And (TradesToExport.fund)="SFM_2" And (TradesToExport.fund)="test1"))
    ORDER BY TradesToExport.td;

    Charlette, do u c anything wrong with this query?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •