I am working on a database of biological samples and test data. A problem is that many times the same subject has multiple ID's. For example, one subject may have the ID "ID234" but they previously had the ID "Sub84" or something like that. I want to be able to have 3 or 4 fields that have ID's for a subject, and I don't want a single one of them to be repeated. So I have column a, b, c, and d, and I don't want to have one record to have the same ID in column a as another record has in column d and so on. All I have been seeing is a way to make sure all of a, b, c, and d are unique combinations, but I want none of the fields to be repeated.
I'm working on a database that has to be done in Access and in coming up with a unique ID for each worker I want to take the first letter of the first and last name and the last 4 digits of their social to create the UID for the table. So for FIRST_NAME:Homer LAST_NAME:Simpson with SOCIAL:123-45-6789 his UID would be HS6789. Is there a function within Access that would allow me to do that, or is that something that's just too advanced?
Two tables, in one there are Items and services, in the other purchases.
1st table: ID - autonumber primary key Product Name - Text Quantity - Number Barcode - Text
2ndt table: ID - autonumber primary key Product Name - Text Quantity - Number Barcode - Text Client - Text Puchase Date - Date
How can I enter data to my 2nd table using unique fields (barcode, Product name actually ID in 1st table?) Problem Is Access alows only 1 unique key field in table and that is undertandable. Is there any way out in my situation.
barcode will be entered using barcode scanner it can be only text field but information like product name must fill in automatically, Product name must be drop down box if one wan't to select it manually then barcode should fill in automatically.
Please Attach your DB if you have time to look into this issue.
I have a large query that has information from accounts sorted by date, account repeat but they are always associated with a different date. I'd like to create a report where each date has it's own column with an associated field displayed as the information in the column. However, as data is added to the table I'd prefer to do it without having to make a query to filter each date each time information is added.
The information is added in bulk with all the same dates, so ALL accounts (excluding newly opened accounts that may have no information with a certain date) should have information for each date - there will not be single accounts with their own date. For example: An account numbered 12345 with data of 57% on 1/2/13, 63% on 6/2/13, and 89% on 12/2/13. I'd like each of the dates to have their own column, with the account numbers as the row and the percent data to show under the date they are associated with. how to manage this in a report, or if it is possible.
I am trying to make a basic database system to track orders and contacts and the like for my small business. I have created a series of databases that look pretty standard, something like this example:
I want to create a form that I will open and complete every time I process an order. I want to fill out both the customer info and the order details at one time.
Assuming that the "CustomerID" in the "Customers" table is set to Autonumber in order to create a unique ID, how do I keep from creating multiple new "customers" every time I enter a new order regardless of whether or not that customer already has an ID number from a prior order?
It seems that I would need to make some sort of Macro that would say something like, if the "CompanyName" entered is already listed in the 'Customer' table, use the existing CustomerID, if it is not listed in the Customer table create a new CustomerID.
Should I be looking into Macros, and if so should the macro be built into the form or the database?
I have this basic question (obviously not so basic for me): how to pull out only unique records from two fields.
More details - two fields with names of competitors in a tournament (Winner or Loser) and i need to build a query to have all players names participating in the tournament regardless if they have won or lost in one list.
How i would best combine values in a table to produce a 'primary key id number.'
For example: the first letter of a city in the ID and the next number available/auto number - Portsmouth -> P233
I know i can create this in a query however i want it as the unique ID for that record entry in a table. If that doesn't make sense i can try to elaborate some more.
So what is the best way to ensure the third entry is fine as whilst ready salted already exists it doesnt exist for doritos but the fourth entry would pull up a msgbox refusing the entry as it is a repeat of entry one.
Any way on a table that I can have a unique identifier over two fields? E.g.I have a login ID and a Domain field in a table with sample data below.
Login Domain
John Test Jane Test Fred Live John Live
The login names need to be unique to the domain so the integrity cannot be breached. This has to fit into one table (although the domain is actually being pulled from a look up table so is in fact a numerical value) for ease of form creation.
I have a large table with information about different cars, called "Car Metadata". In this, each car has a unique identifier ("Car Code"), the make ("Manufacturer Name") and the model ("Short Model").
What I want to do is create a form with dropdown menus.I have a first combo box with all the makes in the Metadata table.
What I am having trouble with is that I want a second combo box underneath which would only show the models which correspond to the make selected in the first box.E.g. someone could select Ford in the top box and the second box would only show Fiesta, Focus etc.
Once it has done that I want it to be able to write the Car Codes for the records which match up to the make & model selected to a new table. Just to complicate things further, there can be multiple records with the same make and model but different Codes, I want all these multiple codes to be written to the final table.
I already have created & run an Access database for around 12 users. I now am looking to create a database that can have at least 30 users. Each one with a unique user ID & password (Using a security file ".mdw") Users log into a front end on a network environment. Now...My question...Is 30 to large of a user base for Access in a network environment??
I have create 6 tables for library books (which are differentiated by categories). Each table has different category and unique ID name e.g. F1, F2, F3..(for table 1), G1, G2, G3..(for table 2) and so on. How do I combine all those tables into 1 table for easy search for a book rather than open up each table? Tried append query but its ID run as 1, 2, 3... , not F1, F2, F3 and it only append 1 table, not the rest.
I have a MS Access 2000 database with 136 data tables in it. What I would like to do is execute a piece of VBA code which will list for me in another table, (for example: Field1: TABLE NAME Field2: FIELD NAME 1 Field2: FIELD NAME2 etc), starting left to right, how many fields would have to be combined in each table to represent a unique record.
For instance:
TYPE ID TEXT 1 1 "Cats" 1 2 "Dogs" 2 1 "Rabbits"
In this example a combination of the fields TYPE and ID give a unique record.
I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).
I confirmed that joining the tables on the phone number text field does not work.My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what Im looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.
I have a client database that has recently had multiple duplicate entries. I need to reduce or negate this erroneous activity. I have a client table where I record amongst others, the following;
key [christian_name] [family_name] [dob] ......
I believe that to prevent duplicate entrie via form I have created an additional field called "unique" given it as a unique index which I want to have populated with the joined fields first_name & last_name & dob (IE johndoe01/01/90), and then as user enters a new client it wont allow a duplicate.
However I need to fill all the existing customers (3600+) with the relevant joined existing data. If I create an expression I can cajoin the fields in a select query but when I try to make an update query the same syntax comes up with empty fields.
select query sql that worked to show field ...
SELECT divers.christian_name, divers.family_name, divers.dob, [christian_name] & [family_name] & [dob] AS Expr1 FROM divers;
update query that was empty ..
UPDATE divers SET divers.[unique] = [christian_name] & [family_name] & [dob];
I tried and failed to get this to work using a multiselect listbox..I have a list of departments in tblFunctionalArea...My main table is tblStatic..I want to be able to for each record select multiple departments affected by a record and store them in the tblStatic.After looking around i couldn't find many people successfully maanging to store listbox values in a table...
I decided to create 5 fields in tblStatic and in my form create multiple combo boxes cboFunctionalArea1, cboFunctionalArea2 etc etc which are bound to these fields.I want to be able to ensure the list for any combo box requeries and takes out any selection in the other boxes.
I have this working in a strict cascade fashion i.e. in cbo1 all dept's visible, in cbo2 it takes off whatever was selected in cbo1 etc. But if someone then jumps back and deletes the content of cbo3 then the whole thing breaks or if they amend in the wrong order it breaks
1. I have a database (see attached) with three tables all of them with the same fields. The first three are numbers (InCo_No, Proto_No, Year_No). Each of these fields (numbers) can be the same in the other table(eg. Year_No), but the combination of the three cannot be.
How can I prevent the entry of a duplicate combination of these three fields?
2. I want to have a form to fill the three tables separately, depending the values in the other fields.
I want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:
Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.
I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.
I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE, Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _ & " FROM Table1 " _ & " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _ & "FROM Table2 " _ & " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
I have read through just about every post on this forum related to "Unrecognized Database errors" and have not found a solution to my direct problem.
Stats: Database is running Access2K on all machines; db is on network location, no sharing issues or permission issues have been identified, no other databases in this network location have had this problem. also, we have tried compact-repair and also have tried re-creating the database and neither have proved successful.
I can open the database just fine if I am the only one opening it. If someone else tries to open it once I have it opened one of the following occurs:
1. Nothing. Hourglass appears for a split second, then nothing happens. 2. Error message "unrecognized database format "mydb.mdb"" appears. If I click OK and both users close the db, it can be opened just fine again by the first user. It doesn't matterwho opens it first, but the second user gets this message.
I created the below query to come up with a new form. When I enter a single parameter, it works fine. When I modify the code and enter multiple patameters, it also works fine if I do not enter any information for the parameters. But once I enter one of the parameters information, then it does not come up with anything. I double checked and made sure it was typed in correctly. Is there a trick when entering multiple parameters on a query?
SELECT [JE 06 Log].[Operational Region Name], [JE 06 Log].[Period Name], [JE 06 Log].[Source System], [JE 06 Log].[Source Name], [JE 06 Log].[Category Name], [JE 06 Log].[Associated Category Name], [JE 06 Log].[JE Name], [JE 06 Log].[JE Base #], [JE 06 Log].Area, [JE 06 Log].[Line Description], [JE 06 Log].[Natural Account], [JE 06 Log].Description, [JE 06 Log].[JE Entry Date], [JE 06 Log].[Debit Amount], [JE 06 Log].[Credit Amount], [JE 06 Log].Amount FROM [JE 06 Log] WHERE ((([JE 06 Log].[Period Name])=[Enter Period]) AND (([JE 06 Log].[Source Name])=[Enter Path]) AND (([JE 06 Log].[JE Base #])=[Enter Base Number]) AND (([JE 06 Log].[Natural Account])=[Enter Natural Accnt])) OR ((([Enter Period]) Is Null) AND (([Enter Path]) Is Null) AND (([Enter Base Number]) Is Null) AND (([Enter Natural Accnt]) Is Null));
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time End Date/Time Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee 12/06/2014 01:00--12/06/2014 03:00------John Smith 12/06/2014 04:00--12/06/2014 06:00------Jane Doe 13/06/2014 02:00--13/06/2014 05:00------John Smith 13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
