simple auto merge database

[benh]

i have been trying to teach myself for a bit and just keep running into dead ends,

i have all excel files in a drop box that others fill out, then access loads them and puts togeather a form

po list file
material list file
supplies file
bad parts file
10 employee files

all thoes files merged by po# and part # into one form automaticly is what im trying to do

and each record has 5 to 20 fields some i want to display and some i do not

[June7]

So what is the question? What issue have you encountered with what you are trying?

Build query. How are the tables related?

[benh]

in the second line where i said report i should have said form, migrain yesterday was not thinking clearly

the question i ment to say is how do i get the info that is related by those two fields across all the excel files to appear in that single form

i do have the relationships made showing they are all related... tried making a query it said I could not do that

[benh]

well i just got the form to half way work, made a po detail form then made a subform for part information and they are tied togeather on the part #

im stuck now combining all the employee spreadsheets into one so i can marge them into the po details form

[June7]

What is the issue with merging the employee sheets? Copy/paste could do that.

I don't know what you mean by 'merge into the po details form'.

[benh]

each employee has an excel file that access imports as a linked file

i want to make a table that combines them all into one,

[orange]

You seem to be using Excel and Access to solve some sort of issue. Readers, at least me, have not understood WHAT you are trying to do, nor why both Excel and Access are involved. Could you give us a brief overview in plain English to help us understand? How familiar are you with these software products?

[benh]

how do i merge 11 linked files that all have the same fields into one file that works the same as a linked data source

"changes made in excel will be reflected however the data can not be changed from within access"

but to me that is the exact same question i just asked im not sure of a more plain english way to state it.......

[June7]

Do you want to eliminate Excel and convert all to Access?

Or do you want to continue using Excel as data repository and link to the sheets?

A UNION query can combine tables (or linked sheets) into a single dataset.

[benh]

keep using excel, we use it on android phones, tablets, old laptops ect and save to dropbox

i have been trying a union query,,,, but it does not combine correctly , so im guessing i need a more advanced union sql file ?

SELECT * FROM [employee1]
UNION
SELECT * FROM [employee2];

employee1 and employee2 are the names of querys i made from a linked excel files <these are small sample files i have made real ones are larger>
togeather they should have 235 records .... after union i have 85.... after union the sorting is random so i cant see a pattern to figure out what is going on

[June7]

UNION will not allow duplicate records. So if a record in one table is identical to a record in another table, only one record will display.

UNION ALL will allow duplicate records, every record from all tables will return.

Also, this might work:

SELECT *, "Emp1" AS Source FROM employee1
UNION SELECT *, "Emp2" FROM employee2
...;

[benh]

UNION ALL didnt function correctly... tried it a couple times and my lack of knowing syntax rules does not help


The snippit you added I like, it worked, and im testing it, just requires a bit of manual checking ...... tested for about an hour and its working

Do you know how to add a line to that which will auto sort the query field Date ?

[June7]

Why bother with sort in query? Are you going to use this as the RecordSource for a report?

[benh]

I figured out the sort part. I appreciate your advice very much and thanks for your help I got it all working.

I wish i could make a template so you could see it better.
Im useing it to complie the information of each PO we work on to add up all the supplies and shop hours. The info gets entered in through the linked excel files which i can edit on almost any device. would be sweet if i had some of those watch phones and somehow could just use a talk to text to fill out the logs. right now we have a couple $50 win xp laptops and a few android devices. And computer illiterate employees

I still need to make it look good but for now it functions,,,, im sure the answer is no, but my last question for now.

Can my PO Details Form <which is the only thing the owners need to see> be ran as a standalone program without opening ms access ?

[June7]

An Access database can be converted to an executable that does not require an installation of Access.

Access can be programmed to hide the application window so the form could appear as a standalone object. This is advanced programming. It works but does have some issues related to minimizing the app.