Trying to link subdata to data

[databasewolfy]

Please forgive the vague subject line. I couldn't think of anything better.



I am tasked with creating an inventory database. I have to track hardware, as well as software installed on each machine. The hardware part is easy enough, I am running into trouble with the software.

I have been given data to go off of. The spreadsheet has only one column listing all of the software for each machine. At the most, there are 200 entries separated by line breaks. I was able to separate the longest line of software entries, so now I can put one entry into a spreadsheet cell.

I have created a new spreadsheet as follows.

A1: device name B1-PV1: possible software entries.
A2: device name B2-PV2: possible software entries.
A3: device name B3-PV3: possible software entries.

What I want to do is create a subdatasheet listing all of the software for each machine. I have been told dlookup can accomplish this, but I would prefer to extract the software applications from this one spreadsheet field make each application its own record rather than some huge string of text. Once I do that, then I feel comfortable proceeding with dlookup, a function I am unfamiliar with.

What is the best way for a newbie to accomplish this?

[ranman256]

you have the tEquipment table
EqID, EqType, Make, Model,Ser#
123, PC, Dell, Insignia123, 12345
124, Printer, HP, Laserjet


the tSoftware table:
SoftID, EqID, SoftNAme, License
1, 123, Msoft Office 2019, abcde

2, 123, CAD 2018 , abcde
etc...

you can create a master form/subform and link the subform to the master form KEY id.
then just enter registered app for that 1 pc in the master form.

no Dlookup needed, but you can make one.
or make a search box for the ID and search in tSofware to return the EqID and show it in the form.

[databasewolfy]

A1: device name B1-PV1: possible software entries.
A2: device name B2-PV2: possible software entries.
A3: device name B3-PV3: possible software entries.

What I'm aiming to do is transpose this so A1 copies the device name for however many software entries exist, converting this into two columns without having to do extraneous copying/pasting for about three thousand different entries.

[ranman256]

make a macro
this will have N append queries to transpose your data into the columns you want.

[ssanfu]

It sounds like you have jumped into Access without first designing the dB using pencil & paper, whiteboard, the window, etc.
What tables do you think you need?
What are the fields you think you need?
What are the field types you think the fields should be?


You say

I have to track hardware

What types of hardware? What are the attributes you need/want to track?

Same for software. What attributes do you need to track?

If you have a dB, would you post it?
Would you post the spreadsheet data?

You should not be using tables/sub tables for data entry/ viewing data.


You understand that Access is a completely different animal than Excel???

[orange]

Agree totally with Steve. There are some basic concepts with database that you should review/learn.
Often best approach is to describe your "business" in simple terms. Then break it into entities and attributes (tables and fields), then test with sample data.
See the Database Planning and Design link in my signature. Working through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in that link will help you with concepts and give you insight for designing your database.
Good luck with your project.