Shuffle Two Fields Of A Db Differently! But Retain Ability To Relink Them
Feb 4, 2007
in a Dbthere are 2 fields which contain 'matching' data. (there are other columns too)
A Genesis
B Exodus
C leviticus
D Numbers
E Deutronomy
I want to shuffle both columns so they are both randomly mixed up
B Numbers
A Exodus
C Detronomy
E Leviticus
D Genesis
How do I do it?
I have explored the random function but that randomises the records but keeps each record intact.
I then want to print these two columns to a report which numbers each line - easy enough once I have the query BUT I want to print an 'answer page ' at the end of the report which will look like this:-
1 B Numbers (5)
2 A Exodus (1)
3 C Detronomy (4)
4 E Leviticus (3)
5 D Genesis (2)
I thought of appending a unique ID string to each pair and only displaying the first part of the string e.g
A@1 Genesis@1
etc and I can do this but maybe there is a simpler way.
Lastly supposing I would want to keep the order of column one and only shuffle column two?
Thanks for any advice!
Hi If I have a table of entrants to a darts contest say, is there a way to randomize / shuffle those names before pairing them off to play? I would expect to need a new table to retain the list in that order? Thanks for any help. Don.
My database was working fine until I had to make some minor changes in a few of the fields (which I did in the table and the data entry form). Now, all of the combo boxes in all of the records show the same selection. If I change the selection in one of the fields in a record, it changes the selection in that field in all of the records to that selection. :eek:
I have a text box in a form. When the value in the text box is changed, a message box appears asking if the user wants to change the value in the text box. If they select "Yes", all is good and it goes on well, but when they select "No" is it possible for the text box to retain its previous value?
For example.
The value in the text box is "1". The user changes this value to "2". A message box pops up asking if the user wants to change the value, the user select "No" and so the value in the text box goes back to "1", the number it started with before it changed.
I have this scenerio that I am trying to work through and find a proper solution:
Main table stores a part # and an index (Primary Key) along with a bunch of other data. I want to store with the part # the current active version of firmware (aka software) associated with that particular part# / index. Part # may be a duplicate, so for this reason the PK is the index, so that each index / part # will have which firmware has been used with it.
I would like the ability to retain a history of firmware versions used for a particular part # / index so that if we need to go back to a previous version of software we will know which ones have been used.
I have been able to create the relationship to show all previous versions of the firmware used for a particular part # / index, but cannot figure out how to only have one 'current' and update the others as 'archived'. I would like to either use a boolean or selectable lookup (ie Current vs. Archived). What I want is that when I select one firmware to be ‘current’ all others related to the particular part # / index are updated to ‘archived’ leaving the other records not related to that part # / index alone. Also note that I am trying to make the update of the firmware status in the table view without having to go through a form.
Main Table ID (PK) Part No Product
Firmware History FirmwareVerIndex (PK) PartNo Firmware ChangeDate ChangeName FirmwareStatus
I have a table that has multiple records (count >1). I used the find duplicate records and then made it a delete query, however, this resulted in deleting all the records that had count >1. I need to retain distinct record, and delete the extra records. Using select distinct.. I do not want to use VBA to achieve this, but at the same time be able to incorporate the steps in a module that would execute the queries in a sequential order and achieve the same results.
I am trying to export my Access pivot table to excel because every time I do, the values (which are text and numbers; i.e. some numbers and some <0.05 format) are changed into a sum, product..... I need to rearrange this data not calculate anything.
We recently put together a database for a call center. The file is located on a public drive, and all users seem to be able to access the file (at the same time) and input new information.
On one particular form, I've found an interesting problem: I've placed a button at the bottom of the form, which will go to a new record when clicked. The command to go to a new record is clearly in the OnClick event, however users in the call center have to click twice in order to execute the code. I don't have to.
We're using the same form, in the same file, opened with the same version of Access.
I want to print the results of a query where some values are whole numbers, some have 1 decimal place, some have 0.3333333 recurring etc. Problem is that in order to fit all the columns on a page the column width does not allow all the 0.33333... to display. So what I see is like this (there are lots of columns, I am just giving an example from one column)
891 833.5 ######
I don't want to fix decimal places to 1 because then I would see 891.0 instead of 891. How can I format the number field so that whole numbers are shown without decimal place, numbers with 1 decimal place stay like that i.e. 833.5 is fine as it is. But display something like 313.666666666667 as 313.67 rather than ##### getting put in as not enough room for all the decimal numbers.
I have a basic form that includes check boxes. All works well. But on my (Windows 8) machine, the check boxes display as big ugly black squares. If I open the db from any another machine (Windows 7), check boxes display as I'm used to with a nice outline and bluey tinge to the middle if not set! All machines have Access 2013 on them.
I'm trying to use code to relink the the table but that is not the only table that is linked in the DB. The other linked tables are in other databases so I want to select specific tables to relink as the others may not need it. I alway will want to select the path.
here is an example of the code I'm using I got it from one of the other users here.
I've searched the forums for days and I can't find exactly what I need to know, even though this is a very common topic, so if it's already been answered, I apologize.
My problem is simple. I have a database that tracks clients for me. I have a front end and back end in the same folder. Under normal circumstances, the static links that Access uses are fine. I just link from the front end to the back end and all is well.
But some other users of the database will need to track multiple people's clients. So instead of putting the database in c:database, I may need to have a copy in c:database, c:database2, c:database3, etc.
So what I need is a (hopefully) simple way to get Access to look for the backend in the current directory, no matter what that directory might be. It may be on a network drive, or the c: drive, or any of a dozen various subdirectories. And the users who will be using the database may be very computer illiterate, so I don't want to have any user intervention required.
And so that I'm not any more confused than I already am by any code you might offer up, the name of the database frontend is tracking.mdb and the backend is tracking_be.mdb
Any ideas? Thanks in advance. You'll make my week if you can figure this out with me.
I created my database a few months ago and split the database into a front and back end. I recently added a new table on the front end. How can I move this table to the back end and re-link it?
I have created an Access 2010 Database for tracking inventory. As parts move to each location, quantities automatically subtract from the previous location. I now realized that some parts are destroyed.
The problem is the if I simply enter a negative value to indicated that a part was destroyed at one location, it appears as a positive value in the previous location. Is there a way to make negative values not affect a specific query?
I am new to using access and have customized a project management database for a friend's company. I need a way to auto relink the front end and back end databases when moved in the same folder. I am currently using Access 2013 and have already tried using the code on this site: [URL]...... Does this code not work for Access 2013 or am I adding it incorrectly.
why access 2007 is displaying an image oddly on a 2003/2008 terminal server farm. I am reasonably sure the problem is an access on terminal server issue because if you view the image in paint, image viewer, or word it displays fine on the terminal server. I don't think it is a resolution or color issue because once again it displays fine in paint, etc regardless of color depth.
The image below shows the difference in the png graphic. The image is transparent (the blue section being the background)
[URL]
As you can see the terminal server image is pixelated.
The form the image is sitting on has a blue gradiant that goes light to dark left to right. The form properties are below
The scenario. Two PC's, one older than the other, both running Windows 2000 (SP4) and Office 2000 (SP3). I use Access as a front end to a MySQL database, connections are made using ODBC. I recently altered the structure of a table and attempted to re-link the table in access using the Linked Table Manager on the new PC. All that happened was the hourglass came on and never went away. I tried to do this on the old PC and it worked fine first time.
Any ideas why I can't get it to work on my new PC? It's quite important as I won't have the old PC with me much longer :-(
I support a database for some users who don't find the built in search function (ctl+f) to be useful enough.
The main data entry form of the database has fields for subdivision, lot number and address, any of which they may use to find the record they want. They're requesting that I add two types for searching: * select sub and/or lot number as search criteria and have the selected record populate the screen * type in a freeform address and have the record populate the screen
I'm debating about what the best approach would be to do this. Should I have a command button which opens a pop up form with the fields? If so, what is the code I will need to take the data selected/entered on the popup, run a query and then populate the underlying form? I think I need to pass parameters but I'm inexperienced at this so I need some guidance.
Also thinking that if I do create a popup form that I'll use it in the open event on the data entry form as well.
Just as the title describes, I can't seem to find a solution to be able to link the front-end to the back-end tables through the common file dialog when the backend is an accdb file that is password encrypted.
See the example attached. Here it should work just fine per the Article at :
http://support.microsoft.com/kb/181076
I've found it works fine without a password encrypted back-end but not with a password encrypted back-end.
Anyone have any ideas? It would be nice to use this feature in Access as well as automating the relinking.
At this point, FE db has 4 linked tables, 3 are located in FileBE1, and the 4th is located in FileBE2.
The production BE is located on a server, the test (when working remotely) is on a local computer that is not able to connect to the server. Also, the test BE files (but not the table names) have different names than the production.
I have the idea of creating a table in the Front End that contains a list of users, filename and location (server or local drive).
Upon startup, how could I set up the vba to check the links and if they need to be changed will go and change them without the user having to click on link table manager or be prompted for names and path.
For now, I envision three rows in the table, one for the test user, one for local user and a default UNC for everyone else. (or would it be six rows since there are two files that need link checking and relinking).
I have a database with a huge table on the back-end, and then I want to set up various front-ends that will only show particular records that a user is interested in.
I notice that, when I query the big table without any join, I get all the records and also a space for entering a new record--that's what I want. But, when I add a join that serves to filter the records to the ones the particular user would be interested in, the space for entering a new record goes away. This is the case whether I view the query itself, or the form that uses the query.
Does anyone know how I can keep the ability to add new records while also making the query a bit more complex?
From a Access database that I inherited. Users used to be able (from a form) run a query and then add data (i.e, new rows). But now that the the database is split and the backend is on MS-SQL, they no longer can do this. I do not think this will be possible. I think they will have to add data directly to the table or have another form for adding data.
Selecting the "General" group as this involves SQL Server Stored Procedures (SP) and VBA code and Reports and and and...
Client has requested exception type reporting noting when a price in a Bill of Materials (BOM) changes.
I am thinking to solve this with the following steps:
1) EXEC SP to run "this week's" BOM reports, automated, figure out how to print to PDF or something 2) EXEC SP to run "this week vs last week" exception report. A giant nasty:
Code: SELECT cols.... FROM [xyz] LEFT JOIN [histxyz] ON [xyz].[partnumber] = [xyzhist].[partnumber] WHERE [xyz].[cola] <> [histxyz].[cola] OR [xyz].[colb] <> [histxyz].[colb] OR etc...
through each of the fieleds that are hooked up to change tracking. Run that SP once, then use that temp table to generate customized reports based on parts per product which had a change.
3) Update weekly state snapshot of all parts remembering this week's state... transfer data from [xyz] to [xyzhist], so TRUNCATE then INSERT commands.
Seems slow and monotonous, the snapshotting "shell game" aspect... perhaps I may wrap that all into a transfer SP and allow the data to stay right on the server as it moves tables.
I created a query that shows the Student ID, First Name, and Last Name.I then created a split form from the query.Finally I added an unbound text box called search with a button next to that has a macros within it:
Code: [LastName] Like "*" & [Forms]![SearchID]![Text14] & "*" Or [FirstName] Like "*" & [Forms]![SearchID]![Text14] & "*"
1. I love that it searches for the name you type in and displays the record 2. What I don't like is that you can edit the record.
I tried to set the AllowEdit in the form properties to no but that also took away the ability to type in the search text box. Is there a way to allow typing in the text box but not allow changes in the record?
