Queries :: Query Is Duplicating Data
Jan 27, 2014
I created a query using 2 tables, [tbl_transaction_details_b] and [Siemens_Contact_Info1]. Currently it is pulling 38 records (which is right and the same number of records in tbl_transaction_details_b). But when I add Line Number to the query (which is in [Siemens_Contact_Info1] I go from 38 records to 1019. Here is the SQL view before adding line number:
SELECT Siemens_Contact_Info1.[Mail Code], tbl_transaction_details_b.INVNUMBER, Format([INVDATE],"mm/dd/yyyy") AS InvoiceDate, "USD" AS InvoiceCurrency, Null AS [Canadian Tax Registration ID], tbl_transaction_details_b.PONUM, Null AS [Account No], Null AS [Payment Terms], tbl_transaction_details_b.SHIPTOCUSTOMERNAME, tbl_transaction_details_b.SHIPTOADDR1, tbl_transaction_details_b.SHIPTOCITY,
[code]....
What do I need to do in order to pull the line number for each record? Oh the line number is determined by the EQUIP_SEG which is also on [Siemens_Contact_Info1]
View Replies
ADVERTISEMENT
Sep 21, 2005
Hello,
I know it's against any database logic but I need a code behind a a button to copy data from one field to another.
What I am trying to do is:
I have a table with two fields:
Father Last Name (txtFLN)
Child Last name (txtSLN)
When writing my data to the table (I use a form named MASTER), I type the father's last name and would like a button that copies that same entry in the other field?
Any help?
View 1 Replies
View Related
Apr 20, 2007
Okay, I've learned TONS from this forum alone as a self-taught database user. But now I'm in a rut... I created a query that contains the following fields:
Termination Date, Name, Rehireable?(yes/no), and age (if statement). This is the if statement someone helped me to write:
Ages: IIf([Age] Between 16 And 20,"16-20",IIf([Age] Between 21 And 25,"21-25",IIf([Age] Between 26 And 30,"26-30",IIf([Age] Between 31 And 35,"31-35",IIf([Age] Between 36 And 40,"36-40",IIf([Age] Between 41 And 45,"41-45",IIf([Age] Between 46 And 50,"46-50","Over 50")))))))
At first glance things looked fine but I checked again and the data is all duplicated - each employee is showing one record for rehireable (yes) and another record for non-rehireable (no).
I'm not even going to think about getting into this more until Monday, so if you have any insight on why my data is doubled I would REALLY appreciate it. Thanks!
View 3 Replies
View Related
Dec 12, 2012
I have two tables that I'm struggling with how to relate to each other. One is tblHardware which contains all the items that we sell. There are no duplicates in this table. The only columns are a part #, description, long description, and cost.
Then I have another table with is tblPackages. These are the packages we sell which pull from tblHardware. This one just has an autonumber, description, and image (image I'll add later and not worried about now).
What I'm struggling with is how to tie these two together because one Package will contain multiple items from Hardware however there are multiple Packages that have the same Hardware items (17" monitor for example).
View 3 Replies
View Related
Oct 14, 2011
We are a travel company and I am just setting up a new database with two tables - [Client_Table] & [Enquiry_Table].
Most exisiting clients call in when they want to make a new enquiry so the 'user' can go it to a form which creates a 'new enquiry' for that client.
We also download 'new enquiries' from our website. This data includes info that goes in to the [Client_Table] and [Enquiry_Table]. They download in to a XL spreadsheet. Currently I copy and paste the data (not sure if there's a better way) from the XL speadsheet in to a query. This query creates a new client record & a new enquiry record which are both linked by a primary key [Client_ID].
This all works however the problem I have is that sometimes existing clients enquire through the website. They do fill in a field to say they're an existing client but if I paste them in to the query as explained above it creates a duplicate client record.
The only way around this I can think of is it to take out any exisitng clients from the XL spreadsheet first, search for their Client_ID and then paste these enquiries seperately with their Client_Id's in to different query which only adds a new record to the [Enquiry_Table] and links them to their exisitng record in the [Client_Table].
View 3 Replies
View Related
Dec 30, 2014
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
View 5 Replies
View Related
Nov 20, 2013
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
View 5 Replies
View Related
Aug 12, 2014
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
View 9 Replies
View Related
Nov 25, 2005
Hi
Please can some help me asap. I need to do a querie which joins two tables together. I have a telemarketing table and an appointment table. The idea is that when an appointment is made telemarketing should be done afterwards to the same company. Therefore I want to view all of the telemarketing done and all of the appointments done and then match companies which have had both an appointment and telemarketing. However when i do this if there has been more appointments made for one company that there has been telemarketing the telemarketing duplicates itself so that the two have the same amount done which is not what I want??? Does this make sense?? Can some one please help????
Thanks
View 5 Replies
View Related
Feb 10, 2008
Hi
I'm hoping this is quite easy as I'm new to Access.
I have a record similar to the below
recordID: 1
animals: cats
breed: persian
number: 3
price: £50 each
What I would like to do is dupicate the above data three times, changing the field in "number" to show as 1 on each occassion...so, theoretically, the data will be exactly the same in each new record except for recordID....
e.g
recordID: 2
animals: cats
breed: persian
number: 1
price: £50 each
recordID: 3
animals: cats
breed: persian
number: 1
price: £50 each
recordID: 4
animals: cats
breed: persian
number: 1
price: £50 each
Can anyone tell me how to do this?
Many thanks, Tim
View 1 Replies
View Related
Jul 4, 2007
Hi There,
I am trying to copy a table and put the copy in a folder called "My Data Sources". The reason being when I set up a mail merge letter it defaults to "My Data Sorces" for the recipient data. I also need the second table to be updated every time we enter data in the original table.Has anyone got any ideas please????
Best Regards
Keith
View 1 Replies
View Related
Nov 25, 2005
Hi
Please can some help me asap. I need to do a querie which joins two tables together. I have a telemarketing table and an appointment table. The idea is that when an appointment is made telemarketing should be done afterwards to the same company. Therefore I want to view all of the telemarketing done and all of the appointments done and then match companies which have had both an appointment and telemarketing. However when i do this if there has been more appointments made for one company that there has been telemarketing the telemarketing duplicates itself so that the two have the same amount done which is not what I want??? Does this make sense?? Can some one please help????
Thanks
View 10 Replies
View Related
Nov 5, 2007
I have a query where I want to type in the part number and the begin and end dates and have that information appear.
In part number I have
[form]![frmTest]![Test_Part]
and in Date I have
Between [forms]![frmTest]![Test_Date].[startdate] And [forms]![frmTest]![Test_Date].[enddate]
It is duplicating my records and not pulling all of them, any help here what be great.
View 14 Replies
View Related
Jan 10, 2008
Hi Guys
Not sure what I should do here. I would like to have a cell populate with the value of a specific cell previously.
explain:
in excel, we can use formulas like this:
A1: 00:00 B1: 08:30
A2: 08:30 B2: 13:00
A3: 13:00 B3: 15:00
etc, etc
Notice that each cell in A is the same as the previous B Cell. So the formula for Cell A2 would be something like: =IF(B1>0,B1,"00:00")
and Cell B3 would be =IF(B2>0,B2,"00:00")
The fields I have in Access are:
TimeIn TimeOut
So I would need to get TimeIn to be the same as the previous timeout???
Not sure If I'm making any sense right now :)
View 5 Replies
View Related
Feb 22, 2005
Hi,
I want to be able to make an exact copy of an existing record in a table and then change the value of a couple of the fields before writing it to the table as a new record.
What is the best way to go about this? I guess it is possible with select, update and append queries but I'd rather do it in code.
It sounds like it should be a simple and commonly performed exercise but I can't find anything on it.
Thanks in advance!
John
View 4 Replies
View Related
Jan 13, 2007
I have a table named tblProduct,
I have a table names tblProductSpec,
tblProduct contains basic info, tblProductSpec contains infomation linked with outer tables. What I need to is have the product ID's duplicated in tblProductSpec. Is it possible to do this via relationships etc?
Any help greatly appreciated. :)
-Adzi
View 3 Replies
View Related
Dec 4, 2007
I have a table that contains financial data on projects. Each project has one record consisting of numerous fields.
I want users to be able to archive off copies of these records at certain points in time so I can track the financial data - a bit like storing different versions or issues.
On any one day I want users to be able to access the live record and review the archive.
Question : Is there a simple way of duplicating a record in a table ?
At the moment I am using a form by copying all of the field values from this form, moving to a new record, writing the field values back to the form hence creating a copy. This seems somewhat laborious.
Can't I access the table directly somehow and simply cut and paste ?
Also, is there a simple way to check for a duplicate record before cloning the record i.e. if the data has already been archived once don't do it again.
View 4 Replies
View Related
Jan 25, 2005
Ok I haven't worked on access for a while and so my knowledge of my own database is a little poor. But I have a button on one of my forms which duplicates records. The problem is, not only does it duplicate the record but it also creates a blank record. Is there something i can put in the command for the button that will delete records that are completely null?
Many fields of other records are also null so I only want to delete records that have null in all their fields, or better yet just stop creating these blank records.
Hopefully that makes sense to someone,
Wee
View 1 Replies
View Related
Mar 8, 2006
I have a form with a job number textbox. I want to be able to edit the current record on screen by changing the job number and then save (duplicate) the record i.e. create a new record with the new job number without saving the old record I was editing. How do I do this? I tried the duplicate record command button but it saves the old record with the changed job number as well as duplicating it. :confused:
View 3 Replies
View Related
Jul 6, 2006
Hi
I need to be able to copy 5/6 fields from one record to the next then carrying on entering different data, ie copy date, location, time then enter name address etc.
I can copy the whole record but not just a couple of fields
Any ideas?
I am using access 2000.
Thanks
View 13 Replies
View Related
Aug 18, 2004
I have a report for parking tickets. In the report is a subreport listing each violation on the parking ticket referenced on the report.
Everything works fine except for tickets with multiple violations. For example, some tickets with two violations come out correctly on the subreport (both violations listed). Others show one of the violations listed twice and the other violation not listed at all. The queries behind the report and subreport show the correct information without the duplications.
I've tried sorting and grouping to no avail.
Any assistance is greatly appreciated. Thanks.
View 2 Replies
View Related
Feb 10, 2007
Here's another one I'm stumped on. What would be the best way to do this... if it can be done:
The tables in question are the following:
1. Exhibitors (stores exhibitor data, i.e. name, age, address, etc)
2. Livestock (stores livestock data for each exhibitor, i.e. breed, weight, gender)
3. Shows (stores data about each show, i.e. title, date, location)
I would like to have the Shows table to also store which exhibitors and livestock participated at this show. For example:
County Show: Jane Doe (exhibitor) - blackie, whitie (livestock)
John Smith - spot, blue, cutie-pie
James Black - smokey, sassy, fluffy
State Show: Jane Doe - blackie
James Black - spot, blue, cutie-pie, snuffles
Christy Meek - baby, apples
The problem is that each show will be associated with multiple exhibitors (and their different livestock). AND each exhibitor (and their livestock) will be associated with different and often times multiple shows.
So what would be the best way to have the Shows table pull the exhibitor and livestock data without having to duplicate exhibitor records for each show?
View 7 Replies
View Related
Feb 9, 2007
First off a big thank you to this forum... have solved a lot of questions I have had and some I never knew I had. Keep in mind this is all from a non-programmer so my question(s) might not be technically sound.
The issue I have is I have built a database to track documents that we process. It is uses an autonumber and that has worked great. The issue is eventually this document gets processed in our accounting system and I want to retrieve data from there and add it to the document record.
I figured out how to like the ODBC database and that worked, but I get an expression error. The field in the ODBC database is text and in Access it is an Autonumber.
Is there a way to copy the autonumber field and change it to text property to align with the other database?
All help welcome and appreciated.
View 4 Replies
View Related
Dec 1, 2014
I created a button in a form that duplicates the record so our employees can fill in the form more quickly.
I know I am being greedy, but is there someway to change the VBC so I only duplicate certain fields in a record, not all of them?
I have attached a (.jpg) of the Duplicate record code that is automatically created by Access when you use the "Duplicate record" option when making up a button on a form. I have attached a 2nd (.jpg) which shows the (7) fields I want to duplicate, out of the (20) available fields for each record.
View 9 Replies
View Related
Mar 28, 2015
Is it possible to duplicate a record with selected fields, to be copied into a new record. ie if there are 10 fields, i only need 8 of them (no need for timestamp field etc form existing record).
View 1 Replies
View Related
Sep 14, 2007
Cannot seem to find an answer to this, but please point me in the correct way if you know of one!
Quite simple i think, but blank mind at moment!
How would i use the values in a table/query as the criteria for another query? I believed i could type in [qryOne]![classification] in the criteria box, but this does not seem to work.
Thanks in advance,
Emily
View 5 Replies
View Related
