Groups

    Microsoft Access - Reports and Forms

    EDIT: New problem now.. help with SUM feature in subforms?

    Hey there people,

    just stuck in on a saturday doing a database for some coursework for uni..

    Came across a major problem that i can't get around for me to carry out my forms..

    Basically in my table theres about 20 different entries, one of the fields has a check box

    so out of the 20 , 13 are ticked

    how do i make a report for the 13 and without the 7?

    Thank youuu

    8 Comments

    Not used Access for some years, but imagine you will have to base the report on a query. Within this query you will need to drag in all the fields you require in the report. Then still in the query, you need to put Yes in the criteria directly below this field.

    Once you base the report on this query it will filter out all the '

    Hope this is of some help, as I say, I haven't used Access for some time

    Original Poster


    allsa001

    Not used Access for some years, but imagine you will have to base the … Not used Access for some years, but imagine you will have to base the report on a query. Within this query you will need to drag in all the fields you require in the report. Then still in the query, you need to put Yes in the criteria directly below this field.Once you base the report on this query it will filter out all the 'Hope this is of some help, as I say, I haven't used Access for some time



    ah thanks for that,
    im going to have some food, will try that when im back!
    thank you for that

    and even if you havent used access for a while, access 2007 is sooo different to 03!

    In the query use the criteria "True" in the tick box column that will exclude those not ticked

    Learn SQL :-p

    Boom123

    Basically in my table theres about 20 different entries, one of the … Basically in my table theres about 20 different entries, one of the fields has a check boxso out of the 20 , 13 are ticked



    We'll assume your database table is called [MyTable].
    The column (field) that is "ticked" (or not) is called [Include].

    Boom123

    how do i make a report for the 13 and without the 7?



    Depending on how the [Include] column is defined (that is, what "data type" is has) use one of the "queries" below as the basis of your report to select the 13 records:

    SELECT * FROM [MyTable] WHERE [Include]=True
    or
    SELECT * FROM [MyTable] WHERE [Include]=-1
    or
    SELECT * FROM [MyTable] WHERE [Include]=1

    To select the (7) other records...

    SELECT * FROM [MyTable] WHERE [Include]=False
    or
    SELECT * FROM [MyTable] WHERE [Include]=0

    Please note that the asterisk (*) denotes that all columns in [MyTable] will be returned even though you may not wish to use the data from all of them.

    If you replace the asterisk (*) with a comma-separated list of columns that you do wish to see in your report then the query will execute faster & your report will be shown quicker.

    For example,

    SELECT [Name]. [Address]. [Date_of_Birth] FROM [MyTable] WHERE [Include]=True

    This will return all the Names, Addresses, & Dates of Birth stored in [MyTable] where the [Include] "check-box" is ticked (checked).

    (Assuming [MyTable] contains columns called [Name], [Address], and [Date_of_Birth] of course!)

    If these Structured Query Language (SQL) statements do not produce the results you expect, please post a further comment with your database table name, and the column names & their data types.

    Thank you.

    BFN,

    fp.

    Original Poster

    Hey man thanks alot it worked!
    Now i have a new problem

    I have a order form, with a subform inside it..

    How can i show a total of two fields in another text box within it?

    I was hoping that ..

    When someone types the quantity they want ordered, i want it to deduct it from the quantity on hand and multiplied by the item price to give a total..

    If you can help me with that i can pretty much get my purchase order form done too!

    I've been in uni for 7 hours today and i couldnt get this to work! no matter what i tried
    Damn lecturer has dissappeared and gone too, they have given us examples but their code/macros are to confusing to replicate within our databases

    Thanks for all the help, its very helpful!
    Edited by: "Boom123" 13th Mar 2011

    Is the sub-form linked to a query (or to a table)?
    Does the column where you wish to display a total exist in the (underlying) table (or query)?

    Or, is the "total" column not in the query/table at all?

    Is the sub-form able to be scrolled within the main (parent) form? That is, how many records are you expecting to display in the sub-form? Do they all fit within the (screen/form) space you have allocated to display the sub-form?

    Presumably the additional "total" column is not one that can be edited; it is just a calculation... the arithmetic addition of two other (numeric) columns. Do you validate the input of the two columns to be used in the calculation to ensure the data values entered are numeric?

    Would it be possible to host your Microsoft Access ".mdb" file so it can be downloaded for viewing in order to advise you better?

    Also, please confirm which version of Microsoft Access you are using.

    Thanks.

    BFN,

    fp.

    Original Poster

    Yes the subform is linked to a table,
    the "total" does not exist at all - just a new textbox. -

    there are no records in the sub-form, its like a shopping list, you click a drop down arrow and it highlights an item

    im using 2007, i can upload a mdb file for you if you want. but i got a meeting with my lecturer tomorrow morning so he might just be able to figure it out, till then i'll let you know.

    thanks again


    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Surprise the Miles136 summer burning caribou comp1122
      2. Surprise! The HUKD Summer Flamedeer Hunt 2017 **OFFICIAL THREAD** (trading …4542119
      3. Back to school: what's your views on your kids uniform and piercing rules e…27120
      4. If you had £50,000 to start a business what would it be ?2546

      See more discussions