Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    20

    Unanswered: Report Pulling fr Table with Changing fields

    Reports question for Access.

    I am generating a report in Access that pulls from a table with, let's say, 10 fields. The problem comes in, in that sometimes, there are only 8 fields, or 5 fields in the table.

    So I've set up the report with the table completely filled initially, but now, as the table decreases in fields (depending on client data), when the report opens up, it keeps asking for the missing fields, because they aren't there.

    Is there a way to do something on each of the report fields to basically say "If the table you're pulling from doesn't have field X, don't sweat it, and don't put a pop up message asking for a value, just keep going. Otherwise, if it does have info, then put that in."

    I'm no expert (by any stretch of the imagination) in VBA, but do I need to go this route, or am I asking something unfair for the reports to do?

    Any help greatly appreciated...and have a great Labor Day weekend,

    Dante33

  2. #2
    Join Date
    Jul 2003
    Location
    Kaukapakapa, NZ
    Posts
    15
    Hi Dante33,

    Are you using a crosstab query?

    If so I use a routine to add to the table fake filler records to ensure that there are say 12 months data even if the data you are querying only has 2 months worth.

    If not let me know and I will work on another solution.

    Regards

    Dale

  3. #3
    Join Date
    Aug 2003
    Posts
    20
    Originally posted by dalenz
    Hi Dante33,

    Are you using a crosstab query?

    If so I use a routine to add to the table fake filler records to ensure that there are say 12 months data even if the data you are querying only has 2 months worth.

    If not let me know and I will work on another solution.

    Regards

    Dale
    ----------------------
    Actually, I started using a Crosstab but when the fields change (as I mentioned in the posting), the report refused to even open the report at all...at least when I pull from a table, the report just asks me for the missing values and then opens up.

    If your crosstab solution works, I guess I can ditch the table idea. What I was doing is opening the crosstab, doing an "output to" to my disk, and then re-importing that back in as a table.

    If your solution involves code, is that something I can just put into the macro I've already created for this? Do I use the "RunCode" selection in the macro and just type in the code? As you can see I am a novice with VBA, so any help is appreciated.

    Thank you for your time!
    Dante33

  4. #4
    Join Date
    Jul 2003
    Location
    Kaukapakapa, NZ
    Posts
    15
    Hi Dante33,

    I'll explain what I do for the 12 months sales and it may suit you needs.

    I run a make table query on the tblSales to create tblSalesAnalysis:

    Customer Period Sales

    Where in our case the Period is 001 to 012

    As there is often not sales in all 12 periods I then append tblFillerRecords to this table.

    tblFillerRecords is only 12 records, one for each period.

    Blank Customer field, Period, blank Sales field.

    Then I run a Crosstab query on tblSalesAnalysis and there will be columns for all 12 periods.

    In the report based on this crosstab query Filter for Customer is null.

    These queries and the report can be in a marco.

    Hope this helps.

    Dale

  5. #5
    Join Date
    Aug 2003
    Posts
    20
    Originally posted by dalenz
    Hi Dante33,

    I'll explain what I do for the 12 months sales and it may suit you needs.

    I run a make table query on the tblSales to create tblSalesAnalysis:

    Customer Period Sales

    Where in our case the Period is 001 to 012

    As there is often not sales in all 12 periods I then append tblFillerRecords to this table.

    tblFillerRecords is only 12 records, one for each period.

    Blank Customer field, Period, blank Sales field.

    Then I run a Crosstab query on tblSalesAnalysis and there will be columns for all 12 periods.

    In the report based on this crosstab query Filter for Customer is null.

    These queries and the report can be in a marco.

    Hope this helps.

    Dale
    Thanks Dale,

    Your idea worked! Thank you! My coworker also showed me another way that might just be 6 of one, 1/2 a dozen of the other. He suggested making a master table (like you did-tblfiller), and then instead of running an append macro, just running a make table query, joining the tble filler PERIOD to your tblSalesAnalysis (linking the PERIOD fields), pulling in all the fields you need from your tblSalesAnalysis and then you'll have created a table that you can use to run a crosstab query.

    Like I said, I think it's a push as to which one works "better", but I thought I'd just share that with you in case you wanted to try that route.

    Thanks again for all your help...it's so great that a New Zealander, and a guy from Los Angeles, can exchange ideas. That's one good thing about this web.

    Dante33

  6. #6
    Join Date
    Jul 2003
    Location
    Kaukapakapa, NZ
    Posts
    15
    Hi dante33,

    Glad to be of help.

    Like the suggestion your co-worker made. I'll try that.

    Agree the web's a great place.

    Have you looked at Tek-Tips?

    Regards
    Dale

Posting Permissions

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