473,320 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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?
Feb 11 '14 #1
11 1520
jimatqsi
1,271 Expert 1GB
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
Feb 11 '14 #2
@jimatqsi
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.
Feb 11 '14 #3
mcupito
294 256MB
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.
Feb 11 '14 #4
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.
Feb 11 '14 #5
zmbd
5,501 Expert Mod 4TB
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
Feb 11 '14 #6
mcupito
294 256MB
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:
Expand|Select|Wrap|Line Numbers
  1. Format([Forms]![YourFormName]![TextBoxYear], "yyyy", 0, 0)
I think that would work.
Feb 11 '14 #7
zmbd
5,501 Expert Mod 4TB
@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.
Feb 11 '14 #8
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
Feb 11 '14 #9
zmbd
5,501 Expert Mod 4TB
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
ʕ•ᴥ•ʔ
Feb 11 '14 #10
NeoPa
32,556 Expert Mod 16PB
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.
Feb 13 '14 #11
mcupito
294 256MB
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
Expand|Select|Wrap|Line Numbers
  1. Criteria: "2013"
Or
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 13 '14 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Nomen Nescio | last post by:
can anyone be so kind as to look at http://www.mysolution.ws/HYPOCRITE.php and let me know why it isn't passing the form data to http://www.mysolution.ws/insertHYPOCRITES.php for the most...
11
by: Hi5 | last post by:
Hi, I am new to access I usedto work in Oracle and Mysql. I am after a way that enables me to populate a database I designed in access with lots of data which can be sorted in excel sheets, ...
4
by: authorking | last post by:
I use the following code to insert a data record in to a datatable of an access database.But every time I execute the command, there will rise an exception and the insert operation can't be...
2
by: Anonieko | last post by:
Hello ASPNET guru's, What is a clean way to go around the problem of displaying a GridView templated column where data can contain Single Quote ( ' )? I maybe too naive, but this is of course...
1
by: laxlou | last post by:
Hi, I'm using script to grab following comma separated data from telnet port. (x86, Windows, ActivePerl) There is coming quite a lot of data from port. several lines per second producing hudreds...
0
by: Fasih Akbar | last post by:
Dear Concerned, I want to create a property in my user control which i am creating in Web Control Library. I am using .Net 2003. My question is that how I can create a property that will...
4
by: Boki Digtal | last post by:
Hi All, C# exe file will contain programmer info right ? So, Microsoft or hacker can easily know who design that program ? correct ? Best regards,
1
by: lipali prusty | last post by:
the procedure should take the table name as input from argument.and data will be inserted to the table mentioned in the argument list
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.