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?
11 1520
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
@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.
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.
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.
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 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.
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 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
ʕ•ᴥ•ʔ
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.
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
Or
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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,
...
|
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...
|
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...
|
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...
|
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...
|
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,
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
| |