ID Changed When UNION Queried
Aug 18, 2006
Hi all,
I have a UNION query of multiple tables:
Select * From tblCal_SlsAB
UNION
Select * From tblCal_SlsBC;
Both of them have Unique IDs as PK, such as: AB001, AB002, etc for tblCal_SlsAB and BC001, BC002, etc for tblCal_SlsBC. When I do the UNION query I get: 1, 2, etc for both IDs instead. What's causing the ID to change and is there a workaround it? I need to get the same exact IDs I have in those individual tables. Thanks in advance!
Max
View Replies
ADVERTISEMENT
Aug 18, 2006
Hi all,
I have a UNION query of multiple tables:
Select * From tblCal_SlsAB
UNION
Select * From tblCal_SlsBC;
Both of them have Unique IDs as PK, such as: AB001, AB002, etc for tblCal_SlsAB and BC001, BC002, etc for tblCal_SlsBC. When I do the UNION query I get: 1, 2, etc for both IDs instead. What's causing the ID to change and is there a workaround it? I need to get the same exact IDs I have in those individual tables. Thanks in advance!
Max
View 2 Replies
View Related
Oct 24, 2013
I'm having a problem with a UNION / UNION ALL query.It seems there is a application crash fault when running the query that MS are aware of and have issued a hot fix. Unfortunately it will take my IT dept some time to check and install the hot fix If they agree to do it at all.
Problem signature:
Problem Event Name: APPCRASH
Application Name: MSACCESS.EXE
Application Version: 12.0.6606.1000
[code]...
View 1 Replies
View Related
Nov 24, 2007
Hi,
Would you humour me for a moment while I explain my query problem.
I work in Air Freight at Heathrow where all consignments are processed under an Airwaybill (AWB), an eleven digit number where the first three digits (prefix) denotes which airline the freight has travelled with. For security reasons a lot of the freight is X-Rayed and I am designing a database to log these shipments. Sometimes freight will arrive at Heathrow on one airline and then travel on to its' destination (this is called a transhipment) on another having first been X-Rayed.
What I want to do is produce end-of-month reports for each airline detailing which of their freight has been X-Rayed. I have created queries which filter the data for each airline according to the first three digits of the AWB, simple. On the data entry form I have a combo box which allows the operator to select which airline the freight has transhipped on. My problem is in the query where I have used the Or operator to "catch" these records e.g in the United Airlines report where the prefix is 016 I have all the records that begin with this prefix plus those that went on this airline but also those that went on a different airline but have the 016 prefix. It is these last records that I only want to appear once on the report of the airline it finished its' journey with.:o
View 5 Replies
View Related
Jun 11, 2007
Hi all,
I'm building a quick database, but some of the data to go into the database is quite... odd...
Anyway, the point is, I have one sheet that says
"Job Number 1200-1245" which would have all the same details, dates, etc.
Is there a quick and easy way to put in a range, say J1200-J1245, enter the data once, then be able to query... for example J1212 would return the same data.
Cheers :)
View 1 Replies
View Related
Jan 13, 2015
Here is my code...if I take out the where statement..it copies the remarks all the way down my table to all the records...with the where statement..it's prompting in for parameters..
Private Sub Remarks1_Click()
Dim strMsg As String, strQry As String, strRemark As String
strMsg = "Update All Comments in the Current view. IMPORTANT!!! Will update a the Viewed records."
[Code].....
View 2 Replies
View Related
Jun 15, 2015
I have a continuous form with an SQL query as its record source, it is a dynaset. Each row on the form is for a specific picture, containing a unique ID, a pre-generated caption field, and an official caption field. The idea is that users will edit the pre-generated caption field, hit a button, then their changes to the pregenerate caption will be made to the value of the official caption field.
My VBA code for the button works fine, in that it doesn't return any errors. The problem is that there is some sort of delay between when the button is hit and when the official caption field is updated. After the first press, the form requeries and the official caption field is the same, but after a second press the official caption field will display the user's changes.
First, a recordset clone is created using an SQL query, the pre-generated caption column is selected and then the results are filtered to the single record that shares the picture ID of whatever row the user was working in. Next, an SQL update runs, replacing the value of the official caption field with the value of the pre-generated caption that is contained in the recordset clone. Then the form is requeried.
I've tried adding a change of focus at the beginning of the code and a 15 second pause between when the update statement runs and when the form is requeried, neither solves the issue. The problem can't entirely be my code, because a second press of the button will make the appropriate changes.
I know what you're thinking, why have divide the captions into pre-generated and official in the first place - why not use pre-generated caption as the caption source? There is a separate feature that allows a user to mass edit captions and I decided to retain the original pre-generated caption as a field, in the event that the user makes a mistake or decides to reference the content of the pre-generated caption.
View 1 Replies
View Related
Sep 18, 2014
I have a report based on a query that returns all info from the query which is fine, I now need to amend this so that individual users can specify the date range to be queried and the person for whom the results are required (one of the query strings)...
View 1 Replies
View Related
Jan 15, 2015
I have one memo per change (user input)...I need to have the comment the user inputs to copy into other records that were queried. I tried insert. My coding does one of the records or all of the records ..I need it to copy just to the queried records ...and I have a query but no filter set.
View 4 Replies
View Related
Nov 30, 2005
Let's see how well I can explain this. I'm sure the issue is much simpler than I know but my knowledge is limited as this is all self-taught.
I have two tables:
Table 1:
Category Name
Part Name
# used per house
Table 2:
Part Name
MFR Name
Part Number
Initial Cost
Rebate %
Final Cost
In my form table 2 is the subform as there are multiple manufacturers that we get price quotes from for every part that we use. I'm trying to query for the minimum price for each part but I cannot get it to give back the MFR name that has the minimum price. I currently can only get it to show the minimum price for each part but that doesn't tell me which MFR it is that provides that price.
Also, Final Cost is just the Initial Cost multiplied by the Rebate %. Is there anyway to make it be auto-populated within the table. Currently I'm just running a text box on my form that multiplies the two fields but that leaves my Final Cost field in the table blank obviously.
I have a few other things but figure I'll start here lol. Any help would be appreciated
View 2 Replies
View Related
Apr 7, 2005
I have a need to know if a user changes any data. The problem is there are 200+ forms where data can be changed.
Is there any way besides attaching the same function (user defined, Public) to each after update event for each form??
Thanks
Mike
View 1 Replies
View Related
Jul 28, 2006
I am in major need of help. I am need Access or Excel to notify a user when a change has been made to a table. I have a potiential database/spreadsheet, and this would help me. Can anybody help me out?
Chris
View 1 Replies
View Related
Mar 20, 2007
Hi,
Yesterday something wrong happened with my computer and got hanged!
I called a hardware person and he corrected the O/s. I am using XP (O/s) and office 2003. The hardware person told me that he has not changed anything in msoffice.
I have already created some applications in access 2003.
I have observed that the application swithboard appearace is changed and buttons on switch boards are looking now like access 2000. Then I checked for database window. The Table, Query, Form, Report etc are looking like access 2000 window.
I have captured present screen and my earlier screen how it was.
Can anyone help me to get original display of my swithboard ?
Thanks in advance..
Satish
View 2 Replies
View Related
Jan 2, 2006
If in a form i have a box that calculates something i cant change that..is there a way around it?
for example on a sales invoice consider the following fields..
sales total
sales tax
grand total
grand total=sales total + sales tax...
but what if i want to change it?? not the formula.. but just to overright it?
View 1 Replies
View Related
Aug 24, 2006
Hi,
I'm trying to populate a read-only field on a form with the time and date the record was last changed.
This is to acheive seeing a list of records that have been updated this week for example.
I have this at the moment:-
Private Sub Form_AfterUpdate()
Last_Updated.Value = Now()
End Sub
When I've updated a record it won't save and when I try to move onto the next record it does nothing, the button clicks but no response.
Does anybody have any idea where I'm going wrong or have any suggestions?
Would appreciate any help.
Thanks,
Dan
View 3 Replies
View Related
Apr 24, 2006
Hi,
I have an complex code. In this code I sent the form value 00712 with query string and an insert SQL command to an Access database. The probleme is that the number is stored as 712 in the database. I do not want that.
Does anybody know how I can avoid this?
Thx a lot.
Flip
View 2 Replies
View Related
Jun 12, 2014
I have a database that contains a table with all tasks made in our team and the time it takes to perform the task. I have another table with month, year and value in which I put month after month the volumes that each task handles. So, in a query I multiply the time to perform the task and the volume; month after month I have different results. However "the time to perform the task" may vary depending on processes improvements that we made. If before I had 5 minutes to perform the task and now I have 2, I change it and then the results on the query are changed for all months. I do not know if there is a way to specify that that change must be applied from the month where it is updated to the following months and not retroactively.
View 3 Replies
View Related
Apr 19, 2007
hi
i have two fields that are the same, but in different tables. can i create a relationship so that when one field (in stock) from the purchases table is selected, the same field in the stock table is the same value as the one from the purchases table... i hope i explained it well enough:)
i know this causes data redundancy, but is there any better way of doing this?
thanks
View 2 Replies
View Related
Mar 10, 2008
I hope that someone out here can help me with this. I have recently had a format change from how I received updated data for my database. I used to update my tables from TXT files. There were several update queries/macros written to import the data from 5 or 6 different TXT files into one table. Well; my source has changed :eek: The downside: I can only get my data from an XLS or CSV file now; but the upside is all the data is only in 1 file.
The macros/queries were written years ago and I've been having migranes trying to figure out how this was all done. So, i've sort of started from scratch. I can easily import the data into a table; I have even gotten our front end of the database working so it's just a click of the mouse again.
The problem I have is that the data that is in this table is linked everywhere throughout this spiderweb of a database! I was unable to import my data into the existing table because that table's field names are different than what I have in the XLS file. I get all sorts of errors when I do this. So I am currently importing into a new table. (I.E. MASS and MASS Imports)
Is there an easy way to update data from table 'MASS Imports' to 'MASS' ?? what I was thinking was to use an update query with both tables and link each line.... Or am I way off base? Is there an easier way to do this?
Thank You!!
View 12 Replies
View Related
Aug 6, 2005
G'Day all, I am new to the forum family so please excuse any ignorance I may have in asking questions the right way.
The following at times depending on certain values creates a negative value.
WDCCFrm: [qry Areas]![Formation Value]-([qry Areas]![Formation Value]*((100/[qry Areas]![Formation_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]![YEAR],Date()))
Due to the data requirements a negative value is not acceptable, it needs to be zero. The above is part of an Asset Managemnt Program and if the WDCC (Written Down Current Cost) is 0 then the item is written off. How do I change the negative value to a zero.
Any comments or suggestions would be appreciated.
Regards
Alrae1
View 3 Replies
View Related
Sep 27, 2005
Could someone look at my code and tell me why my “Me.TargetDate.Undo” won’t work?
First, let me say that the TargetDate field has an On-Click event that calls a module written by Allen Browne), which is shown below and works great.
=CalendarFor([TargetDate],"Select a date from the calendar ")
What happens is that the Me.TargetDate.Undo event always keeps the date selected in the calendar.
Based on something I read from Rual Guy on another issue, I’ve used the TargetDate_LostFocus routine to check for some additional data entry errors. I’ve tried moving the code in “Private Sub TargetDate_Exit(Cancel As Integer)” to the LostFocus, but it doesn’t make any difference to the Undo. I’ve also tried “I’ve tried “Private Sub TargetDate_Change(),Me.Undo…” but that doesn’t work either. :confused:
Private Sub TargetDate_Enter()
If Not IsNull(Me.TargetDate) Then
OldTarget = Me.TargetDate
End If
End Sub
Private Sub TargetDate_Exit(Cancel As Integer)
If Not IsNull(Me.TargetDate) Then
If DateDiff("d", Me.DateOpened, Me.TargetDate) < 0 Then
MsgBox "You have selected a date that is before…bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
ElseIf DateDiff("d", Date, Me.TargetDate) < 0 Then
MsgBox "You have selected a date that is prior to today's date…bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
ElseIf Weekday(Me.TargetDate) = 1 Or Weekday(Me.TargetDate) = 7 Then
MsgBox "You have selected a date that falls on a weekend..bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
End If
End If
End Sub
Private Sub TargetDate_LostFocus()
If Me.TargetDate > OldTarget And Me.cboAssignToID <> OldAssignee Then
MsgBox "The target date can only be changed if …bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
End If
End Sub
Ever thankful for this forum,
Christine
View 1 Replies
View Related
Jul 3, 2013
I have come across a weird event from the 1st July.
I have a form that inputs dates into the table.......nothing special.......
Form is totally unbound and the insertion to the table is done via DoCmd.RunSQL Insert every thing has been fine until the 1st of July at this point it started saving the date into the table in American format 07/02/2013 instead of UK 02/07/2013 There have been no changes to the database at all to give me a reason why this has happened.
The Table fields are set to Date Fields.
The SQL Insert was set to Date ( #" & DATE & "# )
The date displayed on the form is in the correct format to get around this I have set the insert of the date as a string ( '" & DATE & "' ) AND how can i reformat the dates that are incorrectly inserted to the correct format as they include the date and time 07/02/2012 11:11:47
There is about 1000 lines so manual change will be a nightmare.
View 8 Replies
View Related
Aug 7, 2015
I have a "Search" form that I am using to search through a query in a list box of all of my Customers. Once the record that I am wanting to view comes up I highlight it and click a "View" button to bring up another form with the customers full record. When this form is open I am able to update the customer information and add User's to it if necessary.
My problem comes after I click on my save and close button that takes me back to me search form. I want to close the search form and the error I get is "The record cannot be deleted or changed because table "tblUserProfile" includes related records." I click OK and get a SECOND error stating "You can't save this record at this time. MS Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?"I have already made my changes and saved them in the form with the customer record so am not sure why I am getting an error when trying to close the Search form.
I am using an embedded macro on the view button. OpenForm with a Where condition [tblCustomerRecord]![Profile ID]=[Forms]![frmSearchCustomer]![Profile ID]
I have the listbox bound to the Profile ID. I have not been able to get the view button on the form to work without this step.Is there a better way to view a specific record from my listbox results?
View 1 Replies
View Related
Jul 17, 2013
In Access 2010, I have a database with 2 tables "group" and "member". They have a many-to-one relationship with nultiple members per group. Group has identifier "ID" and Member has field "Group Code" that matches with "ID". Formerly we called this field "Bulgiin kod" but needed to translate it. When we changed "Bulgiin kod" to "Group Code", now when we expand the group records (click the + button next to the row) it gives "enter parameter value" box. I tried deleting the relationship and re-creating it but without success. If I change the Group Code back to its original value, everything works fine.
Here is another thing I noticed: if I enter the value of ID into the parameter value box, it just shows the entire Member table, regardless of the actual matches. So if I expand ID #15, and enter "15", the whole Member table displays under the record.
View 2 Replies
View Related
Jun 6, 2013
I have a SQL database with an Access front end that keeps giving me an error "The data has been changed... another user has edited the record.I'm the only user on it, and I'm trying to edit it directly from the table. I already checked my indexes and changed all the bits to ints.
View 6 Replies
View Related
Apr 25, 2012
Within my main table / form I have six status drop down boxes and would like to record with a date and time ever time they are changed and save this data in a second table.
If I cant do this with the six drop downs, can I do it if any change has happened to the recorded.
View 1 Replies
View Related
