data extraction from two interrelated access forms to excel?

Question

I have an access form (access 2007) where two tables are interrelated like for instance let us say two tables:
Employee: Stores the details of the employee (service details). Employee name is the primary key.
Family :Stores the details of the employees family details. It also has employee name as a tupple in the table. Now e.name of family table is in one to one relationship with e.name of employee table.
Now i want to extract the data that is stored in both the forms to an excel file but while extracting to excel only employee table is extracted to the excel file family is not being converted 
What is the problem and how to solve it.

Todd · Answer

The db design is unfortunate (for the designers, not for you), because in real life a person can have more than one family, and a family can have more than one person.

Obviously, you're just concerned right now with the exporting of the query. I don't work with Access (like, ever, and never will), but I'm pretty sure they give you the option of exporting query results. Open up the SQL query tool, write your query, and export it. If it doesn't allow you to do that, other ways provide the result. I resort to a good scripting language when the OS/DBMS doesn't play fair :)

Your query will look something like this (you provide things in angle brackets <>)

SELECT <column stuffs> FROM Employee e JOIN

Family f ON e.name = f.name

WHERE <criteria stuffs>

Here's the problem, though: with the db design as it is, you might have employees without families and families without employees. This data anomaly is not uncommon. So, you sort of want _everything_. I suggest using an outer join for this. You can check your work by doing a SELECT COUNT(*) on the individual tables and see if you are missing items or not.

Back to the exporting of the query. Even though MS is not my favorite tool in the basket, they do have an excellent support site. It is very likely someone has asked this question before, and there, you get the answer from the horse's mouth, so to speak.

Edit... I apologize if you are the designer. If you are, I commend you (not joking at all) on your choice of table and column names. One of my biggest pet pieves is people using silly identifiers in a structured database and those actually make sense.

? · Answer

You had 2 possible solutions. the elementary one is link Excel record to get admission to. you'll continually have as a lot as date thoughts in get admission to without wanting import more recent thoughts. The no longer really effortless way is implement a vba code to move thoughts to get admission to usind DAO and also you'll favor upload connection with DAO in seen straightforward Editor to help you take advantage of this programming source.

Trending News

data extraction from two interrelated access forms to excel?

2 Answers