?
Solved

Appending multi value fields into a new table append query not working

Posted on 2011-10-27
10
Medium Priority
?
513 Views
Last Modified: 2012-05-12
Hi Wise Ones
I want to archive some records and have created a series of append and delete queries for each table and then created a macro for these.
However....when I go to test these, it turns out that I can't append multi value fields into my archive table. I've looked at one suggested solution which suggests just adding an archive field but  the user wants the numbering to recommence every year so I'm feeling a bit stumped. They do need to keep the records of the permits for up to 7 years..
Any suggestions...
0
Comment
Question by:agwalsh
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 668 total points
ID: 37037342
"... but  the user wants the numbering to recommence every year ..."    
I don't know what this means and therefore don't know what it has to do with this issue.

To do the append to an archive table , you wouls append all records excluding the multi-value field, and then you would use code to run an append query for every individual value contained in the multi-value field.


MVFs are there to provide compatibility with Sharepoint but for anything other than the most basic Access app (and which will stay a basic app), use of MVFs in Access applications is a bad idea because of the extra work needed to handle them.
0
 

Author Comment

by:agwalsh
ID: 37037410
@Peter57r - hm, so really I'll have to find another way around that MVF thing as I have 4 of these fields and they each could have different values ranging from 1-50...and it works so well at the front end.
What I meant by the numbering to recommence every year is: the database is for issuing of permits for temporary workers. Therefore the permit numbering system is required to commence at 1 at the beginning of every year...so to deal with that I am setting up a form for the users to archive the records at the end of the year and when those tables are cleared down..the permit number field is set up the a nz/dmax combination to recommence...appreciate the help A
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37037486
For any specific situation you can code a soluton, but there is no general answer. But if you are doing this repeatedly then it sounds like you should be building a code-based procedure.

I don't see a problem in starting numbering from 1 each year; but you cannot use an autonumber for the field and of course you would have to have a field for the year as well.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:agwalsh
ID: 37038362
@peter57r - I get I can't use an autonumber for that...so how would you suggest I archive the records for the current year's permits in preparation for next year...the user loves the MVF...?
thx. A
0
 
LVL 77

Accepted Solution

by:
peter57r earned 668 total points
ID: 37038442
Without a lot more info about requirements it's not possible to give a proper answer.

The extremes would seem to be...
Copy the file at the year end as the archive, and then clear out all the 'transaction' data from the live file to start the new year.  Just keep as many 'old' files as you need.

Write a vba procedure that re-creates the structure and data (using queries run from code  where suitable) to add the current data to a single archive file which can be viewed in its own or the current application.

0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 332 total points
ID: 37038760
"but  the user wants the numbering to recommence every year ..."  
"the user loves the MVF...?>

This is a constant battle...
Because the "User" does not know the inner workings of the app, the things they "Like" or "Want" may be a nightmare in terms or work for the developer.

If you are using an Autonumber field, you really cannot "reset" it. (You can, but that is another issue)
Else if someone asked about OrderID 28, you would have to know the year (because each year could have an Order 28)

The best way to handle this would be to add a second autonumber filed.
So you would have the Autonumber field that would keep incrementing forever (OrderID), and another autonumber field (OrderRefID) that you can simply delete and recreate each year.

As far a MVF go, they are not much more than a "Simple" way for a non-developer to create and manage "Many-To-Many" relationships.
A lot is hidded and inaccessible to the user, making it difficult for a developer to know what is going on, and to manage this data.

My "Guess" is that if you simply "grab" (Archive) the entire table containing the MFV, you will gat all of the data.
Again, this is my "guess".
...as Pete stated, most Access developers avoid using MVF.

The user "Loves" MVFs because they are convenient.
You can build a similar system with a standard Many-To-Many relationship and the user would not know the difference.
However this will require a fair amount of redesign on the part of the developer.

As a final note, remember that there is currently no equivalent to MVF's in SQL, so when it comes time to upgrade, you will have to redesign anyway...

JeffCoachman
0
 
LVL 75
ID: 37039223
"The best way to handle this would be to add a second autonumber filed."
But you can only have one autonumber field in any one table ...

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37040486
<But you can only have one autonumber field in any one table ...>
Yes, this I knew.

What I should have said was that a second field could be created and be made to "Auto-increment"

Dim lngNextNum as long
lngNextNum = CurrentNum+1

...or something similar...

JeffCoachman
0
 
LVL 75
ID: 37040510
ahh so ....
0
 

Author Closing Comment

by:agwalsh
ID: 37058399
I had no idea that the MVF feature in Access was such a nightmare before I began (blissfully ignorant :-)) on this project. Thanks for everyone's help and I'm going to go (with the rather crude solution) of clearing it down every year until I can find a more elegant alternative.
Thanks again.A
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question