I am trying to take a table that looks like this:
Customer Name
Fruit
Customer A
Apples
Customer A
Bananas
Customer B
Pears
Customer B
Apples
Customer C
Pears
And create a table that looks like this:
Customer Name
Apples
Bananas
Pears
Customer A
Apples
Bananas
Customer B
Apples
Pears
Customer C
Pears
I think this is relatively simple but don't know the syntax to create this table.
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)
So I have this field in a form/table called "CustomerID". This is to be an ID number for our customers that includes the first four letters of their last name (LastName field) followed by four digits.
This ID is created when a button is pressed in the form. After that, code needs to grab the first four letters of the last name (LastName field in the form and table) and then go to the Contacts table and see if there is already a CustomerID that starts with those four letters. If there is not, then the CustomerID will be those four letters followed by "0001". If there is, it needs to be those four letter followed by the next number sequence that still does not exist for those four letters.
For example:
First four letters of last name = zabo
Check to see if those four letters exist in CustomerID returns negative then
CustomerID assigned = zabo0001
Another example:
First four letters of last name = zabo
Check to see if those four letters exist in CustomerID returns zabo0001 and zabo002 then
CustomerID assigned = zabo0003
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"
View 6 Replies View Relatedif I got a 4 x 4 matrix table - 4 rows and 4 columns - MS Access 2007/2010 the values should exist as below with no repetition of any number in any of the cells.
1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16
a number should not repeat in any of the cells.I set a primary key on cloumn 1 and defined unique on all the fileds but that doesn't work since 1 columns does not know what the other cell contains and no relationship exists.
I am in the process of creating a DB for customer names, job numbers and document numbers, but have run into a problem right from the start.We are using a naming convention of the first 3 letters of the customer name, and 3 numbers after that. Eg. Customer names Allstar, Allpro would be All001 and All002 respectively.
The job numbers would then be sequential numbers, for each customer. So the first job for Allstar would be All001-001 and so on.How do I get Access to check/create the customer ID's? And then how do I get Access to check for the next job number for each customer?I was able to do this in Excel with the following;
=LEFT(B12,3)&COUNTIF($B$3:B12,LEFT(B12,3)&"*") in one cell
=IF(B12="","",LEFT(D12,3)&TEXT(RIGHT(D12,1),"00")) in another to add the preceding zero's.
Obviosuly , Excel is not the best way of tracking customers, jobs, documents etc.
I have an issue with a code that I have in a form which adds rows in a table as many times as categories chosen from a list. However, the code is not working correctly: it adds the information and creates a row with a category in blank, in addition to the rest of the rows with one of the chosen categories. I would like for this not to happen, to add only as many rows as the categories chosen.
My code is the following:
Private Sub cmdUpdate_Click()
Dim valSelect As Variant, MyDB As DAO.Database, MyRS As DAO.Recordset
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Tasks", dbOpenDynaset)
MyRS.MoveFirst
[Code] .....
I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.
I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.
I need Create a new counter on my Customer form for each day, using an Autonumber and print it
View 6 Replies View RelatedI am doing my project to create data base for Video Hire shop. Was allright so far; however hit the wall now.
I need to create query to list Hires(rentals) by customer age group. I have created query with the following fields: Customer ID, date (Date()), DOB field and calculating field: AgeGroup:Now()-[DOB]. When click to display data in AgeGroup field is displayed in days. How to set it up to display decades not number of days days?
My current database creates an invoice per order. Until now that worked wonderful. Now i want to create an invoice per month per customer with multiple orders on the same database. So i will have to create a new table/query on the existing order table.
View 1 Replies View RelatedHello i want to create a tracking number based on information entered into a form.
So i have my main table called "MainData" and this is in the backend and linked through to the front end where my form is.
I then have a query which is called "calllog" which has a field called "Call Number" inwhich i placed the following
=Year([Date]) & Left([CustomerName],3) & Right([Date Created],2)
This then gives me a unique number. Once i have entered a job in it will generate a number. Now this part works fine but it does not update the Maindata. How can i get the this to transfer the data to the maindata table???
Hi All.
Is there a way to index and create a unique identifier using a combination of fields (eg. User# + Provider + Date)?
Thanks.
I have a form with a combobox where I select the Carrier witch then populates a multiselect listbox with all the record from that carrier in the specified date. When I select them and click save I want all the record ID(ConID) saved under the same ID(manifest ID) ie,
1 223
1 225
2 344
2 4444
Ect
It saves it in a table called Manifest With the fields Manifest ID and CONID which is a lookup field to a different table. Here is my current code for the on click command
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Manifest", dbOpenDynaset)
[Code] ....
Now I've done some searching and I thin dmax is what I want to use.. But I am not sure how to make it work...
I have a table as followed;
EmployeeID: PK
FirstName
MInitial
LastName
Company:FK
Department:FK
What I want to do is create a new field that automatically populates based on what is entered into the above fields.
Essentially creating a single field with unique data generated by multiple fields.
For Example if I entered the following informaiton:
EmployeeID: 1 (AutoNumber)
FirstName: John
MInitial: P
LastName: Doe
Company: FederalGov
Department:Test
The new field can be generated as followed: JohnPDoeFederalgovTest
What i plan on doing with this is making this "generated" field an index so no new duplicated records can be added.
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.
I am trying to create an automatic unique 'number' (actually text) in a form. Here is an example of the format...
1456.R1
1456 is the project number
R stands for revision
1 is the first revision
So, in this database there could be 1456.R2 etc. but there could also be other project numbers, say 2323.R1, 2323. Looking for expression I need to enter to have Access look up the last revision for a specific project and then add 1 to it?
I have created a linked Excel table in Access 2010 called 'tblExcelLinked' and I have a form called 'ASB Log Form' for the purposes of presenting the data in a more readable manner that is easier to view, plus link other fields of data that are not directly related to the 'tblExcelLinked'.
Because there is no unique ID in the 'tblExcelLinked' to create a relationship, I have created a table called 'tblASB', which allows me to add other table data linked from same d/b.
I now want to update the 'tblASB' with data from the 'tblExcelLinked', but only append new records from 'tblExcelLinked', but my inadequate append query is duplicating the records each time I run it, rather than just adding the new ones.
Once sorted my next challenge is a macro so that this runs automatically rather than being manually triggered.
I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2".I would like this done automatically.
Table A
Field X Field Y
1 a
1 b
1 c
1 d
2 a
2 b
2 c
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.
View 1 Replies View RelatedIs there a way of merging 2 fields together to create an additional field
my database consists of 4 main tables (in order of relationships)
*HeadOfficeDetails
*SiteDetails
*ContainersOnSite *Contracts2015-2016
For example;
Account Reference: TEST
Site Number: 001
and the field i would like to have;
Site Reference: TEST/001
I would also like that when i add a new site to that account i will have TEST/002....
I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.
The first field is called Operation Number.
The second is Bag Number.
The reference number needs to be in this format: 19C.3.1
Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.
This is kind of like library catalog numbers. Not sure how to do this.
Hello,
I have a big table like this:
ID Name Race1 Race2 Race3
-- ---- ----- ----- -----
1 a Asian russian
How do I create a query to be like this (either in query or report)?
I like to combine three fields (race1, race2, race3) in one field (Race), and break any value of races to the 2nd line, 3rd line, if there is a value...
ID Name Race
-- --- ----
1 a Asian
Russian
In Oracle and SQL Server, we can combine a few rows to be columns-like, but in Access, is there any way to do that? and how?
Thanks so much!
Jenny.
I would like to make a form that can insert more than one row at one time. Something like add first column, then ask the user how many of the second row they would like, then prompt them for what is in the second row then add the information for the rest of the columns and have a separate row for each of the second column. So every row with have the same first column, but from there have a different row for how ever many desired in second column. So lets i enter for the first column, ABC, then I wanted 3 rows with ABC, then prompts me for the rest of the information for each of those rows separately.Something looking like this, oh and it would be updating an already existing table.
ABC|asdf|asdf|hgaf
ABC|hs|hasd|auio
ABC|JKL|ASE|ASDF
EDIT: I would also like to know if it is possible so it does it in ranges and dont have to do it manually like you enter the first column then enter a range for the second column and a bunch of rows are made with each value in between the range that was specified.
Create a query to show rows with timestamps in between two timestamps? my variables are cutoff time and the current time where cutoff time is saved on a table (tblcontroltable.cutoff ) a pseudo code of what i want to achieve is
select date, product, timestamps
from tblsample
where date = date and timestamps > cutoff and timestamps < time
i cant do this.. its too long..
basically i have a job table and a person table..
jobid, job date, job details....
personid, personname, person details...
the job table needs to store who BOOKED the job, and who was the PASSENGER in the job.
i want to use the person ids in the job table..
im having trouble here with the whole relationships and forms..
i need my user to be able to simultaneously add a booker and a customer to the person table and assign them both to a job and i dont know the best way to do it..
a few rules
job1 can be
booked by person 1
have passenger as person 1
job 1 can be
booked by person 1
have passenger as person 2
also the booker and/or passenger may be unknown.. which is why i want to create an unknown person in the person table.. with id 1 for example.. and anytime the booker or passenger isnt known my user can just type in id 1
someone help me.