Hi thre again

I have set up a database ramework, with lots of help from you lot! However, two or three people will be entering data on different pcs. The data is enetered into a table, Books, which has a sub datasheet, Contents. (this is another table).

My problem is this... how can i bring all the data entries together into one database. I have tried importing, but the subdatasheet values don't come, even when i import the sub datasheet. Anyone got any suggestions?

I'm so close to the finish... I can't fail now!

Thanks
M

What is the Primary Key of the Books table? It would not be easy to import if the PK is AutoNumber.
You have to import the Books table first and then its sub datasheet table. What error message did you get when you imported the tables?

Why don't you a front-end/back-end fashion if all the pcs are in the same LAN?

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

The PK is the autonumber, as it's the only possible individual item for each record. I don't get an error message when I import the tables, but when i click on the + sign in the books table to look at the contents sub data sheet, the sub sheet opens but is empty...

No LAN option in this case unfortunately.

How do you run the routine?

Since the PK (of the Books table) is AutoNumber and that causes the problem. When the Books table inported in which you can't append the PK field in, the records imported have new numbers and when you click for the sub datasheet, it couldn't find its sub records.

I would wait for all of them (users) finished keying in the data. Then change the PK, of the Books tables you are going to import into the main db, to Long. Before doing this, you have to select Cascade Update property of the relationships between the Books and its subdatasheet table. This enables you to change the PK of the Books table in the db you want to import into the main db. Change the first record to the next available number by checking the last record of the Books table in the main db out.
Repeat this again in the next db you want to import.

As clear as mud, huh?

I am searching for my old post similar to this. If I find it, I'll post back soon.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

As clear as mud indeed but it will sink in - I understand the principles of what you are saying. Thank you so much for your help in this...

Michael

Hi

I'm attaching the database that I have so far - maybe this might help you see where I have gone wrong. Any help much appreciated. I'll buy you a drink when you come to Ireland!

You set Title as a primary key for the Books table. Is the Title unique? If it's unique, you don't need the Book Id field at all. How far are you with this project? At the beginning or most of the books are keyed in?

I would suggest you to modify your db as follows:

1. Make Book Id and Title as a compoud primary key (set 2 field as a primary key) since you use the Book ID field as a primary key field with the Book ID field in the Contents table as a foriegn key.

2. Add an AutoNumber field in the Contents table to be as a primary key.

3. Create a relationship between the 2 tables by going to Tools menu>Relationships...>add the 2 tables in>drag the Book ID field in the Books table and drop it in Book ID field in the Contents table>double click the relationship line>select Enforce Referential Integrety and Cascade Update Related Fields>save the relationships and close this window.

Here are steps to do before import the data in:

1. In the db file you want to import data into the main db, go to Relationships window and select the relationship line and hit Delete button on your keyboard to delete it.

2. Close the window and open the Books table>change Book Id field type from AutoNumber to Number.
The field will be set to Long data type.

3. Create the relationships of the 2 tables back again. Make sure you exactly repeat what I said in step 3 of the previous section.

4. Check the last Book Id (the max one) of the Books table in the main table. Write down the number and go back to your previous db.

5. Run Query1. Check the attached out. I created an update query to change the Books ID of the Books table. When the query is open, it will promts you for '(Enter) The Max Book ID Number'. Just enter the max Book Id number you joted down. Click Yes to all the comming windows you see.

6. Open the Books table and check for the new Books IDs. Click the sub datasheet and you should see related records as you expected.

7. Now go back to the main db and import the 2 tables in. Repeat this routine for the next db to be imported.

8. Done!

Feel free to discuss more.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

Thanks
I'll work on this tonight and let you know how i get on

thanks again

M

You're welcome and tell us how it goes with you.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

Ok. Here goes. The long and short of it is that it worked. So I am a happy boy (IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

In answer to your questions, I'm using a book ID as a sort of barcode, so that if the system is expanded in the future to cater for library loans, each book will have an individual record. Your idea of creating a compound primary key (I didn't know you could do this) gets over the problem of repeated entries of the same title.

Data entry hasn't started yet, so I can still change the table design. I did pretty much what you said, except for one thing. I am going to tell data entrists that book ID code must be unique, so therefore one entrist uses number 1-500, the other 500-1000 etc. Therefore I don't need to run the query when importing the data, and I don't have negative book id numbers. In addition, the boks will be assigned with a sticker on entering record, so book ID is determined at that stage.

Other than that, I followed your instructions, and it worked well.

One problem (as always) has cropped up though. In records wher there is no sub datasheet entry, when I run a search query, these records do not appear. I can get over this by telling data entrists to enter "-" in the subdata sheet columns if there is no entry - a bit messy, but works.

Thanks very much for your help, you have been very generous with your time

Regards,
Michael

Glad you sorted it out. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

For your last question, you can use LEFT (or RIGHT) JOIN to help. When you are in the select query Design view, double click the relationship line and select 'INCLUDE ALL RECORDS FROM 'BOOKS' ... This force it to show all records in the 'Books' table, no matter if their subdatasheet are null or not.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

LOL... you're a genius.

Thanks, all done just in time too - project starts tomorrow. I've learned so much this week... one thing is don't volunteer something you can't do ;-)

Later

You're welcome.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)