Relationships for names in two different places?

[Thiazi]

I'm importing a rather large CSV file that has many employees,
repeated on several lines (one line per day) that contains performance
information.

If I want to create a separate table that will contain basic
information about each employee (for inclusion on reports), would that
be accomplished with a relationship?

I can setup a query to pull up the information by employee name, but I
of course receive more than one row since on the import an employee
can be listed several times (multiple rows).

The two tables would not share any data, I just want a setup so when a
report is generated and a specific employee appears, this information
is generated with it. Thank you!

 

[Arvin Meyer [MVP]]

If each row represents 1 day, your query can use a date criteria to limit
the data to a single date or range of dates:

SELECT tblMyData.*
FROM tblMyData
WHERE (((tblMyData.DateField)>#3/29/2007#));

or

SELECT tblMyData.*
FROM tblMyData
WHERE (((tblMyData.DateField) Between #3/29/2007# And #3/30/2007#));

 

[Thiazi]

If each row represents 1 day, your query can use a date criteria to limit
the data to a single date or range of dates:

SELECT tblMyData.*
FROM tblMyData
WHERE (((tblMyData.DateField)>#3/29/2007#));

or

SELECT tblMyData.*
FROM tblMyData
WHERE (((tblMyData.DateField) Between #3/29/2007# And #3/30/2007#));
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com









- Show quoted text -

But essentially it's going to have to be two separate tables:

- one table where the CSV is imported (on a daily basis), each line
is one day and is formatted the same all the way across (with the
emplpoyee name beginning the line)
- second table with employee information that will be entered
through a form

What I want to do is at the time a report is ran, merge the two tables
back together. So match up the employee names on both tables, show the
data that was imported from the CSV, and show the employee information
entered through the form.

 

[Arvin Meyer [MVP]]

But essentially it's going to have to be two separate tables:

- one table where the CSV is imported (on a daily basis), each line
is one day and is formatted the same all the way across (with the
emplpoyee name beginning the line)
- second table with employee information that will be entered
through a form

What I want to do is at the time a report is ran, merge the two tables
back together. So match up the employee names on both tables, show the
data that was imported from the CSV, and show the employee information
entered through the form.

Unless you want multiple records from the CSV table, you'll need to run the
query. Also, you should have a key field like PersonID in the CSV table. Use
the same values in your Access table so you can sync up the records.

 

[Thiazi]

Unless you want multiple records from the CSV table, you'll need to run the
query. Also, you should have a key field like PersonID in the CSV table. Use
the same values in your Access table so you can sync up the records.

Yes, it's possible that I could be getting multiple records from the
CSV. I want to setup a query to pull data based on a specific field in
the data set. Then, when the report is generated based on the returned
results, I want the data that I entered on my own form to show up in
the report. So essentially, the report is pulled, employee names are
given, and if those employee names match employee names entered on the
contact information form, it merges the data for each employee and
adds it to the report.

Hopefully I haven't made that sound really confusing. How can I
accomplish that? The CSVs look like this:

Employee name, Data 1, Data 2, Data 3, Data 4, Data 5, Data 6, Data 7
Employee name, Data 1, Data 2, Data 3, Data 4, Data 5, Data 6, Data 7
Employee name, Data 1, Data 2, Data 3, Data 4, Data 5, Data 6, Data 7

But, an average CSV can look like this:

Jane Doe, Data 1, Data 2, Data 3, Data 4, Data 5, Data 6, Data 7
Jane Doe, Data 1, Data 2, Data 3, Data 4, Data 5, Data 6, Data 7
Jane Doe, Data 1, Data 2, Data 3, Data 4, Data 5, Data 6, Data 7
John Doe, Data 1, Data 2, Data 3, Data 4, Data 5, Data 6, Data 7
John Doe, Data 1, Data 2, Data 3, Data 4, Data 5, Data 6, Data 7
John Doe, Data 1, Data 2, Data 3, Data 4, Data 5, Data 6, Data 7

So I setup a form to enter contact information for John & Jane Doe.
Then, when I setup a query to get data based on, say, Data 2, it
returns the data set.

Then, at that point, I want it to grab the data out of employee
information for both employees and add it to the access report that is
generated.

 

[Guest]

Just some background:

Access needs to have a "primary key" index on each table
so that your queries are updateable.

If you don't have a "primary key", you can still do reports,
but any query result with more than one table is read-only.

For the same reason, you need to have a matching index
on the joined tables. If you don't, the query result (the recordset)
will be read-only.

But even without the matching index, you can still do reports.

One way to make sure you have a matching index is to go
into Table Design View, pull up the indexes window, and
make sure that the field is indexed.

Another way to make sure that you have a matching index
is to go into the Relationship window, and draw a line between
the Primary Key in one table, and the matching field in another
table.

But even if you don't draw a relationship, or create an index,
or have a primary key, you can still do reports.

To join two tables in a query, you can draw a join in the
query design window. Sometimes Access will draw those
joins for you, but if it doesn't, you can draw the join yourself.

(david)

 

[dbahooker]

Arvin should not be trusted; he believes that the only appropriate use
of Access is to use MDB / MDE files.

I mean.. that cry baby needs to lose the training wheels

 

[dbahooker]

Arvin should not be trusted; he believes that the only appropriate use
of Access is to use MDB / MDE files.

I mean.. that cry baby needs to lose the training wheels

 

[Arvin Meyer [MVP]]

Your mistake is setting up the form to enter John and Jane Doe in the same
record. It is easier to combine or concatenate records than to parse out dat
into separate columns (which you must do to get a match.

 

[Todos Menos [MSFT]]

Arvin is a baby retarded programmer
don't listen to him or else you'll be stuck in the 1st grade with him,
making $12/hour