I have two table one contain 2013 data and one will contain 2014 data (these are very

[pro7000]

I have queries, forms and code built to access reports from the 2013 table. Also users will now enter data into 2104 table. I now need to add 2014 table and have users be able to access either table. I am pretty new at this. How do I go about doing this?

[jimatqsi]

Must the data be in two different tables? Why not add a column to describe what year the data is for. Then each query or form could ask what year is being processed.

Jim

[pro7000]

Must the data be in two different tables? Why not add a column to describe what year the data is for. Then each query or form could ask what year is being processed.

Jim

Jim
Table are very large 158,955 records, that is just 2013 so we would double that. There is a date field which I could reference to. I have a form where the user can run reports and I would need them to be able to run both years, wat is the best way to do this providing you don't think the tables will be too large.

[mcupito]

If you are retrieving the same information, just make a copy of each query change the filter for the queries to the "2014" table.

For queries where your users will need to access both tables, simply include both tables and the required fields in the queries.

As for the code, how are we supposed to know what it is doing, and thus tell you how to fix it - if we have NO idea what it is doing?

The thing you're getting caught up on is the difference in dates. If you find it essential, create the same exact table structure/queries/code and format it for 2014, or both 2013 and 2014 when what you're doing requires data from both tables.

You have all of the pieces, it's only a matter of changing the criteria / table structure.

[pro7000]

OK I can do that, its just there are at least 15 queries for differnt report. They would only need to access one year or the other never at the same time. Could there be a button on form the form that would refernce what table (year) they wanted.

[zmbd]

Access 2010 specifications

Other than the 2GB file size limit, there is no limit on the number of records you can have in a table.

Queries are limited to pulling 1GB of data/reacord at a time

You should be normalizing your database or you will continue to have these issues.[*]> Database Normalization and Table Structures.

By having your database normalized then writing the query becomes almost automatic and will allow you to use parameters and user prompts:

Using parameters with queries and reports

Queries III: Create parameter queries that prompt users for input

[mcupito]

Yeap! You already got it. If the user selects the [2014] button, just use everything you already have and change it to reflect the new 2014 table.

I could see the 'Button' route being an issue in the long run -you want to avoid a large array of buttons [2013] [2014] [2015] etc..

Edit: The prior posts do make a good point, and you should make note that the option of keeping them all in the same Table is ideal, because the records contain all of the same information, just differ by year.

In that scenario, you can avoid creating a new table, and your data won't have any redundancy. (DBMS Normalization standards). You can actually make your procedure fairly similar.

Having the user enter in the year they want to run the report for on the form, use the name of that control as the parameter for the query.

Say you want the report for 2014.
Prompt the user to enter 2014 in a TextBox control.

In the criteria for your queries, update it to something like:

Format([Forms]![YourFormName]![TextBoxYear], "yyyy", 0, 0)

I think that would work.

[zmbd]

@mcupito: Certainly one way to handle it; however, IMHO, much too complicted to maintain.

@pro7000: Better to normalize, then use a query to just pull the available years from the table and use that as the rowsource for a combo/list-box that the user can enter or select a year from; thus, as you add more data, the years will show up in the dropdown list without any need to change the underlying code nor alter the form.

[pro7000]

Thank you both, I am going to work on this now. I know it is not ideal to have 2 tables, but we number weeks of the year using only full weeks so each year dates change and we use these dates (week number) to pull data. I will keep you informed, thanks again for your imput

[zmbd]

Once again, there is a function for that:
For example this is week 7 of the year.
Open Access, <ctrl><g> copy and paste the following in the immediates window: ?datepart("ww", now())
A little conversation can be found here:http://bytes.com/topic/access/answer...r-calculations
and ofcourse, in the [F1] help thru the VBA-Editor which will show you the options for first full week etc..

You really should follow the normalization rules
ʕ•ᴥ•ʔ

[NeoPa]

I would strongly urge that you consider normalisation in your design. One of the biggest reasons for doing so is that it will make your life much easier in the long run. It is rarely, if ever, a good idea to maintain multiple tables with the same design. That goes for other objects too, like queries and reports.

What may appear to you to provide an easier development path at this time will certainly not appear so when you get to the stage of better understanding after all the problems you encounter and are kicking yourself for ignoring the advice of those who know a thing or two about databases.

While I could refer to some of us here that way, I'm actually thinking of the very clever guy (Codd) who came up with the concepts of normalisation in the first place. He was no fool. He didn't suggest these laws for reasons of how cool they seemed.

[mcupito]

Though it is rumored he won a popularity contest in college... (haha)

Pro - NeoPa is referring to Boyce-Codd Normal Form. Follow the link zmbd provided, and become familiar with database design. You will be a much better developer over time and with practice.

Also, by now perhaps you have seen the light of what we are suggesting. You can easily format dates to your needs, and query by specific date formats as well. 2013 or 2014 - It will not matter in your case. It is a simple switch in a query of

Criteria: "2013"

Or

Criteria: "2014"

To help you any further, we would need to see what table structure you have. What are the columns? Is there a "year" column?

Your solution is very easy, regardless of how the data is set up. Let us know your next step.