Keeping linked date over 2 tables (1 Viewer)

Donury236

New member
Local time
Today, 13:45
Joined
Jan 10, 2021
Messages
14
I do apologise if I make a hash of explaining this

I have made a few changes to the asset management template and its basically what we need to track assets at work.
I have approx 1220 clients and 1600 items of equipment.
Some clients will have more than one peice/type.

I currently have this data in excel sheets for each type of equipment.
The rows have the clients details then the equipment.

Access has 2 tables - clients and Assets.
The assets list form has an 'owner' field that is populated from the contacts extended query and I assume links the data?

As you can tell I am not enamoured at the prospect of going through 1220 people and matching them to their equipment.
Is there any way on earth to (easily as I am a bit of a dunce with this) upload the data so that stays linked?
 

plog

Banishment Pending
Local time
Today, 08:45
Joined
May 11, 2011
Messages
11,611
Some clients will have more than one peice/type.

In communicating data to people who have no idea of it, do not use synonyms. Piece, type, asset,? Are those 3 concepts or 1? Then later you introduce "equipment", is that a fourth distinct idea or are they all synonyms?

It's probably best to demonstrate your issue with data. Show us some sample data from your spreadsheets to demonstrate your issue.
 

Donury236

New member
Local time
Today, 13:45
Joined
Jan 10, 2021
Messages
14
In communicating data to people who have no idea of it, do not use synonyms. Piece, type, asset,? Are those 3 concepts or 1? Then later you introduce "equipment", is that a fourth distinct idea or are they all synonyms?

It's probably best to demonstrate your issue with data. Show us some sample data from your spreadsheets to demonstrate your issue.
Apologies.

I though it was clear when I mentioned it after mentioning the equipment, that a client (or Contact as access has named the table In the template) could have more than one peice of this equipment.

But then you. Guys understand access and I do not.. So

My issue isn't an issue

It's a question.

Do I have to Upload all the data for the 2 tables (assets and contacts - which are our service clients) separately and then manually go through each row of the asset form and match it up to the contact that has it. (I've calculated that I could. Fit in 50 a day on top of my Current workload so would take me 6 weeks.)

Or, os there any way to upload all the data already matched up?

I cannot upload Anything with data in it or I'd be breaching my works gdpr. Regulations.

But it's literally the Microsoft access asset maintenance template that I am using. I have not added or removed any tables, forms or queries.
The template has the query form. 'contacts extended' which somehow generates a field/option/category of 'owner', which is then on the asset details Form linking that asset to that contact.

I am assuming for now that there is no way to import This data with the contact and asset already assigned to Each other.....
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:45
Joined
Jan 23, 2006
Messages
15,362
But then you. Guys understand access and I do not.
Since you are still in the "what should I be doing stage" and trying to come up with some options for proceeding, you could:
-You absolutely should be working on a test/development database!!
-Mock up some data for both your Assets and Clients tables. We often recommend namess like Porky Pig, Barb Dwyer, Owen Money etc. to provide some data to assist with communications. Remove confidential/private material and substitute mock/fake names/numbers/cities etc. We do not want your real data.
-provide your table structures( as you or your template are using)
-provide some details about your requirement and any timeline/constraints envisioned
-identify some of the transactions/activities that this proposed database should/must support

You may get some insight from my Stump the Model.

Good luck with your project.
 

plog

Banishment Pending
Local time
Today, 08:45
Joined
May 11, 2011
Messages
11,611
Do I have to Upload all the data for the 2 tables (assets and contacts - which are our service clients) separately and then manually go through each row of the asset form and match it up to the contact that has it....Or, os there any way to upload all the data already matched up?

I don't really know what you are asking. On one hand it seems like you are implying that it would be simple to match the data externally then upload it--if that's the case do that. Or are you asking for instructions on how to do the matching? If that's the question, we are Access guys and prefer doing it in Access. We could certainly come up with an algorithm...if we had data to use as a guide.

For sample data, we don't need real world data, just data that gives us an idea of what you are starting with and where you want to end up. Suppose I needed help extracting name and social security number from a field in a dataset. I wouldn't post the actual data, I would tell people that my data looks like this:

MyField
"Jim Smith, 123-45-7890"
"Sally Jones, 999-99-9999"

And I want this:

FName, LName, SSN
"Jim", "Smith", "123-45-7890"
"Sally", "Jones", "999-99-9999"

That would give people enough of an idea of what I have to start with and what I want to end up with.
 

CarlettoFed

Member
Local time
Today, 14:45
Joined
Jun 10, 2020
Messages
119
Of course it can be done but you need to have the Excel file available to get an idea of how to carry out the project.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:45
Joined
Sep 12, 2006
Messages
15,613
Given your current Excel file, you should be able to load the necessary data into Access from the Excel files.

However, Access itself is not really amenable to simple user development in the way you use Excel. An Access database needs more careful design, and manipulating the data in Access assumes a level of proficiency that exceeds the equivalent starter level in Excel.

The idea is that your final database has a single table of users, a single table of assets, and a single table of asset-allocation, showing which users and assets are allocated together. You then filter your data by means of forms to give you the view you want. In fact you very rarely see all your data at one time. You may need a more complex design than this.

the data filters you have might answer queries like this:-
show all historic allocations of a particular item
show all assets currently allocated to a given customer.
show current allocation of all items in a particular class of asset.

Does your template include different views of this nature?

Access really is powerful. It's so powerful that developers design Access databases to limit what users can do (including themselves) as it's possible to damage your own data very easily, and without even realising it has been damaged.

You say you have a template - so your first task is to populate the template with the assets, customers and allocations by manipulating your spreadsheets, but that requires both an understanding of the database, and the appropriate knowledge of using and writing access queries.

The template probably allows you to do all of this by entering the data manually, but doesn't tell you how you can automate this to load the data more quickly. Therefore, your best bet may be to pay someone to load the initial data. Note that Access is strongly typed. Data in an access column needs to be of a single type, such as "date". The data in your spreadsheets is pretty well all text. So if dates in some columns are not entered as correct valid dates, the database will reject the dates, which may affect the data loading - therefore as well as code to load the data, you also probably need procedures to validate and clean the data.

You also need to do this for each spreadsheet. How many spreadsheets do you have? Again the idea is to have a single process that works with each spreadsheet, but this pre-supposes that your spreadsheets are all formatted with the same column structure.

Finally, out of interest, you mentioned you amended the template? What sort of amendments did you make?
 

Donury236

New member
Local time
Today, 13:45
Joined
Jan 10, 2021
Messages
14
Given your current Excel file, you should be able to load the necessary data into Access from the Excel files.

However, Access itself is not really amenable to simple user development in the way you use Excel. An Access database needs more careful design, and manipulating the data in Access assumes a level of proficiency that exceeds the equivalent starter level in Excel.

The idea is that your final database has a single table of users, a single table of assets, and a single table of asset-allocation, showing which users and assets are allocated together. You then filter your data by means of forms to give you the view you want. In fact you very rarely see all your data at one time. You may need a more complex design than this.

the data filters you have might answer queries like this:-
show all historic allocations of a particular item
show all assets currently allocated to a given customer.
show current allocation of all items in a particular class of asset.

Does your template include different views of this nature?

Access really is powerful. It's so powerful that developers design Access databases to limit what users can do (including themselves) as it's possible to damage your own data very easily, and without even realising it has been damaged.

You say you have a template - so your first task is to populate the template with the assets, customers and allocations by manipulating your spreadsheets, but that requires both an understanding of the database, and the appropriate knowledge of using and writing access queries.

The template probably allows you to do all of this by entering the data manually, but doesn't tell you how you can automate this to load the data more quickly. Therefore, your best bet may be to pay someone to load the initial data. Note that Access is strongly typed. Data in an access column needs to be of a single type, such as "date". The data in your spreadsheets is pretty well all text. So if dates in some columns are not entered as correct valid dates, the database will reject the dates, which may affect the data loading - therefore as well as code to load the data, you also probably need procedures to validate and clean the data.

You also need to do this for each spreadsheet. How many spreadsheets do you have? Again the idea is to have a single process that works with each spreadsheet, but this pre-supposes that your spreadsheets are all formatted with the same column structure.

Finally, out of interest, you mentioned you amended the template? What sort of amendments did you make?

I have all the data. Its all cleaned so that it has the correct matching fields to Access so that it populates all the fields.

I have spreadsheets that have the contact details and the asset details matched up already.

The Access asset maintenance template has 2 tables : contacts & Assets.

I can upload the data to each table. In doing so it becomes unmatached. i.e I can look up an asset but it is not allocated to a contact. I can look up a contact but they have not been allocated an asset.

So I as looking for a way where I did not have to sit and go through 1200 contact records and match these up individually to the 1600 odd assets that are assigned to the various contacts.

This is the template I am using : https://omextemplates.content.office.net/support/templates/en-us/tf01225342.accdt
I have one with altered fields for both the contact and assets on my works laptop, which I don't have at the moment.

And I have attached an example of the data. Yellow is the contacts data. Green is the asset data for that contact. i.e what has been issued to them on loan.

What I was looking for an answer to is....can I upload that data, for both the asset table and the contacts table, and have it already linked up in access.
There is the Assets Extended query that has an 'Owner' field. And you can add an owner in the asset details, asset list, and contact asset datasheet subform and that will link the asset to the contact.

I am not rally keen on doing that for 1200 people and allocating roughly 1600 assets manually.
 

Attachments

  • example.pdf
    214.6 KB · Views: 296

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:45
Joined
May 7, 2009
Messages
19,169
Do I have to Upload all the data for the 2 tables (assets and contacts - which are our service clients) separately and then manually go through each row of the asset form and match it up to the contact that has it. (I've calculated that I could. Fit in 50 a day on top of my Current workload so would take me 6 weeks.)
you need to unless on your Assets there is a field/column that it can be identified to a particular Contacts.
if there is, you can just create an Update query to the assets table, replacing the Owner with the ID generated from
the Contacts table after uploading.
 

CarlettoFed

Member
Local time
Today, 14:45
Joined
Jun 10, 2020
Messages
119
Again I repeat that it can be done but it is necessary to have the Excel file available, with a minimum of data and replacing any sensitive ones, to get an idea of how to carry out the project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:45
Joined
Feb 19, 2002
Messages
42,970
Two people have suggested a junction table but that is not appropriate unless an asset can belong to multiple customers. The junction table is used to implement many-to-many relationships which is not what you have.

I think the question is about when you load the assets - how do you get the customerID for use as a Foreign Key so that is what I will describe.

First import the the customer table. Do some analysis on the table to ensure that there are no duplicates. Then add an autonumber as the PK. Add a unique index on your old Customer Number.

To add the asset records, link to the Asset spreadsheet. Then create a query that joins the linked spreadsheet to the NEW Customer table. Join the two on the old customer number using a LEFT join. This will allow you to append unassigned Assets if you have any. If all assets are assignes to a customer, then an inner join will work. Select all the columns from the linked Asset table EXCEPT the old Customer Number. Then convert the query to an append query type. If the column names are identical, Access will automatically populate the "To" row. If the names are different, you will need go go through them one by one to map the spreadsheet names to the Asset table names. Now seledt the new autonumber PK from the new Customer table and append it to the CustomerID FK in the new Asset table. This is how you change the link from using the old customer number to using the new customerID.

This is fairly trivial for those of us who have been there, done that. I've attached a sample to help you. The sample uses an embedded spreadsheet rather than a link for convenience.
 

Attachments

  • GetFKviaJoin.accdb
    472 KB · Views: 274

Donury236

New member
Local time
Today, 13:45
Joined
Jan 10, 2021
Messages
14
Two people have suggested a junction table but that is not appropriate unless an asset can belong to multiple customers. The junction table is used to implement many-to-many relationships which is not what you have.

I think the question is about when you load the assets - how do you get the customerID for use as a Foreign Key so that is what I will describe.

First import the the customer table. Do some analysis on the table to ensure that there are no duplicates. Then add an autonumber as the PK. Add a unique index on your old Customer Number.

To add the asset records, link to the Asset spreadsheet. Then create a query that joins the linked spreadsheet to the NEW Customer table. Join the two on the old customer number using a LEFT join. This will allow you to append unassigned Assets if you have any. If all assets are assignes to a customer, then an inner join will work. Select all the columns from the linked Asset table EXCEPT the old Customer Number. Then convert the query to an append query type. If the column names are identical, Access will automatically populate the "To" row. If the names are different, you will need go go through them one by one to map the spreadsheet names to the Asset table names. Now seledt the new autonumber PK from the new Customer table and append it to the CustomerID FK in the new Asset table. This is how you change the link from using the old customer number to using the new customerID.

This is fairly trivial for those of us who have been there, done that. I've attached a sample to help you. The sample uses an embedded spreadsheet rather than a link for convenience.
Thank you. This is, I think, the answer I was looking for!
 

Users who are viewing this thread

Top Bottom