Combining Data From Different Names For Same Customer
Jun 29, 2005
I have a table with all repair history in it. Unfortunately, the entry system is such that people can initiate a new record without typing in the correct customer name. So if my customer was "Fry's" it could be entered into the system as "FRYS" "Frys electronics"... etc. You wouldn't believe the ways people enter the same name. Anway, the only thing that links any of these records together is the item serial number. Though one serial number could be under a different name than the other.
Does anyone have any ideas as to how to create a temp table which associates related serial numbers to a single customer name? Even if the user had to select the customer names he want's associated that would be fine.
Note: I should mention that no there is no way to get this changed on the back end. I'm stuck with the tables I have.
I have a DB set up with a debtors table (Customers) and a Customer Contacts table, I was thinking of combining these two tables into one.Most of the Debtors are companies, but a few are actual people. With the current setup, i need to have both a debtor and a customer in two different tables, but with exactly the same data.
I am working with an orders database and want to build a mailing label query that will not print duplicate labels.In the query I have the customer address book and the orders table. I know how to create the query to filter the year I want but how do I add the customer information as to not duplicate the customers and waste labels?
Example:
Order Date Year: ([Order Date]) Last Name Orders Addressbook
I want to be able to bring up all sales for 2014 but have no duplicate last names.
create a check on a feild(customer id) of customer table in MS Access , as "Customer ID is of 8 characters, the first 4 are alphabets and the last 4 are digits"
I have a Table Sales - that lists customer and order information
Most of the customers are new - is there a way to populate Customer Name Table with a new entry each time a new customer is input into the Sales Tale
(Without having to add the custome to Customer Name Table - then going to Sales Order) OR Failing that - is there a way to use a list box that points to Customer Name Table (and if it doesn't find the one you want, will allow you toadd a new record) In the SAME form as you use to add to Sales Table.
So: Date (textbox linking to form.Sales) Customer Name (List Box linking to form.CustomerName + adding the info to form.Sale)
I currently have an access database for tracking company assets and performing maintenance tasks on them.We also have a number of Stores Stock Items that are controlled under a separate finance package.We currently have forms that create individually numbered "Job Cards" in the Job Cards Table..we input the data and manually type into a Text Box the we have used 'X' number of Store Stock 'Item Y'
I have then use a query to export the data i need (Job number, dates, location and Store Stock Items).But I have to manually re-construct the data and items into separate lines in an excel sheet for a paste operation into the finance package (lots of typing of 16 digit stock codes)
What i want is to use a second table (linked via the "Job Card#" key).It will be called "Stock Items" which has a separate field for each of our stock items.the user then just opens second form while creating the job that immediately creates a new record.The user then simply has to put the number of each item used in the relevant text box on the form and close.
example of datasheet view Job# __ ItemX __ ItemY 2417_____3_______7
Now, I have all of that working as I want, But i nee to be able to combine the data from each field into a single field (either in access or excel).What I want to see is a field containing 3x ItemX, 7x ItemY, etc
if there are none of one or more items, I don't want anything displayed for that item (blank space is ok)I have 30 or more stock items, so there will be 30 or more columns in the table. the actual data stored in the table is simply the quantity of each item used.
this data is used twofold,on export to Excel I can split the data into new cells and it's used on a despatch note that is returned with the item so the end user can see the items that were used in the repair.So it needs to be done upon saving the record, record by record.
I just had to pull a large amount of data from an off-site SQL server. Due to speed issues, I broke up the data into 3 pulls and exported the data as text.
I have now imported the data into Access and wish to combine the three tables into one. All of the fields are the same, the only difference is that I broke up the pulls by date ranges.
Would this be done via an Update or Append query? AND, how would that query look? Sorry, still a bit new to this. :)
"Dynamically search multiple fields" using my own customer data.
I'd like to add in additional searches as what he has will let you do one search but I'd like to, in his example, search on vintage and state, or the like. I assume Id need two multi-searches for this.
Right now the data is in excel so I have a completely blank page to fill.
I have a very large Access application and use a hidden "main switchboard" to store data such as the current customer ID. Prior to the advent of tempvars, I embedded a key control into many query's underlying combo boxes, list boxes and forms criteria:
"Forms![main switchboard]![currentcustomerid].
The problem arises when the user quits the application on with forms using this in their underlying query(ies). As far as I can tell, the application closes forms in the order in which they were opened, so the "main switchboard" closes first. Then, for some reason, the current form has a requery and the user is presented with a prompt to enter the parameter "forms![main switchboard]![currentcustomerid]. I tested and found I could replace the criteria reference to a tempvar to "fix" the problem. I wonder if there would be a way to open every query in design view and do a search and replace on the criteria statement.
As the title suggests I would like to combine the data from two tables.
The first table records customer orders in fields - Part No / Quantity / Reqd Date Of Delivery
The second table is an output from a scanning s/w package we recently installed.
This provides the following data - Pack Qty / Part Number / Date Scanned.
The first thing I did was create a simply summing query to add up all the pack qtys for a specific part on a specific day.
Now comes the hard part.
What I want to do is sort and combine both tables in chronological order.
Where the part number & date for an order and a delivery match, I require the record to be combined to one record (in this query).
Where it does not match, then it should remain as two seperate records.
So for example if you ordered 100 footballs for delivery on 11/11, and we delivered 110 footballs on 11/11, the query would return:
Footballs 11/11 100 110
If the delivery was early / late we would get:
Footballs 11/11 100 0 Footballs 12/11 0 110
Now this last bit is where my queries (simple cross table queries) falls down as I only have three options when creating the join properties, ie both match, all from left table or all from right table, whereas I want all from both tables even if they do not match.
Maybe a query is the wrong way to go, but as the table from the scanner s/w is updated on a daily basis it would seem to me to be the best way forward.
I need to combine data from a Yes/No field such that for any instance of a Yes/True the query will show Yes (checked). Only if there are no instances of Yes should the query return No (unchecked) for the field. In addition, this rule must apply for each individual account number in a [separate] table of accounts.
The following query works fine except that it returns two entries for John Doe if his account has entries for both FlagSpecialOrder=True and FlagSpecialOrder=False. You can see in the attached image that accounts 8 and 9 both have two entries each. Because these customers have a special order I want just one one row for each showing Yes (checked).
I want the query to return a single row for each unredeemed account. If there are no special orders then that field will be No (unchecked), but if there is at least one instance of a special order the field show be Yes (checked).
(To put this into some kind of context, customers can place orders which may be stock items or special orders, in which case I need to record the supplier, quotes, delivery dates, etc. In some cases they save for an item and there may or may not be a special order involved. Once the order is fulfilled it is redeemed.)
SELECT DISTINCT Customers.Name, SavingsClub.AccountNumber, SavingsClubReservations.FlagSpecialOrder, Suppliers.ShortName, SavingsClub.FlagAccountClosed, SavingsClub.FlagDelete FROM Suppliers INNER JOIN (OrderDetails INNER JOIN (((Customers INNER JOIN SavingsClub ON Customers.Index = SavingsClub.CustomerID) INNER JOIN SavingsClubAccounts ON SavingsClub.AccountNumber = SavingsClubAccounts.Account) INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation) ON OrderDetails.Index = SavingsClubReservations.OrderID) ON Suppliers.Index = OrderDetails.Supplier WHERE (SavingsClubAccounts.Redeemed=False) ORDER BY Customers.Name
I've spent a week trying to solve this and thought I was on the right tack with the following query, which does return a single instance of each relevant account, but I can't manage to combine it into the query above to obtain the complete query.
SELECT DISTINCT SavingsClubAccounts.Account FROM SavingsClubAccounts INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation WHERE (SavingsClubReservations.FlagSpecialOrder=False) And (SavingsClubAccounts.Redeemed=False) And SavingsClubAccounts.Account Not IN (SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False)) UNION (SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False));
Appreciative of any help, otherwise I think I'll end up manipilating the data in code and use a temp table, etc...
I've been given the task of combining data from three locations into one database in Access, something that is relatively new to me. I'm trying to decide whether to have the locations enter data into an Access database or into an Excel spreadsheet at each location. Regardless of the program used to enter data, I would probably like to link each of the files to my main Access file and combine the three into one database there. I do not want the locations to add data directly to the main database if possible.
My question is, would this be done by an append query? If so, how? Also, my thought is that the data at each location would be added to every day. I would need to be able to add the NEW data only to the database without duplicating the previous day's data.
I have created a DB who's sole purpose is to track employee availability and job positions available (a poor man's version of a scheduling database, if you will). I have 2 listboxes on one form. Listbox A shows all the employees available for work, and Listbox B shows the available work positions that require an employee. I am trying to create a functionality behind a button that will allow me to highlight one row in Listbox A and one row in Listbox B to combine the information in a seperate table (still not created) and refresh each Listbox to remove the entries. Similarly, I would also have to create functionality behind another button to undo the above, if needed.
I'm trying to set up a way to import and combine excel files that contain multiple data sets. So for example, each excel file has a summary heading which consists of the first 3 rows.
Each data set thereafter consists of approximately 50-60 rows of data that I would like. There are approximately 1400 groups of data. Each group has a label which includes the state and the store number. I would like to automate a way to copy the state and store number information down each data set as well.
I have approximately 200 excel files that I want to load into access and have it format it so it basically will end up 1 big file with State, Store Number, relevant information from the data set. I've seen a module do this before, can't remember how to do it.
Is it possible to append two queries? The queries have the same layout and labels...it's just a matter of combining the data into one datasheet view.
The two queries are below:
1) SELECT prc.Market AS Geography, trp.Product, trp.Indication, trp.[Variable Name], trp.Period, Round(prc.Index*trp.[Variable Value],2) AS [Variable Value], trp.Outlet, trp.Daypart, trp.[Program Name], trp.Len, trp.Creative, trp.Campaign, trp.[Campaign Name], trp.[Media Type], trp.Vendor, trp.Channel FROM tblTRP AS trp, tblTRPpercent AS prc WHERE (((trp.Indication)=prc.Indication) And ((trp.[Media Type])=prc.Type) And ((trp.Geography)="National") And ((trp.Month)=prc.Month));
2) SELECT prct.DMA AS Geography, tv.Product, tv.Indication, tv.[Variable Name], tv.Period, Round(prct.percentUniverse*tv.[Variable Value],6) AS [Variable Value], tv.Outlet, tv.Daypart, tv.[Program Name], tv.Len, tv.Creative, tv.Campaign, tv.[Campaign Name], tv.[Media Type], tv.Vendor, tv.Channel FROM tblTVOtherMetrics AS tv, tblDMApercent AS prct WHERE (((tv.Geography)="National"));
I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:
I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.
The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.
I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.
I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.
Here is my problem. I have created a dbase that takes a download of financial data on a four weekly basis to produce an report for my users. The data always comes across with the same numer of columns but at each four weekly period the date moves on. To show this data in a meaningful way I need to change the field heading automatically, based on the date in the data to show the current Period and Year. The dates given in the download cannot be used as the field names as they are not in the correct format.
In Tbl_Output's 3rd column, only the Columns names: Name, Age and Location are repeated for each person and not column names ID1,ID2 (only its data xxx,yyy etc. is required in columns 1 and 2 as shown).
I was helped by rpeare with a VBA module that gives a single column output in Tbl_Output as
Mike 25 Essex Jack 32 Surrey Bob 36 Newcastle
The code is:
Sub main()
Dim db As Database Dim rstElements As Recordset Dim sName As String Dim sNumber As String Dim sArea As String Dim freefile Dim Filenumber As Integer Dim sSQL As String
Set db = CurrentDb Set rstElements = db.OpenRecordset("tbl_elements") rstElements.MoveFirst
sSQL = "DELETE * FROM Tbl_Output" db.Execute sSQL
Do While rstElements.EOF <> True sName = rstElements.Fields(1) sNumber = rstElements.Fields(2) sArea = rstElements.Fields(3)
Each month I get the previous months data. In that data, I have a list of names that have duplicates in them. Some of the names have last, first middle. Some have just last, first. I want to combine the like names and leave the unlike names not combined.
I want to combine in this situation ex. Smith, Peter Smith, Peter W
I do not want to combine in this situation ex. Smith, Peter M Smith, Peter W
My goal is to get the original name in a table with the combined name next to it.
Original NameNew Name Smith, PeterSmith, Peter Smith, Peter WSmith, Peter
I have a form with several data fields on it. I also have a button on the form that allows the user to duplicate a record . The reason for this duplication is so that if there will be an additional client record for the same customer, but only one piece of data will need to be changed, it's easier to copy the record and then change the one field.
However, I am getting the following message:
"some of the field names you tried to paste don't match fieldnames on the form"
and then not all data in all fields gets duplicated.
I need to figure this out, but am going nuts with it. If anyone has an idea or two they'd care to toss my way, I would be happy.
...TableA: 1 indexed field "Name" and many data fields in ~ 1000 records ...TableB: 1 indexed field "Name" and 2 data fields (DataB1 and DataB2) in about ~ 50 records
Tables currently have NO relationship set.
Want to add TableB data fields to TableA if Name are identical.
DataB1 and DataB2 fields are Not currently in TableA but could be added very easily.
Update (or whatever) will be done weekly where all previous data for DataB1 and DataB2 will be cleared from TableA before job is run.
