I want to create a different table for each sales person listed in a table. If I have salesperson1 through salespersonN listed in the table, I'd like to end up with a table for salesperson1 with just salesperson1 data through salespersonN with just salespersonN's data.
The resultant tables would be named something like
salesperson1 table1
through
salespersonN table1
Make sense?
6 6726 MMcCarthy 14,534
Recognized Expert Moderator MVP
Your question makes sense, it's just not the way that databases work.
So lets say you have a table for salespeople, then a table for leads and a table for sales.
You then would have something like this tblSalespeople
SalespersonID (Primary key)
FirstName
Surname
Etc tblLeads
LeadID (Primary key)
SalespersonID (Foreign key to tblSalesperson)
LeadName
PhoneNum
Etc tblSales
SalesID
SalespersonID
CustomerName
Etc
Then using a query something like ... - SELECT tblSaleperson.*, tblLeads.*, tblSales.*
-
FROM tblSalesperson LEFT JOIN tblLeads
-
ON tblSalesperson.SalespersonID=tblLeads.SalespersonID
-
(LEFT JOIN tblSales
-
ON tblSalesperson.SalespersonID=tblSales.SalespersonID
You can retrieve the information you need for each sales person. For further information check out this insight. Database Normalization and Table Structures
I really owe you a little more explanation. From time to time I send a personalized excel file to each salesperson asking them review and mark up a listing of a subset of their accounts with various information I generally collect yes/no type of data for a specific question about the account.
If my sales people could use Access I'd build a form where they choose their accounds to collect their data right into a table.
Unfortunately, I have to expernalize data and then import.
Does that help explain why I'm doing this silly thing?
MMcCarthy 14,534
Recognized Expert Moderator MVP
That helps explain it a little better :)
Still don't think its a good idea to create a lot of tables though. So if I understand correctly, you are going to import/link each excel sheet and then try to create a table from the data and then use the subsequent tables to update your data.
If that is what you are trying to do it would be easier to use vba code to automate the process. However, I don't know how familar you are with vba code.
Not familiar with VBA but can learn.
Best scenario is I would write a macro to create a temp table, one salesperson at a time, and export to an excel file of that salesperson. So, in the end I'd have salesperson1.xl s --> salespersonN in some directory somewhere.
MMcCarthy 14,534
Recognized Expert Moderator MVP
OK it sounds like you need to create dynamic queries.
You can put this code behind a command button. - Private Sub cmdButton1_Click()
-
Dim rs As DAO.Recordset
-
Dim qdf As DAO.QueryDef
-
Dim strSQL As String
-
Dim path As String
-
-
' find the path for the current division
-
path = "C:\FolderName\" ' Path to the folder you want to export to
-
-
With CurrentDb
-
Set rs = .OpenRecordset("tblSalesperson") ' replace with name of your salespeople table
-
-
rs.MoveFirst
-
'For each person in the table
-
Do Until rs!EOF
-
' Set up SQL query string, replace with your field names, query name, salesperson unique id etc.
-
strSQL = "SELECT Field1, Field2 FROM MyQueryName WHERE SalespersonID=" & rs!SalespersonID
-
' createquerydef command line follows
-
Set qdfNew = .CreateQueryDef("My_Query", sqltext)
-
DoEvents
-
' save the query to Excel, replace surname and firstname with your appropriate field names
-
DoCmd.OutputTo acOutputQuery, "My_Query", acFormatXLS, path & rs!FirstName & "_" & rs!Surname & Format(Now, "ddmmyyyy hhmm") & ".xls", True
-
' delete the temporary query
-
DoCmd.DeleteObject acQuery, "My_Query"
-
qdfNew.Close
-
Set qdfNew = Nothing
-
rs.MoveNext
-
Loop
-
-
rs.Close
-
Set rs = Nothing
-
End With
-
-
End Sub
Thanks for all your help. I'll give it a go this week.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Jay |
last post by:
Hi everyone !
I hope someone will be able to help me with this problem.
I currently have several tables in MySQL database. Below is my table
structures.
(Data type for each fields are ignored)
user
{
|
by: Gianfranco |
last post by:
Hi
I got a problem with 2 tables.
I have a table, say A, with x records, coming from a make table query
and a table, say B, with y records, coming from another make table
query. I need to join and link the 2 tables, so that the first record
of A is associated with the fist record of B, the second record of A
is associated (I mean,in the same row) with the second record of B and
so on...until the last row of A (where x<y).
I tried to...
|
by: JJ |
last post by:
I have a Make Table query which uses a date parameter. Each time the
user runs the query they will be prompted for a date. The table which
is created should be named based on the date which they entered. For
example: The query will retieve all invoices dated xxxxx, the user
will may supply the date of Jan 1 2004. It should create a table
called "Jan 1 2004 Invoices" (or something like that). Each table
created needs to be saved in the...
|
by: Mo Ka |
last post by:
Hi all,
Is there a function (or other method) to automatically append the system
date to the output tablename of a make table query such that the new one
does not overwrite an older table?.
I need these for stats but want to keep all previous tables created by
the same query and I do not want the user to have to enter the date the
table was created.
|
by: Nicolae Fieraru |
last post by:
I have two tables, they contain:
Table1: ID1, Name1, Address1, Purchase1
Table2: ID2, Name2, Address2, Purchase2
I need a query which creates Table3 with content from Table1 and Table2.
The records in Table3 have to contain all distinct records from Table1 and
Table2 (records where Name2 and Address2 do not already exist in Table3) and
Any help appreciated. I need these queries as a reference, I consider they
| |
by: Kathy Krizl |
last post by:
I'm probably doing something stupid, but I have a make table query.
One of the tables I reference has some check box fields in it. Their
Data Type is Yes/No, their field property format is Yes/No, they have
no default value, and their Display Control is Check Box. When I run
my make table query, the new fields have a Yes/No Data Type, but the
field property format comes up blank, and the Display Control comes up
as Text Box. How come...
|
by: Doc |
last post by:
Per earlier post, I am trying to save 'out' production data from a
program called Solomon - basically (I think) this was /is an Access/Sql
based program.
We are updating to different application and I need to bring over ONLY
certain DATA.
When I did an ODBC link to the DB all the tables are present.
I linked the three or four tables and have my fields (these are the ones
I want to modify for LATER import into the new application)
|
by: Robertf987 |
last post by:
Hi, I'm a bit stuck with an access database. I'm using access 2000 if that's any help.
Right, it's 3:40am right now and I'm rather tired, but I *hope* this makes sense.
I have a table which contains the main data of the database, records of information of grants. For each financial year, the old data needs taking out of the table. Basically I have two options: delete the old data from the year before, or back it up in a new table. I'd rather...
|
by: TC |
last post by:
I'm experiencing an unusual problem. When I run a specific make-table
query on a computer, that computer shuts down. The computer shuts down
completely, without warning, as if a power failure occurred.
I can run the query without any trouble on other computers. I can also
run other queries on this computer, including make-table queries which
create much bigger tables.
When it works (on other computers), the make-table query creates a...
|
by: Mourad |
last post by:
Hi All,
Is it possible to create a Make Table query in access (2.0 and 2003)
that creates the table into a SQL Server database?
Following the steps:
1- Create New Query
2- Set Query Type as Make-Table query
3- Enter table name
4- Click "Another Database", click Browse, then I cannot see anything
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |