Results 1 to 7 of 7
  1. #1
    03lances is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    4

    Question about a select query.

    Hi all, I had a question that I really hope will be easy. Here is the deal, I am in the process of converting my personal financial data (just day to day household expenses) from the tables in word we have used since '05 into Access now that I am taking courses to learn how to use this software. Importing the data isnt a problem and I have made 2 queries that work great, one I can choose to list the businesses of my transactions and total up $$$ and the other query I can list a type to total them up (gas, groceries etc.) Both queries work great as long as I am only referring to one table.



    Now keep in mind here I am new at this so please to flame me too hard,
    I have set up the database to give each month its own table and I made a "master table" if you will for the year with just two fields, month name and month ID. I made a matching ID field in each month table and set a default value (ie January table has it set at "1", Feb table default is "2" so every row in jan will have a 1, in feb will have a 2 etc.). I created a one to many relationship with the master table out to every month table linking the ID fields. My thinking was that the january row in the master table would point to every row in the january table and so forth.

    I really want to just have 2 queries and be able to run it and point to a specific table and then list the business or item data for just that table, please help how can I get this to work? I really dont want to make 24 queries lol.


    Oh ya I should mention that I have access 2010 64 bit and win 7 on my laptop I bring to class. The database I am referring to in this post is actually on my desktop which is running Vista and access 2007 if this matters.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    A table for each month is bad design. Should be one table. Then can apply criteria in query to view only desired month records. Month can be calculated from a date value.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    although you have given an indication, would be helpful, if you provide the kind of data that you plan to store.
    off the cuff, perhaps, we should not be storing data monthwise in separate tables.
    A single table for datewise expenses & related category tables, etc should suffice.
    Also, very likely, you should be able to get free personal expense access tools online, though, designing one by oneself, would be really satisfying (I personally would take this path).

    Thanks

  4. #4
    03lances is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    4
    Quote Originally Posted by June7 View Post
    A table for each month is bad design. Should be one table. Then can apply criteria in query to view only desired month records. Month can be calculated from a date value.
    I tried this as first. I used brackets in the criteria row so it will ask me what parameter I want. I set it to first ask for a date but when I tried to retrieve say a swath of data a week in size (for example I put "Between 10/1/2012 and 10/15/2012" in the parameter window) it kept coming up with an error message about too many parameters or variables entered.

  5. #5
    03lances is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    4
    Quote Originally Posted by recyan View Post
    although you have given an indication, would be helpful, if you provide the kind of data that you plan to store.
    off the cuff, perhaps, we should not be storing data monthwise in separate tables.
    A single table for datewise expenses & related category tables, etc should suffice.
    Also, very likely, you should be able to get free personal expense access tools online, though, designing one by oneself, would be really satisfying (I personally would take this path).
    Thanks
    I like designing my own esp since I am taking a college course on this it is giving me more practice which is always good . As far as the transaction fields I just have a few real basic. Transaction date, input date (default for this set for current date for obvious reasons), business, transaction item(s) or service, and amount.

  6. #6
    03lances is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    4
    OMG man I am bright lol. So I had a lightbulb go off after I posted. I went back into my query and gave the criteria 2 parameters for the transaction date field I wrote it like so-- Between [Start Date?] and [End Date?], then in the same criteria row I gave the business field-- [What business are you looking for?] and VIOLA I can punch in the starting date then the ending date then what I am looking for and it works beautifully. Sorry just very excited as I was working on getting the separate tables to link together for hours last night and finally gave up extremely frustrated so this is great news for me.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That does work, however, I never use input parameter prompts in query. Too hard to validate input.

    Review this http://datapigtechnologies.com/flash...earchform.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-25-2012, 07:19 PM
  2. Replies: 7
    Last Post: 05-02-2012, 08:04 AM
  3. Simple Combo Box Multiple Select Question
    By ahamilton in forum Access
    Replies: 7
    Last Post: 03-17-2011, 01:38 PM
  4. Select query question
    By CoachBarker in forum Queries
    Replies: 2
    Last Post: 10-31-2010, 11:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums