Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: I thought I knew Access...

    I am stumped... I have just pulled a bunch of data from a mainframe that I intend to use for a balance report. This may very well be a brilliant display of how much an Access hack I am...

    I have created two tables:
    1) Collections Data
    2) Monthly Allowances

    For my first report, I desire the following fields from each table:
    1) Collections Data
    - Provider Number
    - UPP_CHECK_DATE
    - COLLECT_AMT

    2) Monthly Allowances
    - Provider Number
    - Date_Processed
    - Payment
    - ProcMonth
    - ProcYear

    Now, I created a form that has fields to limit the date range or provider number included in the report. I wanted to include these variables in my query as well. We can call these unbound fields:
    [forms]![Balance_Report]![Chkdate]
    [forms]![Balance_Report]![provider]

    My question: How do I do this?!?! The criteria will apply differently to both tables... I have no relationsips defined, either...

    ANY pointers would be greatly appreciated, I am a pretty quick study!

    Thanks,

    Mitch
    Last edited by jimmyswinger; 12-06-04 at 15:46.

  2. #2
    Join Date
    Nov 2004
    Posts
    34
    Base your form on a select query. If you want the form to show data from both tables then just add both the tables to the query. In the field that you wish to limit the data by, just use something like this in the criteria: Like [Enter project number] & "*". The asterisk in the quotations (wildcard character) means that it will return all results that contain the user defined criteria as well as anything else after, i.e. If the user entered '123' as the criteria, the query would return results containing 123, 1234, 1235, 123hte, etc. Sorry if this reply seems a little simpleton, I just prefer to explain everything as simply as possible because I don't know your experience level with Access.

  3. #3
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    The place I'm getting stumped at is this: say one table has five hundred records and the other has five thousand...

    I can't seem to get my head around the idea; How does the query result return records? i continually get weird results, looks like lots of duplicates and whatnot.

    Here is what I'm working with:

    SELECT Collections.PROVNO, Collections.UPP_CHECK_DATE, Collections.COLLECT_AMT, [Monthly Allowances].Provno, [Monthly Allowances].PPODAmt, [Monthly Allowances].ProcMonth, [Monthly Allowances].ProcYear
    FROM Collections, [Monthly Allowances]
    WHERE (((Collections.PROVNO)=52) AND (Left([upp_check_date],4)=2004) AND (([Monthly Allowances].Provno)=52) AND (([Monthly Allowances].ProcYear)=2004));

    I'm have a feeling that this is one of those things I'm going to have to keep chipping away at until !eureka! i finally crack the nut...

Posting Permissions

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